MySQL (4.1 a vyšší) a čeština minifaq

Úvod

S příchodem verze 4.1 přinesla MySQL nový systém práce se znakovými sadami, kódováními a řazením. Je podrobně popsán v 9. kapitole MySQL manuálu, ale nikdo se zjevně nenamáhá si ho přečíst. Proto vzniklo tohle minifaq.

Za případné škody způsobené použitím těchto rad neručím :-).

Dotazy ohledně MySQL a češtiny můžete klást ve fóru o databázích na Builderu.

Obsah

  1. Úvod
  2. Obsah
  3. Obecné dotazy
  4. Upgrade ze starších verzí
  5. Programovací jazyky
  6. SQL
  7. Ostatní

Obecné dotazy

Jaké (reálně použitelné) znakové sady (kódování) a řazení nabízí MySQL pro český jazyk?
Znaková sada (kódování)Řazení
Unicode (kódování UCS-2)ucs2_czech_ci
Unicode (kódování UTF-8)utf8_czech_ci
Windows CP1250cp1250_czech_cs
ISO 8859-2latin2_czech_cs
Přípona ci znamená Case Insensitive, tedy řazení bez ohledu na velikost písmen, cs pak Case Sensitive, tedy opak.
Jakou verzi phpMyAdmina mít pro bezproblémovou práci se znakovými sadami a řazením?
2.6.3 a vyšší
Jakým SQL příkazem vypíšu informace o tabulce včetně znakové sady a řazení?
SHOW FULL COLUMNS FROM tabulka;
Od MySQL 5.0 pak také standardně pomocí SELECT:
SELECT column_name, collation_name FROM information_schema.columns WHERE table_schema='jmeno databaze' AND table_name='tabulka';

Upgrade ze starších verzí

Mám tabulku v latin1_swedish_ci (typicky pohrobek ze starších verzí), která obsahuje znaky v ISO-8859-2 nebo Windows CP1250. Jak ji převedu do ISO-8859-2/CP1250 ?
Příslušné sloupce typu CHAR/VARCHAR/TEXT převeďte na sloupce typu BINARY/VARBINARY/BLOB naprosto stejných parametrů. Ty pak zkonvertujte na CHAR/VARCHAR/TEXT ve správném kódování. Lze provést v phpMyAdminu nebo SQL příkazy. (Před akcí si vše pro jistotu zazálohujte!)

Programovací jazyky

PHP: jak odstranit otazníky na místech českých znaků?
Musíte si před jakýmkoli jiným SQL příkazem říci pomocí SET NAMES v jakém kódování vám má MySQL data posílat. Musí být stejné jako to v kterém máte stránku napsanout! Tzn. musí odpovídat metatagu Content-Type, respektive odpovídající HTTP hlavičce! Příklad: <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=Windows-1250" /> </head> <body> <?php mysql_connect('localhost', 'root') or die('Could not connect'); mysql_select_db('aaa') or die('Could not select database "aaa"'); mysql_query("SET NAMES 'cp1250'") or die('Could not set names'); $result = mysql_query("SELECT b FROM a ORDER BY b"); while ($row = mysql_fetch_object($result)) { echo $row->b . '<br>'; } ?> </body> </html> Jak má vypadat SET NAMES pro různá kódování?
KódováníSET NAMES příkaz
CP1250SET NAMES 'cp1250'
ISO-8859-2SET NAMES 'latin2'
UTF-8SET NAMES 'utf8'
Jak má vypadat metatag Content-Type pro různá kódování?
Kódovánímetatag Content-Type
CP1250<meta http-equiv="Content-Type" content="text/html; charset=Windows-1250" />
ISO-8859-2<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-2" />
UTF-8<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
Na čem naopak závislé není je kódování použité v tabulce. Používá-li tedy databáze např. Windows CP1250 a chcete ji vypsat na stránce v ISO-8859-2, použijete SET NAMES pro ISO-8859-2 a nikoli pro CP1250.
PHP 5.1 a mysqli (MySQL Improved Extension)
Pod PHP 5.1, pokud používáte mysqli, existuje ještě možnost volat metodu set_charset(). To je ekvivalentní SQL příkazu SET NAMES, s tím, že by navíc měla být funkce (real_)escape_string() měla být schopna takto předanou informaci o kódování využít, což by mohlo být užitečné, pokud ji chcete použít s kódováním, které nemá escapované znaky (jako je např. znak konce řádku) na pozicích odpovídajících klasickému ASCII. Takovým kódováním je např. UCS-2.
Java: jak má správně vypadat connection string pro JDBC?
"jdbc:mysql://server/databaze?user=jmeno&password=heslo&useUnicode=true&characterEncoding=UTF-8"
parametr UTF-8 specifikuje kódování pro přenos dat mezi MySQL databází a JDBC ovladačem, tzn. bude vám bude fungovat pro libovolné kódování použité v tabulce (cp1250, ISO-8859-2). Pokud opravdu toužíte po jiném, vyberte si z této tabulky. (Ale opiště jeho název přesně! Chybný název nevyvolá chybu (nevyhodí výjimku)! Jenom se vám zmrší čeština :-) )
Python
Podobně jako v PHP pošlete SET NAMES. Např. skript v kódování CP1250, který nejprve vloží data do tabulky a pak je odtamtud přečte a vypíše na konzoli Windows (CP852): 
# -*- coding: cp1250 -*-
import MySQLdb
db=MySQLdb.connect(user='root', db="test")

c=db.cursor()
c.execute("SET NAMES 'cp1250'")
c.execute("INSERT INTO cs VALUES ('čšřěšžířžčřířž')")
c.execute("SET NAMES 'cp852'")
c.execute("SELECT * FROM cs")

for i in c.fetchall():
  print i[0]

SQL

Jak to udělat aby podmínka LIKE ignorovala velikost písmen?
Buď použijte funkci UPPER() nebo LOWER() — např.: 
SELECT * FROM tabulka WHERE LOWER(sloupec) LIKE LOWER('Řetězec');
nebo nastavte řazení, které velikost písmen ignoruje (tj. končí na _ci), buď pro daný sloupec, nebo jenom pro danou operaci — např.: 
SELECT * FROM tabulka WHERE sloupec LIKE 'Řetězec' COLLATE utf8_czech_ci;
Podmínka LIKE mi ignoruje češtinu! Když zadám "řeka", najde i "reka"!
Nastavte sloupci české řazení, viz tabulka. Máte-li nastavené např. cp1250_general_ci a cp1250_czech_cs nechcete, protože rozlišuje velikost písmen, ponechte sloupci řazení a pouze ho změňte v dané podmínce: 
WHERE sloupec COLLATE cp1250_czech_cs LIKE 'řeka'
Pokud chcete zachovat ignorování velikosti písmen, pak v souladu s předchozí radou se lze dobrat k něčemu jako: 
WHERE UPPER(sloupec COLLATE cp1250_czech_cs) LIKE UPPER('řeka')
INSERT do tabulky mi hlásí nesmyslný "Data too long for column" pro znaky s diakritikou!
Tato chyba se objevuje také v případě, že pošlete MySQL znak, který v daném kódování není platný. To se stane snadno pokud např. PHP skript uložíte v kódování odlišném od toho, které nastavujete pomocí SET NAMES. Příklad: 
mysql_query("SET NAMES 'utf8'") or die('Could not set names');
mysql_query("INSERT INTO tabulka VALUES ('ř')");
Pokud tento skript obsahující takovýto kód uložíme v kódování CP1250 místo UTF-8 ohlásí MySQL tuto chybu, neboť znak ř v CP1250 má kód 0xF8, který netvoří žádný platný znak v kódování UTF-8.
Jak donutit fulltext, aby nerozlišoval velikost písmen?
Nastav sloupci porovnávání, které nerozlišuje velikost písmen.

Ostatní

Do databáze se mi místo českých znaků ukládají HTML entity jako &scaron; nebo &353;!

Jediný způsob, jak dostat do databáze HTML entity je je tam uložit. Jediný způsob, jak dostat do databáze HTML entity je je tam uložit. Jediný způsob, jak dostat do databáze HTML entity je je tam uložit.

Možná máte pocit, že se opakuji, ale někteří lidé tohle nepochopí ani na potřetí :-). Pokud jde o text napsaný v kódu stránky, může jít např. o špatně nakonfigurovaný HTML editor, pokud jde o text získaný z nějakého formuláře může jít např. o to, že daný znak se nenachází v kódování, které má stránka nastavena v metatagu Content-Type nebo HTTP hlavičce nebo není uvedeno vůbec.

Nemám databázi v UTF-8 a když v MySQL Query Browseru zkouším některé příkazy, vrací to chyby typu "Illegal mix of collations".
Někdy může být nutné explicitně převést řetězec z UTF-8, který používá Query Browser do příslušného kódování pomocí funkce CONVERT(řetězec USING kódování), např: 
WHERE UPPER(sloupec COLLATE cp1250_czech_cs) LIKE UPPER(CONVERT('řeka' USING cp1250))
Jak donutit mysql.exe spuštěné ve Windows k češtině?
Textová konzole ve Windows používá kódování CP852, tzn. nejdříve je nutné zadat SET NAMES 'cp852';
Používám MyODBC (např. při přístupu z Delphi přes ADO), jak rozchodím češtinu?
Mělo by stačit poslat správné SET NAMES pro Windows, tedy SET NAMES 'cp1250'. Ideálním místem, kam tento příkaz napsat je ve vlastnostech ODBC spojení na záložce Connect Options políčko Initial Statement, viz obrázek:
Picture of MyODBC connection configuration.
Tutoriál ve Flashi na používání MyODBC s Delphi 7. (cca 1,5 MiB)
Používat SET NAMES nebo SET CHARACTER SET?
Já doporučuji jednoznačně SET NAMES, protože SET CHARACTER SET se spoléhá, že je korektně nastavené kódování pro databázi. A vzhledem k tomu, že většina SQL skriptů distribuovaná s aplikacemi pro vytvoření potřebné struktury tabulek neobsahuje vytvoření databáze, ale požaduje, aby uživatel nejprve databázi vytvořil ručně a až pak nad ní skript spustil (a ALTER DATABASE většinou neobsahuje), je takové spoléhání se ošidné. Nejlepší je prostě nespoléhat se na nic. Nevýhoda SET NAMES je, že nastavuje collation_connection na výchozí hodnotu pro dané kódování (tj. nikoli české), ale tato proměnná má malou prioritu, jakmile je ve výrazu sloupec tabulky (cca 99,9 % případů), použije se porovnávání nastavené pro něj, tudíž je to jedno. A ve zbylém 0,1 % případů lze nastavit toto porovnávání ručně pomocí SET collation_connection = 'porovnávání';