Unicode, UTF-8 und Kollation in MySQL
David Maus, 2018-07-20
Zeichenkodierung
Die Zeichenkodierung utf8 kann nur Unicode-Zeichen verarbeiten die sich in maximal 3 Byte UTF-8
kodieren lassen. Dadurch erschöpft sich der verwendbare Zeichenvorrat auf die Zeichen der Basic Multilingual
Plane (BMP).
Die Zeichenkodierung utf8mb4 unterstützt auch Zeichen der höheren Unicode-Ebenen, allerdings um
den Preis verkürzter Indexschlüssel.
Die Maximallänge dieser Schlüssel beträgt unabhängig vom Datentyp im Fall der InnoDB-Engine 767 Byte. Die
Zeichenkodierung utf8 verwendet maximal 3 Byte pro Zeichen, so dass ein Indexschlüssel aus 767 ÷ 3 =
255 Zeichen bestehen kann; die Zeichenkodierung utf8mb4 verwendet maximal 4 Byte pro Zeichen, wodurch
sich eine maximale Länge eines Indexschlüssel von 767 ÷ 4 = 191 Zeichen ergibt.
Für die MyISAM-Engine mit einer Schlüssellänge von 1000 Byte gelten die Einschränkungen analog.
Wenn ein Index über ein TEXT, VARCHAR oder CHAR-Feld benötigt wird, der
mehr als 255 bzw. 191 Zeichen umfasst, dann ist der Einsatz eines Einweg-Prüfsummenalgorithmus mit geringer
Kollisionswahrscheinlichkeit wie MD5 oder SHA1 als Option zu prüfen.
Kollation
Die Kollation legt fest, wie Zeichen miteinander verglichen werden. Sie gilt nicht nur für Suchanfragen, sondern auch für die Berechnung von Indexschlüsseln. Praktisch bedeutet das, dass bei Verwendung einer Kollation, die z.B. Umlaut und Grundbuchstaben gleich behandelt, für die Werte 'zahlen' und 'zählen' identische Indexschlüssel berechnet werden.
Gleichbehandlung von Umlaut und Grundbuchstabe
mysql> create database datenbank;
mysql> use datenbank;
mysql> create table tabelle ( text varchar(100) collate utf8_unicode_ci );
mysql> insert into tabelle (text) values ('zahlen'), ('zählen');
mysql> select count(*) from tabelle where text = 'zahlen';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.01 sec)
mysql> create unique index idx_text on tabelle (text);
ERROR 1062 (23000): Duplicate entry 'zahlen' for key 'idx_text'
Gleiches gilt für die Groß- und Kleinschreibung.
...und von Groß- und Kleinschreibung
mysql> delete from tabelle;
mysql> insert into tabelle (text) values ('zahlen'), ('Zahlen');
mysql> create unique index idx_text on tabelle (text) ;
ERROR 1062 (23000): Duplicate entry 'zahlen' for key 'idx_text'
Wenn ein UNIQUE Index verwendet werden soll aber Datenbankbenutzer auch komfortabel suchen können
sollen, dann gibt es mindestens zwei Möglichkeiten: Konversion zur Abfragezeit oder Verwendung einer gesonderten
Spalte.
Konversion zur Abfragezeit
Kollation zu Abfragezeit konvertieren
mysql> alter table tabelle change text text varchar(100) collate utf8_bin ;
mysql> select count(*) from tabelle where 'zahlen' = text;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from tabelle where 'zahlen' = text collate utf8_unicode_ci;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
Verwendung einer gesonderten Spalte
Eine Spalte text mit der Kollation utf8_bin wird für den UNIQUE-Index verwendet,
eine Spalte text_suche mit der Kollation utf8_german2_ci für die Suche.
Andere Kollation in gesonderter Spalte
mysql> drop table tabelle;
mysql> create table tabelle ( text varchar(100) collate utf8_bin; text_suche varchar(100) collate utf8_german2_ci );
mysql> insert into tabelle (text, text_suche) values ('zahlen', 'zahlen'), ('zählen', 'zählen');
mysql> select count(*) from tabelle where 'zahlen' = text;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from tabelle where 'zahlen' = text_suche;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
Es ist abhängig vom Anwendungsbereich, welche der Möglichkeiten sinnvoll ist. Die Konversion zur Abfragezeit spart Speicherplatz auf Kosten der Rechenleistung, die Verwendung einer gesonderten Spalte spart Rechenleistung auf Kosten von Speicherplatz.
Umwandlung von Altdaten
Bei der Umwandlung von Altdaten sind in jedem Fall die verschiedenen Einstellungsmöglichkeiten der Zeichenkodierung zu beachten Schwartz, Zaitsev und Tkachenko 2012, S. 298ff. In der Regel werden die Zeichen "on the fly" bei einem Datenbankzugriff in die verbindungsspezifische Kodierung gewandelt. Dadurch ist nicht ohne weiteres möglich, festzustellen, wie ein Zeichen tatsächlich in der Datenbank gespeichert ist. Um das festzustellen kann der Text noch während der Abfrage in eine hexadezimale Darstellung gewandelt werden.
Feststellen, wie ein Text datenbankintern codiert ist
mysql> select hex(text),text from tabelle;
+----------------+--------+
| hex(text) | text |
+----------------+--------+
| 7A61686C656E | zahlen |
| 7AC3A4686C656E | zählen |
+----------------+--------+
2 rows in set (0.00 sec)
Literatur
Schwartz, Baron, Peter Zaitsev, und Vadim Tkachenko. High performance MySQL: Optimization, backups, and replication. Beijing et al.: O’Reilly, 2012.