Notatnik Webmastera : My SQL ...



Obserwuj zmiany wprowadzane na tej stronie | Wersja do druku | Zaloguj się:   Hasło:  

Wchodzicie tu często. Popatrzcie też na inne zapiski. Współtwórzcie tą wiki.



MySQL

pobierz MySQL'a


MySQL'a można pobrać stąd: http://dev.mysql.com/downloads/

mysqldump

Żeby polecenie mysqldump zwróciło tylko samą strukturę tabel bez żadnych danych można użyć opcji --where=0 lub -d

np.


Żeby zrzucić i strukturę i dane:


opis opcji:
  • --user – uzytkownik mysql
  • --password – hasło
  • --delayed-insert – dane zapisane będą jako kwerenedy INSERT DELAYED
  • -Q – nazwy tabel i pól będą ujmowane w znaki ` (potrzebne jeżeli mamy nazwy ze spacjami czy innymi dziwacznymi znakami)
  • -e – Wszystkie dane z jednej tabeli będą wyeksportowane jako pojedyncza kwerenda INSERT (szybsza i mniej zajmująca) (wygląda na to, że ta opcja jest w nowszych mysqldump włączona domyślnie i nie bardzo wiadomo jak ją wyłączyć ... jeżeli w tabelach jest dużo rekordów powstają wtedy bardzo długie kwerendy insert i może być problem z ich wykonaniem na innym serwerze MySQL ... aby ograniczyć rozmiar pojedynczej kwerendy insert należy użyć opcji np. --max-allowed-packet=2M)
  • -q – dane będą zrzucane szybciej (podobno (wg. man-a))
  • --where – pozwala podać warunek określający, które rekordy mają być wyeksportowane
  • --allow-keywords – pozwala żeby tabele i pola miały nazwy takie same jak słowa kluczowe My SQL
  • --no-create-info – eksportuje same dane bez struktury tabel

Opis tego narzędzia można znaleźć tutaj: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
Szczególnie interesujące mogą być opcje --skip-opt i --skip-quote-names

MySQL i różne kodowania znaków

Od czasu kiedy MySQL zaczął obsługiwać inne kodowania znaków (chyba coś koło wersji 4.0 albo 4.1) sprawy się nieco skomplikowały.

Przydaje się następująca linijka w pliku my.cnf :
default-character-set=latin2

Dobrym pomysłem jest również wykonywanietuż po połączeniu z bazą w swoich plikach php następujących kwerend:
SET NAMES 'latin2'
SET CHARACTER SET latin2

Aby było to wykonywane automatycznie powinno wystarczyć dodanie linijki:

w pliku my.cnf

Tabele dobrze tworzyć w następujący sposób (sposób porównywania napisów latin2_general_ci zostanie przyjęty automatycznie):
CREATE TABLE `tabela` (
`pole` INT NOT NULL
) TYPE = MYISAM  DEFAULT CHATRSET latin2;

Bazy danych dobrze tworzyć następująco:
CREATE DATABASE `test2` DEFAULT CHARSET latin2

Używanie tabel InnoDB


Jeżeli chce się używać tabel typu InnoDB (pozwalających dokonywać transakcji) trzeba być przygotowanym że każda kwerenda może zawieść z powodu deadlock-u.
Jak piszą w manualu do mysql-a deadlock-i nie są groźne o ile nie zdażają się tak często że uniemożliwiałyby wykonywanie kwerend (http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html)

Jeżeli Twoje skrypty są odpalane bardzo często, a do tego jeszcze w transakcje spinasz wiele długotrwałych kwerend, to zakleszczanie jest codziennością. Rozwiązań tego problemu jest kilka:
  1. zamykanie w transakcje jak najkrótszych kawałków kodu / kwerend;
  2. wykonywanie sekwencji kwerend zawsze w tej samej kolejności (wbrew pozorom nie jest to wcale łatwe do zrealizowania);
  3. jeżeli nie udaje się uniknąć zakleszczeń to można spróbować je wykrywać i ponawiać wykonywanie rolback'niętych transakcji.
Powyższe piszę z doświadczenia w pracy z M$ SQL Serverem 2000, ale to pewnie bez różnicy.
Paweł Walaszek

Z deadlockami można sobie radzić po prostu ponawiając wykonanie kwerendy do skutku w przypadku wystąpienia błędu spowodowanego deadloockiem:
// $query - kwerenda do wykonania
        do {
            $result = mysql_query($query);
            if(mysql_errno()==1213) {
                usleep(666666);
            }
        } while(mysql_errno()==1213); // deadlock occured

Jeżeli używamy kilku kwerend po kolej w transakcji to ponawiać trzeba wykonanie całej transakcji:
// $queries - tablica z kwerendami do wykonania w transakcji
        $queries = array_merge(array("START TRANSACTION"),$queries,array("COMMIT"));
        do {
            $result = true;
            $query = reset($queries);
            while(($query && ($result = ($result && mysql_query($query))))) $query = next($queries);
            if(mysql_errno()==1213) {
                usleep(666666);
            }
        } while(mysql_errno()==1213); // deadlock occured

XML bezpośrednio z bazy


Od wersji 5.1.5 MySQL'a dostępne są dwie funkcje, dzięki którymi możliwe jest komunikowanie się z bazą danych za pomocą XML'a.
Więcej na ten temat na stronie: http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
Funkcjonalność bardzo przydatna w przypadku stosowania technologii AJAX.
(szkoda, że 5.1.5 to dopiero alpha)

Backupowanie My SQL-a

Dobry opis jak powinno być zorganizowane backupowanie danych mysql można znaleźć tu:
http://dev.mysql.com/doc/refman/4.1/en/backup-policy.html

Jak uniknąć wywoływania za każdym razem SET NAMES przed jakąkolwiek inną kwerendą

Od mysql-a 4 moga być problemy z polskimi znakami.
Zamiast w swoich skryptach tuż po otwarciu połączenia wywołać za każdym razem

żeby obejść ten problem można ustawić w /etc/mysql/my.cnf

Pojawia się jednak wtedy problem podczas użycia phpMyAdmin-a
Próba wejścia w opcję Uprawnienia powoduje błąd:

Wystarczy wykonać kwerendę:
ALTER TABLE `mysql`.`user` CHANGE `Host` `Host` VARCHAR60 ) CHARACTER SET latin2 COLLATE latin2_bin NOT NULL ,
CHANGE `User` `User` VARCHAR16 ) CHARACTER SET latin2 COLLATE latin2_bin NOT NULL

żeby sytuacja się poprawiła.

Różne rodzaje konstrukcji JOIN w My SQL-u


Mimo występowania wielu rodzajów konstrukcji JOIN w mysql-u (INNER, OUTER, NATURAL, CROSS, STRAIGHT_JOIN) robią one tylko dwie różne rzeczy:
1. JOIN (robi to samo co przecinek przy wyborze z wielu tabel) – Złączanie pasujących rekordów z lewej i prawej tabeli wg. warunku umieszczonego po ON. Jeżeli brak pasującego wiersza w lewej bądź prawej tabeli to taki wiersz nie pojawi się w wyniku.
2. LEFT JOIN (analogicznie RIGHT) – Złączenie wszystkich rekordów z lewej tabeli z pasującymi (wg. warunku umieszczonego po ON) rekrodami z prawej. Jeżeli brak w prawej tabeli pasującego rekordu to w wyniku i tak pojawi się wiersz z lewej, tylko że w miejscu pól które normalnie zajmują dane z rekordu z prawej tabeli będą NULL-e.

Reszta słówek pojawiających się w JOIN nie ma żadnego istotnego efektu. INNER, CROSS i znak przecinka zamiast JOIN-a daje efekt nr 1.
OUTER nie robi nic. NATRUAL to skrócony zapis że tabele są łączone wg. pól które w obu tabelach mają te same nazwy. A STRAIGHT_JOIN wymusza użycie najpierw lewej tabeli potem prawej (może to mieć w specyficznych warunkach wpływ na szybkość wykonania).

Warunki umieszczone w ON dotyczące tylko jednej z tabel filtrują tabele jeszcze przed ich złączeniem. Może to być istotne w przypadku LEFT JOIN jeżeli chcemy dołączyć do jakiejś tabeli jedynie część innej tabeli.

konstrukcja zapytania join:

select (pola które chcemy uzyskać w wyniku zapytania)
from 
(tabela1) left/right join (tabela2) 
on  (warunek połączenia rekordów, lub warunek ograniczający rekordy z tabeli2) 
where (warunek na rekordy po połączeniu);

przykład z życia:

tabele:
-- Struktura tabeli dla `tcenniki`
-- 

CREATE TABLE `tcenniki` (
`id_firmy` int(11NOT NULL default '0',
`Ttowary_idTtowary` int(10) unsigned NOT NULL default '0',
`Cena` double default NULL,
`DataUstaleniaCeny` date default NULL,
KEY `Tcenniki_FKIndex1` (`Ttowary_idTtowary`)
) TYPE=MyISAM;
 
CREATE TABLE `tcennikstandard` (
`id_cenastandard` int(11NOT NULL auto_increment,
`Ttowary_idTtowary` int(11NOT NULL default '0',
`cena` double default NULL,
`datawyceny` date default NULL,
PRIMARY KEY (`id_cenastandard`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

zagadnienie:
w tabeli tcennikstandard mamy ceny wszystkich dostępnych towarów.
W tabeli tcennik mamy ceny specjalne dla nie których towarów przypisane do klienta (każdy klient może mieć inną cenę na dany towar.

Problem:
wybierz ceny wszystkich towarów dla wskazanego id_firmy w taki sposób, że jeśli dla danego towaru istnieje cena specjalna to zostanie ona podana, natomiast jeśli cena specjalna nie istnieje to zostanie podana cena standardowa, najważniejsze cena dla żadnego towaru nie może się powtórzyć.

rozwiązanie:
1. należy wybrać ceny specjalne dla danej firmy i z tabelki cennikstandard dobrać ceny dla produktów któych nie ma w tabeli specjalnej dla danego klienta.

LUB

2. wybrać wszystkie ceny z tabeli cennikstandard i dla produktów które mają cenę specjalną podmienić cenę.

wybierzemy rozwiązanie 2, sposób rozwiązania:
Zrobić left join na całej tabeli tcennikstandard i rekordami z tabeli tcennik z cenami dla wybranej firmy.
Problem: jak wybrać rekordy z tabeli przed połączeniem ?
opcja where działa dopiero na wyniku operacji join :(,
rozwiązanie:
w sekcji on mozna podać warunek wyboru rekordów z dołączanej tabeli ( czyli zostaną wybrane tylko rekordy które spełniąj podane warunki).

SELECT  ts.Ttowary_idTtowary, t.Cena,ts.cena,  if(t.cena is NULL ,ts.Cena,t.cena) as cena1, id_firmy 
FROM fakturowanie2006.tcennikstandard ts 
LEFT JOIN 
fakturowanie2006.tcenniki t  
ON t.Ttowary_idTtowary=ts.Ttowary_idTtowary and id_firmy=1;

Pożyteczne funkcje w My SQL

  • GROUP_CONCAT – pozwala skleić wartości danego pola ze wszystkich rekordów wchodzących w skład jednej grupy zdefieniowanej klauzulą GROUP BY, np.:
SELECT GROUP_CONCAT(DISTINCT k_imie,' ',k_nazwisko,' ',k_email SEPARATOR '\n') as klient FROM klienci

da nam listę imion i nazwisk i email klientów przy czym imię od nazwiska i nazwisko od e-maila oddzielać będzie spacja a kolejnych klientów będą oddzielać znaki nowego wiersza. Więcej o funkcjach nadających się do użycia z klauzulą GROUP BY tutaj: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Odczytywanie ilości wierszy pasujących do kwerendy ( mimo tego, że zwracane wyniki zostały obcięte klauzulą LIMIT )

Należy użyć w kwerendzie SQL_CALC_FOUND_ROWS

W ten sposób:
SELECT SQL_CALC_FOUND_ROWS imie, nazwisko FROM tabela WHERE fajna = 1 LIMIT 0,100

Potem wystarczy już tylko błyskawicznie pobrać wartość zwracaną przez kwerendę:
SELECT FOUND_ROWS()

Problemy przy imporcie i eksporcie.

Po wyeksportowaniu bazy mysqldump-em w pliku wynikowym ląduje linijka zaczynająca się od:

utf8 jest tam nawet wtedy gdy serwer mysql faktycznie eksportuje dane w innym kodowaniu. Prowadzi to do problemów podczas importu (np. polskie znaki wyglądają w bazje jak znak zapytania)

Aby pozbyć się problemu można już po wygenerowaniu pliku z eksportem wymienić w nim problematyczną linijkę na podobną ale w właściwym kodowaniem:

Uszkodzenia tabel My ISAM?

Czasem tabele ulegają uszkodzeniu. Wszystkie tabele które wymagają naprawy we wszystkich bazach można naprawić wydając polecenie:

 
Nie ma plików na tej stronie. [Wyświetl pliki/formularz]
Komentarze (0). [Dodaj/Wyświetl komentarze]

Informacje zanotowane na tej stronie sa publicznie dostępne. Każdy kto ma ochote może je wykorzystaż w dowolnym celu. Notujac tu coś godzisz sie z tym faktem.

Jeżeli widzisz na tej stronie treść lub kod Twojego autorstwa, na których upublicznianie przeze mnie sie nie godzisz daj mi znać.

Jeżeli uważasz informacje zamieszczone na tej stronie za użyteczne umieść linka do tej strony na własnych stronach. Dzięki temu większa ilość ludzi będzie mogła na tą stronę trafić.

Internetowy katalog stron internetowych