Unicode, UTF-8 und Kollation in MySQL

David Maus, 20.07.2018 · Twitter · Comment · Permalink

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.

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:

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

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.

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.

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.

David Maus M. A.