Co nefunguje v MySQL a jak to obejít
Školení, která pořádám 
Článek vyšel na serveru Root.cz.
Databázový server MySQL udělal za posledních několik let významný pokrok a k jednoduchému rychlému úložišti přidal i pokročilejší funkce. Některé věci ale nefungují nebo je potřeba si na ně dát pozor. Článek uvádí některé z nich.
U sloupce typu date nelze nastavit výchozí čas na aktuální
Problém: Hodnota DEFAULT CURRENT_TIMESTAMP z historických důvodů funguje pouze u sloupce typu timestamp a navíc ji lze použít jen u jednoho takovéhoto sloupce v tabulce.
Řešení: Častý požadavek na existenci dvou sloupců vytvoreno a zmeneno se tedy řeší poněkud krkolomně. Dá se zajistit vytvořením BEFORE INSERT triggeru:
CREATE TRIGGER tabulka_bi BEFORE INSERT ON tabulka FOR EACH ROW SET NEW.vytvoreno = NOW()
Sloupec zmeneno může být klasický timestamp.
Sestupné indexy
Problém: MySQL při definici indexů ignoruje požadavek na sestupné třídění položek – (skupina DESC, poradi) vytvoří stejný index jako (skupina, poradi).
Řešení: MySQL dokáže takovýto index použít i pro sestupné třídění, důležité ale je, aby pořadí všech částí indexu bylo při třídění stejné: ORDER BY skupina DESC, poradi DESC index využije, dotaz ORDER BY skupina DESC, poradi ne. Pokud to nedokážeme zaručit, můžeme do tabulky vložit opačnou hodnotu sloupce a řadit podle něj, obvykle to ale potřeba není.
Indexy nad výsledkem funkce
Problém: MySQL na rozdíl třeba od PostgreSQL nedovoluje vytvářet indexy nad výsledkem funkce. Pokud na sloupec v dotazu aplikujeme nějakou funkci, tak se index až na výjimky nepoužije.
Řešení: Při porovnávání je tedy vhodné indexované sloupce uvádět samotné:
-- index se nepoužije SELECT * FROM tabulka WHERE zmeneno + INTERVAL 1 DAY >= NOW(); -- použije se index nad sloupcem (zmeneno) SELECT * FROM tabulka WHERE zmeneno >= NOW() - INTERVAL 1 DAY;
Využití indexů v poddotazu
Problém: MySQL nedokáže využít indexy pro setřídění výsledků poddotazu.
Řešení: Pokud z poddotazu vytvoříme pohled, MySQL indexy využít dokáže.
Materializované pohledy
Problém: Pohledy se v MySQL vyhodnocují při každém dotazu znovu. Na rozdíl od jiných databázových serverů nedokáže MySQL vytvořit tzv. materializovaný pohled, který by data fyzicky ukládal (a při změně aktualizoval) a nad kterým by třeba šly definovat i indexy.
Řešení: Vyřešit se to obvykle dá doplněním dopočítávaných sloupců a jejich automatickou aktualizací pomocí triggerů, dá to ale dost práce.
Trigger nemůže měnit stejnou tabulku
Problém: Trigger nemůže měnit data ve stejné tabulce, pro kterou je definován.
Řešení: Žádný work-around neznám, ale pokud nám stačí upravit modifikovaný záznam, lze to udělat změnou hodnot v „tabulce“ NEW.
Stejné omezení platí i pro poddotazy při modifikaci záznamu – ty se také nemohou dotazovat do stejné tabulky. Takový příkaz je nutné rozdělit do dvou – nejprve získat data a v druhém kroku provést aktualizaci. Toto omezení lze obejít pomocí druhého poddotazu.
Triggery se nespustí při kaskádovém mazání
Problém: Pokud definujeme cizí klíč s příznakem ON DELETE CASCADE a v tabulce je definovaný trigger pro smazání, tak se tento trigger nespustí, pokud se záznam smaže v důsledku kaskády.
Řešení: Řešení je pro tabulky s takovýmto triggerem nepoužívat kaskádové mazání a záznamy mazat ručně. Často ale trigger pouze mění záznam v rodičovské tabulce, kdy nám jeho nespuštění nemusí vadit.
Příkazy ukončující transakci
Problém: Všechny příkazy pracující se strukturou tabulek vyvolají implicitní COMMIT právě probíhající transakce. Platí to i pro další příkazy, např. ty pro práci s uživateli a donedávna třeba i pro příkaz LOAD DATA.
Řešení: V transakcích je tedy vhodné používat jen příkazy manipulující s daty.
Omezující podmínky
Problém: MySQL ignoruje omezující podmínky definované klauzulí CHECK při vytváření tabulky.
Řešení: Obejít se to dá triggerem, který v případě nesplnění podmínky vyvolá chybu:
CREATE TRIGGER uzivatel_bi BEFORE INSERT ON uzivatel FOR EACH ROW IF CHAR_LENGTH(NEW.login) < 3 THEN DO `Login musí mít alespoň tři znaky.`; END IF
Stejný trigger bychom samozřejmě museli definovat i pro změnu záznamu.
Všimněte si také krkolomného způsobu vyvolání chyby, který navíc MySQL obalí hláškou, že sloupec daného jména neexistuje. V MySQL totiž dosud neexistuje příkaz SIGNAL, který by se dal pro vyvolání chyby použít.
Závěr
MySQL je stále mladý databázový server a otázkou je, jestli budou uvedené nedostatky v blízké budoucnosti odstraněny. Lepší je proto o nich vědět a naučit se s nimi žít.
Pokud jste narazili na další gotchas, tak s nimi čtenáře můžete seznámit v diskusi.
Přijďte si o tomto tématu popovídat na školení Konfigurace a výkonnost MySQL.
Diskuse
Honzy:
* Trigger nemůže měnit stejnou tabulkuasi jsem presne nepochopil, co to ma presne znamenat, protoze menit data trigerem nad stejnou tabulkou imho jde. No v nejhorsim si udelam trapas:)
CREATE TRIGGER `vypocet_urok_castka` BEFORE INSERT ON `exekuce`
FOR EACH ROW
SET NEW.`urok_castka` = (ABS(DATEDIFF(NEW.`od`, NEW.`do`)) * NEW.`jistina` * NEW.`urok`)
--------
# v insert/update atribut `urok_castka` neni
# vsechny atributy vyse jsou v tabulce `exekuce` (nad kterou je trigger definovan)
# stejne to funguje u BEFORE UPDATE
--------
pokud jsem nejak vedle, tak se omlouvam a klidne to smazte.
Jakub Vrána 
:
Modifikovaný záznam měnit samozřejmě jde, ale nelze pracovat s ostatními záznamy v tabulce.
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 