Ärger mit UTF-8 in MySQL…

Kris hatte hier und hier 2006 die Grundlagen von Encodings/Charsets und Collations in MySQL erklärt.

Viele Artikel und Howtos im Netz beschäftigen sich damit, wie man Daten im latin1-Format aus MySQL 4.0 Datenbanken in 4.1 oder 5.x DBs konvertiert und gleichzeitig den Umstieg auf UTF-8 vollzieht.

Was jedoch meist nicht zur Sprache kommt ist die Problematik, dass unter bestimmten Umständen für MySQL bei Verwendung von UTF-8 der String “A” identisch ist mit “a”, bzw. “hatte” mit “hätte”.

Einfach ausgedrückt: Groß-/Kleinschreibung und Sonderzeichen wie deutsche Umlaute.

Wir migrieren z.B. gerade Daten aus pre-MySQL-4.1-Zeiten (latin1) auf MySQL 5.0.54 (utf-8). Der Datenbank-Server läuft komplett auf UTF-8, genauso wie die Entwickler-Maschinen:

SELECT VERSION(); SHOW GLOBAL VARIABLES LIKE ‘character%’; SHOW GLOBAL VARIABLES LIKE ‘collation%’;
+————+
| VERSION()  |
+————+
| 5.0.54-log |
+————+

+————————–+—————————-+
| Variable_name            | Value                      |
+————————–+—————————-+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+————————–+—————————-+

+———————-+—————–+
| Variable_name        | Value           |
+———————-+—————–+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+———————-+—————–+

CREATE DATABASE test2 DEFAULT CHARACTER SET utf8;
USE test2;
CREATE TABLE foo(user VARCHAR(20));
INSERT INTO foo (user) VALUES(‘a’),(‘A’),(‘ä’),(‘Ä’);
SELECT user, LENGTH(user), CHAR_LENGTH(user) FROM foo where user=’a’;
+——+————–+——————-+
| user | LENGTH(user) | CHAR_LENGTH(user) |
+——+————–+——————-+
| a    |            1 |                 1 |
| A    |            1 |                 1 |
| ä    |            2 |                 1 |
| Ä    |            2 |                 1 |
+——+————–+——————-+

Die einzige Möglichkeit, MySQL dieses Verhalten auszutreiben und gleichzeitig bei UTF-8 zu bleiben, ist offenbar VARBINARY anstatt VARCHAR zu verwenden:

ALTER TABLE foo MODIFY user VARBINARY(20);
SELECT user, LENGTH(user), CHAR_LENGTH(user) FROM foo where user=’a’;
+——+————–+——————-+
| user | LENGTH(user) | CHAR_LENGTH(user) |
+——+————–+——————-+
| a    |            1 |                 1 |
+——+————–+——————-+

Allerdings gibt die 20 bei VARBINARY(20) nicht die Anzahl der maximal zulässigen Zeichen an (im Gegensatz zu VARCHAR), sondern die Bytes. Dies hat Zur Folge, dass man bei VARBINARY nun nicht mehr die maximale “Länge” (im Sinne von Zeichen) festlegen kann, weil Strings mit Sonderzeichen mehr Bytes belegen und damit von MySQL abgeschnitten werden:

INSERT INTO foo SET user=’Maximale-Laenge-betraegt-20-Bytes’;
INSERT INTO foo SET user=’öäüÖÄÜ Sonderzeichen mit dabei’;
SELECT user, LENGTH(user), CHAR_LENGTH(user) FROM foo;
+———————-+————–+——————-+
| user                 | LENGTH(user) | CHAR_LENGTH(user) |
+———————-+————–+——————-+
| Maximale-Laenge-betr |           20 |                20 |
| öäüÖÄÜ Sonderz       |           20 |                20 |
+———————-+————–+——————-+

(Wieso sagt hier MySQL beim 2. String CHAR_LENGTH(user) sei 20?)

VARBINARY scheidet also aus.

Muss man sich jetzt seine eigene Kollation bauen oder gibts es andere Möglichkeiten, dass MySQL so arbeitet wie man es eigentlich erwarten kann?

MySQL beschreibt unter Examples of the Effect of Collation genau dieses Problem, gibt aber für UTF-8 keine Lösung.

Wie handhabt Ihr das? Umstieg auf UTF-8 abblasen und weiterhin latin verwenden?

Update: Mit Kollation utf8_bin scheint sich das Problem in Luft aufzulösen. Morgen mal damit experimentieren.