Hlavní navigace

Co nefunguje v MySQL a jak to obejít

Jakub Vrána 16. 12. 2009

Velmi rozšířený a oblíbený 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 některé pokročilejší funkce. Na část vlastností je ale potřeba si dát pozor, některé z nich přímo nefungují. V následujícím článku uvádím ty nejviditelnější 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;

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.

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ž 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.

Našli jste v článku chybu?

16. 12. 2009 16:52

soucasti standardu to je, ale az v SQL2008 – co vim, tak standardni zapis podporuje snad jen PostgreSQL a Sybase Anywhere

Podnikatel.cz: Alza.cz má StreetShop. Mall.cz více výdejních míst

Alza.cz má StreetShop. Mall.cz více výdejních míst

Vitalia.cz: Manželka je bio, ale na sex moc není

Manželka je bio, ale na sex moc není

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte

Vitalia.cz: Znáte „černý detox“? Ani to nezkoušejte

Znáte „černý detox“? Ani to nezkoušejte

120na80.cz: Jak oddálit Alzheimera?

Jak oddálit Alzheimera?

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

Měšec.cz: Jak vymáhat výživné zadarmo?

Jak vymáhat výživné zadarmo?

Lupa.cz: Insolvenční řízení kvůli cookies? Vítejte v ČR

Insolvenční řízení kvůli cookies? Vítejte v ČR

Lupa.cz: Seznam mění vedení. Pavel Zima v čele končí

Seznam mění vedení. Pavel Zima v čele končí

Vitalia.cz: Dáte si jahody s plísní?

Dáte si jahody s plísní?

DigiZone.cz: Sat novinky: slovenská TV8 HD i ruský NTV Mir

Sat novinky: slovenská TV8 HD i ruský NTV Mir

Podnikatel.cz: Babiš: E-shopy z EET možná vyjmeme

Babiš: E-shopy z EET možná vyjmeme

Podnikatel.cz: Víme první výsledky doby odezvy #EET

Víme první výsledky doby odezvy #EET

Podnikatel.cz: Chtějte údaje k dani z nemovitostí do mailu

Chtějte údaje k dani z nemovitostí do mailu

Lupa.cz: Co se dá měřit přes Internet věcí

Co se dá měřit přes Internet věcí

Vitalia.cz: Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Podnikatel.cz: Na poslední chvíli šokuje vyjímkami v EET

Na poslední chvíli šokuje vyjímkami v EET

Vitalia.cz: Často čůrá a má žízeň? Příznaky dětské cukrovky

Často čůrá a má žízeň? Příznaky dětské cukrovky

Měšec.cz: Air Bank zruší TOP3 garanci a zdražuje kurzy

Air Bank zruší TOP3 garanci a zdražuje kurzy