3 Adatok tárolása Ebben a fejezetben azokkal az SQL- és MySQL-parancsokkal foglalkozunk, amelyekkel adatokat adhatunk a táblákhoz. Nem térünk ki a táblák programozott módon történõ feltöltésére, sem a már a táblákban szereplõ adatok frissítésére. (Ezekrõl a 8., illetve az 5. fejezetben lesz szó.)
Táblák feltöltése adatokkal # Általános forma INSERT tábla_neve (oszlopok, listája) VALUES (értékek, listája); # Egy sort ad a book táblához INSERT book (title, author, cond) VALUES ('Where the Wild Things Are', 'Maurice Sendak', 'fine');
Sorokat egy már létezõ táblához az egészen egyszerû INSERT paranccsal adhatunk.
MySQL zsebkönyv
Tipp Ebben a listában nem csak értékek szerepelhetnek; függvényhívások, változók és más dolgok is megengedettek. Lásd a fejezet A dátum és az idõ beszúrása (MySQL függvénnyel) címû részét a részletekért.
• VALUES – Azt jelzi a MySQL-nek, hogy értékek felsorolásának kell következnie. • (értékek, listája, ...) – Értékek felsorolása zárójelben, a megadott oszloplistának megfelelõen.
Tipp Az oszloplista nem kötelezõ. Ha mellõzzük, az azt jelenti, mintha az összes oszlop szerepelne a felsorolásban. Ezt a lehetõséget azonban egyáltalán nem ajánlott kihasználni, mert a táblák oszlopainak sorrendje megváltozhat.
• INSERT – A parancs eleje. • tábla_neve – A már létezõ tábla neve (például book), amelybe egy vagy több sornyi adatot szúrunk be. • (oszlopok, listája, ...) – Az oszlopnevek felsorolása zárójelben. Az itt fel nem sorolt oszlopok esetében a beszúrt sorok az alapértelmezett értéket fogják tartalmazni.
Az utasítás kulcsfontosságú elemei a következõk:
28
3. fejezet • Adatok tárolása
Több sor megadása egyetlen lekérdezésben az INSERT utasítással INSERT book (author, title, cond) VALUES ('Maurice Sendak', 'In the Night Kitchen','mint'), ('Caroll Spinney', ¯ 'How to Be a Grouch','poor'), ('Dr. Seuss', 'Green Eggs and Ham', 'good');
Az INSERT parancs több értéklistával egyetlen lekérdezésben több sort is be tud szúrni. Az értékhalmazokat egyenként zárójelbe kell tenni és vesszõvel elválasztani egymástól. Az utolsó felsorolást pontosvesszõ zárja. Ha egyszerre több sornyi adatot szúrunk be egyetlen SQLlekérdezéssel, akkor amellett, hogy rengeteg gépelést takarítunk meg, hatékonyabbá is tesszük a mûködést: a MySQLnek kevesebb dolga van így, mintha külön-külön végezné a lekérdezéseket.
Egyedi sorszámok megadása az AUTO_INCREMENT utasítással CREATE TABLE tábla ( oszlop INT AUTO_INCREMENT, ... );
29
MySQL zsebkönyv
Az AUTO_INCREMENT (automatikusan növekvõ sorszámú) oszlopok lehetõvé teszik, hogy a táblák soraihoz automatikusan rendeljünk számokat. Mindig, amikor egy új sort adunk a táblához, egy nulla érték (hacsak más értéket nem határozunk meg az AUTO_INCREMENT oszlopoknak), illetve az adott AUTO_INCREMENT oszlopban tárolt legnagyobb érték rendelõdik az oszlophoz. Ugyanezt a hatást érjük el, ha egy NULL értéket szúrunk be az oszlopba. Ha így járunk el, azzal a kódot olvasók számára egyértelmûvé tesszük, hogy az oszlopoknál automatikus sorszámbeillesztést alkalmazunk. Tanulmányozzuk, majd futtassuk az alábbi kódot, amely az AUTO_INCREMENT alkalmazásának kitûnõ példája: CREATE TEMPORARY TABLE demo ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); INSERT demo () VALUES (); SELECT id FROM demo; # az id érték 1 INSERT demo (id) VALUES (NULL); SELECT id FROM demo; # az id értékek 1 és 2 INSERT demo (id) VALUES (4); SELECT id FROM demo; # az id értékek 1, 2 és 4 INSERT demo (id) VALUES (NULL); SELECT id FROM demo; # az id értékek 1, 2, 4 és 5
Egy táblában csak egyetlen AUTO_INCREMENT oszlop lehet, és ennek az elsõdleges kulcs részének kell lennie. Amikor AUTO_INCREMENT oszlopot tartalmazó sort törlünk egy táblából, az oszlopok sorszámozásának folyamatossága csorbát szenved (szünetek keletkeznek). Emiatt ne ag30
3. fejezet • Adatok tárolása
gódjunk, csak akkor, ha az AUTO_INCREMENT oszlopba szúrt érték megközelíti az oszlopban tárolható legnagyobb értéket – ez egy INT oszlop esetén több mint 2 milliárd. Lehet, hogy nem elegáns megoldás, de az adatainkkal akkor bánunk következetesen, ha nem használjuk újra a (törölt) sorszámot egy másik sorban. Ha vészesen közeledünk az oszlopok terhelhetõségének maximumához, az AUTO_INCREMENT sorozat újraszámozása elõtt változtassuk az oszlop típusát olyanra, ami nagyobb értékeket tud tárolni.
A dátum és az idõ beszúrása (MySQL-függvénnyel) INSERT valamilyen_tábla (valamilyen_oszlop) ¯ VALUES (NOW());
A fenti SQL-töredék a NOW() nevû MySQL-függvénnyel szúrja be az aktuális dátumot és idõt egy képzeletbeli tábla (valamilyen_tábla) valamilyen_oszlop nevû oszlopába. Amikor a NOW()-t meghívjuk, az az aktuális dátummal és idõvel tér vissza (másodpercre pontosan) azon a gépen, amelyen a MySQL-kiszolgáló fut. A függvény a szöveget alapértelmezetten emberi olvasásra szánt formában, azaz szóközökkel és írásjelekkel tagolva adja vissza (például 2005-09-04 01:43:37). Kipróbálásához futtassuk a SELECT NOW() függvényt.
31
MySQL zsebkönyv
Ha a dátumot és az idõpontot számkarakterekbõl álló karakterláncként szeretnénk megjeleníteni (például 20050904014624 formában), adjunk egy nullát a NOW() értékéhez. Az utasítás ekkor így néz ki: SELECT NOW()+0; Tipp A MySQL-ben rengeteg hasznos függvényt és mûveleti jelet (operátort) találunk, amelyekkel matematikai mûveletek hajthatók végre, vagy oszlopok és értékek összehasonlításán alapuló feltételes kimenet állítható elõ, és még sorolhatnánk. Ezekrõl az internetes kézikönyvben olvashatunk.
SQL-parancsok betöltése fájlból shell> mysql -u felhasználó_neve ¯ -p adatbázis_neve < fájl_neve.sql mysql> \. fájl_neve.sql
A könyv példáit úgy terveztük meg, hogy azokat elolvasva párbeszédesen a mysql konzolon, azaz a MySQL parancssori ügyfélprogramba írhassuk be. Ugyanilyen egyszerû megoldás, ha a parancsokat egyszerû szövegfájlokban tároljuk, majd a mysql-lel (vagy más ügyféllel) futtatjuk azokat. Az elsõ példában a mysql-t kötegelt üzemmódban futtatjuk a parancssorban. Ebben a módban a mysql végrehajt néhány parancsot, kimenetet ad (ha van), majd kilép.
32
3. fejezet • Adatok tárolása
Konkrétabban, az elsõ példában a mysql a következõket teszi: 1. Kapcsolódik a MySQL-kiszolgálóhoz a megadott felhasználónévvel (felhasználó_neve). 2. Kér egy jelszót, amellyel megpróbálja azonosítani a felhasználót. 3. Alapértelmezettként beállítja az adatbázis_neve adatbázist. 4. A kiszolgálónak elküldi a fájl_neve.sql tartalmát. 5. Megjeleníti a parancs eredményét (ha van). 6. Kilép. A második példában az ügyfélprogram beolvassa a fájl_neve.sql tartalmát, és elküldi a kiszolgálónak. Fontos, hogy a mysql parancssori ügyfélprogramot interaktív üzemmódban kell használnunk. Tipp A \. (slash, dot; perjel, pont) parancsnak (remélhetõleg) nincs köze a hírhedt Slashdothoz, amely egy technikai témájú internetes újság. A név valójában a UNIX héj forrásparancsára vezethetõ vissza, amellyel a fájlok szövege héjparancsok halmazaként dolgozható fel. A parancs a legtöbb UNIX-héjban a shell> .valamilyen_fájl_neve-ként hívható meg.
Adatok betöltése másik táblából # Az INSERT ... SELECT általános utasításformája INSERT elsõ_tábla (oszlopok, listája) SELECT ...;
33
MySQL zsebkönyv
Az INSERT parancsot összeköthetjük a SELECT-tel, így könnyen másolhatunk sorokat egy táblából egy másikba. A parancs utasításformája nagyon egyszerû. Vegyük az INSERT parancs elejét (a VALUES záradékig), és toldjuk hozzá a SELECT parancsot. Az a lényeg, hogy a SELECT visszaadjon egy oszlopot minden olyan oszlophoz, amit az INSERT megkövetel. A példa az INSERT ... SELECT parancs általános alakját mutatja. Gyakorlati alkalmazására az 5. fejezetben találunk egy összetett példát.
Adatok betöltése szövegfájlokból LOAD DATA INFILE 'valamilyen_fájl.txt.' INTO TABLE 'valamilyen_tábla' (oszlopok, ¯ listája ...);
Úgy a legegyszerûbb adatokat átvinni más alkalmazásokból a MySQL-be, ha az adatokat valamilyen strukturált szövegformátumban mentjük (például tabulátorokkal tagolt értékekként), majd betöltjük azt a MySQL-be. A betöltés (importálás) jelentheti azt, hogy az adatokat lekérdezéshalmazokká alakítjuk, ám ez a megoldás nehéz-
a strukturált szövegfájlok adatait a MySQL-be tölteni.
kes és számos hibalehetõséget rejt magában. A LOAD DATA INFILE paranccsal sokkal egyszerûbb (és gyorsabb)
A LOAD DATA INFILE alapértelmezetten tudja, hogyan töltse be a tabulátorral tagolt érték formátumú fájlokat.
34
3. fejezet • Adatok tárolása
Az ilyen formátumban a fájl mezõit tabulátorjelek választják el egymástól, a sorokat pedig újsor karakterek. Amikor a parancs beolvassa az adatfájl egyik sorát, a céltáblába beszúr egy sort a megadott oszlopelrendezést használva. Tegyük fel, hogy a következõ szövegfájlt a book táblánkba szeretnénk betölteni: Where the Wild Things Are→Maurice Sendak→fine Cigars of the Pharoah→Herge→good ...→...→...
Ha a fájl a /Users/zak/books_and_authors.txt könyvtárban található, a szöveg az alábbi paranccsal tölthetõ be: USE library; LOAD DATA INFILE '/Users/zak/books_and_authors.txt' INTO TABLE book (title, author, cond);
Ennek eredményeképpen a book táblában az alábbi új sorok jönnek létre: +--------+---------------+---------------+------+ |book_id | title | author | cond | +--------+---------------+---------------+------+ | 5 | Cigars of ... | Herge | good | | 6 | Where the ... | Maurice Sendak| fine | | 7 | ... | ... | ... | +--------+---------------+---------------+------+
35
MySQL zsebkönyv
Akad néhány kulcsfontosságú tudnivaló a LOAD DATA INFILE paranccsal kapcsolatban:
• Az adatfájl ugyanazon a gépen kell, hogy legyen, mint a MySQL-kiszolgáló. Tipp Ha távoli MySQL-kiszolgálóval dolgozunk, és az adatokat egy helyi fájlból szeretnénk a kiszolgálóra tölteni, a LOCAL kulcsszó használatára van szükség. Errõl további információt a http://mysql.com/LOAD DATA INFILE weboldalon találhatunk. • Meg kell adnunk a fájl teljes elérési útját, ahonnan adatokat töltünk be. Ha a fájlnevet elérési út nélkül adjuk meg, a MySQL-kiszolgáló az adatkönyvtárában fog keresni. Ha a LOCAL kulcsszót használjuk, és nem határozunk meg egy teljes útvonalat, a MySQL-ügyfél abban a könyvtárban fog keresni, amelybõl indítottuk. • A Windows rendszereken is perjeleket kell használni az elérési útban (nem fordított perjelet). Tehát Windowson a UNIX típusú útvonal ('/Users/zak/ books_and_authors.txt') a következõképp fog kinézni: 'C:/Desktop/book_and_authors.txt'. Ha az elõbbi útvonalban fordított perjeleket használtunk volna, akkor azokat a feldolgozó védõkarakterekként értelmezné.
• Ha a parancs kimenetét figyelmeztetés vagy hibaüzenet követi (például Query OK, 3 rows
Ráadásként jöjjön még néhány tipp, hogy könnyebben boldoguljunk a LOAD DATA INFILE-lal:
36
USE library;
3. fejezet • Adatok tárolása
affected, 1 warning (0.01 sec)), a konkrét problémákat a SHOW WARNINGS; és SHOW ERRORS; parancsokkal fedhetjük fel. • Azt, hogy jól állítottunk-e be egy bonyolult LOAD DATA INFILE-lekérdezést, úgy ellenõrizhetjük, hogy létrehozunk egy ideiglenes táblát, ami ugyanúgy néz ki, mint amelyiken dolgozni szeretnénk. A parancsot erre az ideiglenes táblára adjuk ki. Teljesítmény- vagy tárhelyproblémák esetén csökkentsük az ideiglenes táblába betöltendõ adat mennyiségét. Tehát ha a példa kódját akarnánk tesztelni, mielõtt élesben végrehajtjuk az adatbázisunkban, ezt a következõ lépéseket követve tehetjük meg:
# Létrehoz egy ideiglenes (temp) táblát # az igazi mintájára CREATE TEMPORARY TABLE book_temp LIKE book; # Betölti az adatok egy részét LOAD DATA INFILE ¯ '/Users/zak/books_and_authors.txt' INTO TABLE book_temp (title, author, cond) IGNORE 195 LINES; # Figyelmen kívül hagyja az elsõ 195 sort SELECT * FROM book_temp; # Ellenõrzi, hogy minden rendben van-e
• A parancsnak több kapcsolója is van, amelyekkel szabályozható az adatok betöltésének menete, például az, hogy milyen formátumú adat lesz beolvasva, hány sor legyen betöltve stb. 37
MySQL zsebkönyv
Részleteket a http://mysql.com/LOAD DATA INFILE weboldalon találunk.
Adatok gyors beszúrása Ha gyakran elõfordul, hogy sok sornyi adatot adunk egy táblához, és lassúnak találjuk a mûveletet, olvassunk tovább, mert ennek a problémának a megoldásához adunk most tippeket. Különbözõ módszereket alkalmazhatunk, attól függõen, hogy a több sort egyszerre vagy egy bizonyos idõ alatt szeretnénk betölteni, esetleg sok olvasási mûveletre is szükségünk van közben.
• MyISAM-tábláknál zároljuk a táblát a beszúrás idejére, hogy az olvasási mûveletek ne lassítsák az írást.
ALTER TABLE tábla_neve DISABLE KEYS; # A sok-sok INSERT utasítás helye ALTER TABLE tábla_neve ENABLE KEYS;
• Ha megoldható, használjuk a LOAD DATA INFILE-t (nézzük meg a fejezet Adatok betöltése szövegfájlokból címû részét). Ez elõnyös a teljesítményre nézve, mert csökkenti mind az ügyfél, mind a kiszolgáló által elvégzendõ munkát, amely az adatok táblákba való beszúrásához szükséges. • Az ALTER TABLE paranccsal ideiglenesen kikapcsolható az indexelés nagy mennyiségû adat beszúrásánál. Ha az adatok a helyükre kerültek, utána az indexek létrehozása egyetlen lépéssel elvégezhetõ, például így:
Több sor egyidejû beszúrásához próbáljuk ki a következõ optimalizálási megoldásokat:
38
3. fejezet • Adatok tárolása
Ehhez az alábbi kódot használhatjuk: LOCK TABLES tábla_neve WRITE; # A sok-sok INSERT utasítás helye UNLOCK TABLES;
• InnoDB-tábláknál az INSERT utasításokat a következõ kóddal hajtsuk végre: BEGIN; # A sok-sok INSERT utasítás helye COMMIT;
• Használjunk többértékû INSERT-et. Az ilyen típusú utasítások csökkentik az ügyfélprogram és a kiszolgáló által elvégzendõ munka mennyiségét, amely a lekérdezések feldolgozásához kell az adatok táblába írása elõtt. Lásd a fejezet elején található Több sor megadása egyetlen lekérdezésben az INSERT utasítással címû részt. Ha több sornyi adatot kell beszúrnunk, miközben nagyszámú olvasási mûvelet is folyik, alkalmazzuk az alábbi optimalizálási megoldásokat:
ALTER TABLE tábla_neve DELAY_KEY_WRITE = 1;
• MyISAM-tábláknál kapcsoljuk be a DELAY_KEY_WRITE kapcsolót. Ez a táblakapcsoló csökkenti az olyan lemezírások számát, amelyeket a MySQL-nek akkor kell elvégeznie, amikor egy tábla indexében új bejegyzéseket hoz létre. Az alábbi kód alapján állíthatjuk be a kapcsolót egy létezõ táblára az ALTER TABLE paranccsal: Figyelem! Ha lefagy a MySQL-kiszolgáló, elõfordulhat, hogy a DELAY_KEY_WRITE-ra állított táblák indexelése félbemarad. Úgy gyõzõdhetünk meg az indexelés elké39
MySQL zsebkönyv
szültérõl, hogy a MySQL-kiszolgálót újraindítjuk a -myisam-recover=BACKUP, FORCE utasítással.
• Használjunk InnoDB-táblákat, mert ezek ügyesebben kezelik a párhuzamos írást és olvasást, mint a MyISAM. • Ne írjunk abba a táblába, amelyikbõl olvasunk. Hatékonyabb, ha külön táblát hozunk létre az írás kezelésére, majd a korábban ismertetett több sort beszúró paranccsal szabályos idõközönként egyesítjük az írásra használt tábla sorait a másikéval. Sok más módja van még a MySQL hatékonyabbá tételének. A hardverbeállítástól kezdve a kiszolgáló finomhangolásán át a táblatervezésig és az SQL-optimalizálásig sokféle lehetõség közül válogathatunk. Az internetes kézikönyvbõl további ötleteket meríthetünk.
További információk
http://dev.mysql.com/doc/refman/5.0/en/ example-auto-increment.html
• Az AUTO_INCREMENT használata –
http://dev.mysql.com/doc/refman/5.0/en/ data-manipulation.html
• Adatkezelõ utasítások –
A fejezet témájához az internetes kézikönyv alábbi részei kapcsolódnak:
40
