Správné MySQL kódování a case (in)sensitive

V dnešním článku budu počítat, že data chceme ukládat v kódování UTF-8 (UTF-16 či 32 je přijatelné, pokud víte, co děláte). Sice MySQL umožňuje vybrat jiné, ale plno chytrých lidí vyvíjí Unicode, aby pomocí jediného kódování šly ukládat všechny znaky všech jazyků světa, tak nevidím důvod používat něco jiného.

utf8 vs. utf8mb4

Starší utf8_ kódování dokáže ukládat pouze znaky zakódované na maximálně 3 bajty. Pokud se nějaký znak kóduje v UTF-8 do 4 bajtů, tak je MySQL jednoduše nezvládne. Proto v novějších verzích MySQL je kódování utf8mb4_, které i tyto znaky dokáže uložit právě pomocí 4 bajtů. Z toho jednoduše vyplývá, používat hlavně utf8mb4_ kde to jde.

_unicode_ci vs. _general_ci vs. _czech_ci ...

Všechna kódování začínající utf8mb4_ jsou jak název napovídá UTF-8, co náleží za prvním _ blíže určuje collation, neboli způsob porovnávání. Na samotné ukládání to nemá přímý vliv, ale špatný výběr může také mít vedlejší následky, například při využití indexu nebo seřazování.

_general_ci nepoužívat vůbec

Hodně lidí by asi napadlo využít utf8mb4_general_ci jako hlavní kódování. Problém ale je, že obsahuje velmi jednoduchý algoritmus porovnávání, a tím pádem není správný. Například německé ẞ (řazení jako ss) nebo latinské Œ (řazení jako oe) seřadí špatně.

_unicode_ci nebo _czech_ci

Nejsprávnější by měl být utf8mb4_unicode_ci, který kombinuje pravidla všech jazyků dohromady a jeho řazení by mělo být nejsprávnější. Je ale asi jasné, že nebude, protože české CH by se podle jiných jazyků zařadilo do skupiny s C. Jen ale v češtině by CH mělo být mezi H a I. Protože některé jazyky mají jiné požadavky, implementuje MySQL lokální collation pro jednotlivé jazyky.

Nejlepší tedy je využít utf8mb4_unicode_ci, které bude všeobecně fungovat nejlépe. A jen až v případě, kdy řazení vyžaduje správné české řazení, může se to v klauzuli ORDER BY specifikovat. Na obrázku výše je při řazení pomocí češtiny vše správně, protože jazykové kódování jsou založeny na utf8mb4_unicode_ci + specifika jazyka. Pak je ale jasné, že pro jiné jazyky, které tam jsou uvedeny, může být více chyb při použití _czech_ci než při použití _unicode_ci. Je tedy pouze na uživateli, co je pro něj výhodnější. Osobně jsem pro použití hlavně utf8mb4_unicode_ci.

SELECT *
FROM `table_name`
ORDER BY `col_name` COLLATE utf8mb4_czech_ci

Case (in)sensitive - rozdíl velkých a malých písmen

Poslední část za podtržítkem jsou 2 písmena _cs (case sensitive) nebo _ci (case insensitive). V MySQL je UTF-8 kódování vždy case insensitive, tudíž A = a. Pro vyhledávání to může být výhodné, do doby, než je potřeba ukládat např. unikátní kódy, kde hraje velikost písmen roli. Tento problém nastal s platební bránou od ČSOB, kde platby jsou identifikovány právě case sensitive kódem.

Nyní nastupuje na řadu kódování utf8mb4_bin, nebo zvláštní kódování binary. Pokud probíhá porovnání, kontrolují se přímo jednotlivé bajty tak jak jdou po sobě. Nyní se už a != A. Rozdíl mezi oběma kódováními však je, a to v řazení. Binary řadí data bajt po bajtu, proto více bajtové znaky se rozdělí a řazení může být úplně špatně. A hlavně, následující kód funguje pouze pro utf8mb4_bin, pro binary ne.

-- Pro sloupec utf8mb4_bin v bude fungovat
-- Pro sloupec binary vyhodí chybu
SELECT *
FROM `table_name`
ORDER BY `col_name` COLLATE utf8mb4_czech_ci

Detailnější popis problému je na stránkách MySQL: charset-unicode-set a charset-unicode-utf8mb4.

---

Máte jiné zkušenosti s kódováním v MySQL, nebo byste rádi něco doplnili? Podělte se v komentářích.