Unicode, UTF-8 und Kollation in MySQL
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.
Gleiches gilt für die Groß- und Kleinschreibung.
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
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.
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.
Literatur
Schwartz, Baron, Peter Zaitsev, und Vadim Tkachenko. High performance MySQL: Optimization, backups, and replication. Beijing et al.: O’Reilly, 2012.