MySQL: Emoji werden abgeschnitten oder gar nicht gespeichert
utf8 ist in MySQL nicht UTF-8, sondern utf8mb3 mit höchstens drei Byte pro Zeichen. Emoji brauchen vier — und fallen deshalb hinten runter.
Aufwand:
ca. 40 Min.
Voraussetzung:
Datenbankzugang mit ALTER-Rechten, Sicherung der Datenbank
Gilt für:
MySQL 8.0
Das Symptom
Ein Nutzer schreibt einen Kommentar mit einem Emoji. Gespeichert wird der Text bis
zum Emoji — der Rest fehlt. Oder das Emoji erscheint als Fragezeichen. Oder die
Anwendung bricht beim Speichern mit einer Beschwerde über einen unzulässigen Wert
für genau diese Spalte ab.
Nachgesehen hat man längst: Die Datenbank steht auf utf8, die Tabelle auf utf8,
die Spalte auf utf8, die Webseite liefert UTF-8 aus, und die Anwendung arbeitet
intern mit UTF-8. Es sieht überall richtig aus und funktioniert trotzdem nicht.
Die „Layer 8“-Wahrheit
Der Fehler steckt im Namen. Was MySQL jahrelang utf8 genannt hat, ist nicht UTF-8,
sondern utf8mb3: eine Fassung, die laut Handbuch ausschließlich Zeichen der Basic
Multilingual Plane unterstützt und höchstens drei Byte je Mehrbyte-Zeichen belegt.
Ergänzende Zeichen — Emoji, viele historische Schriften, ein Teil der
CJK-Erweiterungen — brauchen vier Byte und passen dort schlicht nicht hinein. Wer
utf8 einstellt, hat also nicht Unicode gewählt, sondern ungefähr zwei Drittel
davon.
MySQL selbst hat das inzwischen eingeräumt. utf8mb3 gilt als veraltet und wird in
einer künftigen Hauptversion verschwinden; der empfohlene Zeichensatz ist
utf8mb4, und alle neuen Anwendungen sollen ihn nutzen. Ab MySQL 8.0.30 dreht der
Server sogar die Anzeige um: Statt utf8mb3 wie früher zu utf8 zu verkürzen,
zeigt er in SHOW CREATE TABLE und im Information Schema ausdrücklich utf8mb3
an — damit niemand mehr das eine für das andere hält.
Die Umstellung ist trotzdem kein einzelnes ALTER. Sie betrifft Tabelle, Spalten,
Collations und die Verbindung, und sie stolpert unterwegs über Indexlängen, weil
sich die zulässige Bytezahl nicht ändert, die Bytezahl je Zeichen aber schon: aus
255 indizierbaren Zeichen werden 191. Das ist unangenehm, aber es ist Arbeit, die
einmal anfällt. Die Alternative ist eine Datenbank, die einen Teil der Sprache ihrer
eigenen Nutzer nicht schreiben kann.
Die echte, verifizierte Lösung
1000 % faktenbasiert. Jeder Befehl ist real, jeder Pfad existiert. Schritt für Schritt:
1.
Feststellen, was wirklich eingestellt ist
Nicht raten, sondern nachsehen — und zwar getrennt für Tabelle und Spalten, denn beide können unterschiedliche Zeichensätze haben. Ab MySQL 8.0.30 zeigt der Server in solchen Ausgaben ausdrücklich utf8mb3 an, statt es wie früher zu utf8 zu verkürzen. Wer dort noch utf8 liest, arbeitet mit einer älteren Version und sollte den Wert als utf8mb3 lesen.
SHOW CREATE TABLE beitraege\G
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'beitraege';
2.
Vor dem Umbau die Feld- und Indexlängen prüfen
Der Umbau scheitert sonst mitten im Lauf. Weil ein utf8mb4-Zeichen bis zu vier Byte belegt, sinkt die Zeichenkapazität bei gleicher Bytegrenze. Das Handbuch nennt zwei konkrete Stolpersteine: Indexpräfixe von 255 Zeichen müssen bei COMPACT oder REDUNDANT auf 191 Zeichen verkürzt werden, und TINYTEXT fasst statt 85 Zeichen zu drei Byte nur noch 63 Zeichen zu vier Byte — wer mehr braucht, muss auf TEXT wechseln. Diese Stellen erst suchen, dann umstellen.
3.
Eine Sicherung anlegen
Ein Zeichensatzwechsel schreibt jede betroffene Tabelle neu. Das ist keine Einstellung, die sich zurückschalten lässt, wenn unterwegs Daten verstümmelt wurden — schon gar nicht, wenn die Anwendung bereits falsch kodierte Bytes eingelagert hat. Vor dem ersten ALTER also ein vollständiger Abzug, und der wird auch eingespielt und geprüft, nicht nur erzeugt.
Das Handbuch zeigt den Weg über eine ALTER-TABLE-Anweisung, die den Vorgabe-Zeichensatz der Tabelle setzt und die betroffenen Spalten samt Collation einzeln nachzieht. Der Weg über MODIFY ist der genauere, weil er für jede Spalte sichtbar macht, welche Collation sie danach hat, statt das einer Sammelumstellung zu überlassen.
ALTER TABLE beitraege
DEFAULT CHARACTER SET utf8mb4,
MODIFY titel VARCHAR(255)
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
MODIFY text MEDIUMTEXT
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
5.
Die Verbindung mitziehen
Der letzte und am häufigsten vergessene Schritt. Sendet die Anwendung vier Byte lange Zeichen, muss der Server das für diese Verbindung auch erwarten — sonst kommt am Ende trotz umgestellter Tabelle wieder Datenmüll an. In der Datenbankschicht der Anwendung wird der Zeichensatz der Verbindung gesetzt, entweder über die Verbindungszeichenfolge oder direkt nach dem Verbindungsaufbau. Bei Replikation gilt zusätzlich: Alle Replikate müssen ergänzende Zeichen unterstützen, wenn die Quelle sie verwendet.
SET NAMES 'utf8mb4';
Häufige Fragen (FAQ)
?Ich benutze doch utf8. Warum passt ein Emoji trotzdem nicht hinein?
Weil utf8 in MySQL historisch nicht das vollständige UTF-8 meint, sondern utf8mb3. Das Handbuch beschreibt diesen Zeichensatz so: Er unterstützt ausschließlich Zeichen der Basic Multilingual Plane, also keine ergänzenden Zeichen, und benötigt höchstens drei Byte je Mehrbyte-Zeichen. Emoji liegen außerhalb dieser Ebene und brauchen vier Byte. Das Handbuch empfiehlt deshalb unmissverständlich: Anwendungen, die UTF-8-Daten verwenden, aber ergänzende Zeichen brauchen, sollten utf8mb4 statt utf8mb3 verwenden — der empfohlene Zeichensatz für MySQL ist utf8mb4, und alle neuen Anwendungen sollten ihn nutzen.
?Reicht es, die Tabelle auf utf8mb4 umzustellen?
Nein, es sind mindestens drei Ebenen. Umzustellen sind der Zeichensatz der Tabelle, der Zeichensatz der betroffenen Spalten und die zugehörigen Collations. Dazu kommt die Verbindung: Das Handbuch weist darauf hin, dass Anwendungen, die vier Byte lange Zeichen senden, sicherstellen müssen, dass der Server utf8mb4 versteht — dafür ist SET NAMES utf8mb4 gedacht. Wer nur die Tabelle umstellt, aber weiter über eine utf8mb3-Verbindung schreibt, hat den Fehler nur eine Schicht nach oben verschoben.
?Warum bricht das ALTER TABLE mit einem zu langen Indexschlüssel ab?
Weil sich mit dem Zeichensatz die Bytelänge je Zeichen ändert, die zulässige Bytelänge des Index aber nicht. Das Handbuch rechnet es vor: Bei den InnoDB-Zeilenformaten COMPACT und REDUNDANT liegt die maximale Indexlänge bei 767 Byte, das sind bei utf8mb3 höchstens 255 Zeichen, bei utf8mb4 nur noch 191. Bei COMPRESSED und DYNAMIC sind es 3072 Byte, also 1024 gegenüber 768 Zeichen. Eine Indexdefinition mit col1(255) muss also auf col1(191) verkürzt werden — oder das Zeilenformat muss stimmen.
Belege
Diese Seite ist gegen die folgenden Primärquellen geprüft – Hersteller, Standard oder Gesetzestext. Wer es nicht glaubt, muss es nicht: Da steht, wo es nachzulesen ist.
Der Login wird abgewiesen, obwohl das Passwort stimmt? Bei MySQL zählt auch, von welchem Host aus man kommt — der Benutzer ist an eine Herkunft gebunden.