Encoding in MariaDB/MySQL: Tipps zur Vermeidung von Encoding-Albträumen

Dieser Artikel soll den Nebel um das oft vermiedene Thema Encoding lichten. Meistens hofft man das Beste und kommt damit durch, aber wenn Encoding-Probleme auftauchen, dann oft zu einem ungünstigen Zeitpunkt und dann kostet es auch noch viel Zeit sie zu lösen. Zumindest, wenn man nicht weiß, womit man es zu tun hat. Die gute Nachricht ist, dass Sie Encoding-Problemen vorbeugen können, und ich möchte Sie dazu ermutigen, dies zu tun.

Dieser Blog-Beitrag orientiert sich lose an und ist eine Art Kurzfassung von http://mysql.rjweb.org/doc.php/charcoll.

Ich werde alles, was man unbedingt wissen muss, im Voraus abdecken und dann einige Beispielszenarien im Detail durchspielen. Am Ende gibt es eine kurze Zusammenfassung über bewährte Problemlösungstechniken.

Encoding – Was ist das?

Computer kommunizieren über Bits. Ein Bit ist die kleinste Informationseinheit und ist entweder 0 oder 1. Unter Encoding versteht man die Übersetzung von Bits, genauer gesagt einer Folge von Bits, in ein (Schrift-)Zeichen und umgekehrt. Beispielsweise kann die Bitfolge 01000001 in UTF-8 in "A" übersetzt werden. Beachten Sie jedoch, dass die Bitfolge für ein "A" in einer anderen Kodierung möglicherweise anders aussieht.

Nützliche Wissens-Nuggets

• Bit vs. Byte: Wir haben gerade von Bits gehört. Eine Folge von 8 Bits (z.B. das UTF-8 "A" von oben: 01000001) ist ein Byte. Ein Byte kann 256 verschiedene Zustände darstellen, denn 2^8 = 256.
• Character set vs. Collation: Wenn Sie eine Datenbank oder eine Tabelle in MariaDB/MySQL erstellen, können Sie einen Zeichensatz (Character set) und eine Sortierung (Collation) festlegen. Der Zeichensatz teilt der Datenbank grundsätzlich mit, welches Encoding beim Einfügen von Daten (in Form von Bits) verwendet werden soll. Bei der Collation hingegen handelt es sich um eine Reihe von Regeln, die bestimmen, wie die verschiedenen Zeichen verglichen werden. Beispielsweise entscheidet die Sortierung, ob "a" und "A" gleich sind oder ob "oe" und "ö" gleich sind. Das ist praktisch zu wissen, wenn wir die Daten sortieren oder filtern, einen group by durchführen usw.
• Mojibake: Das japanische Wort beschreibt den Buchstabensalat, der durch die Verwendung des falschen Encodings entstehen und Unsinn oder unlesbaren Text erzeugen kann.
• Hexadezimal: Wenn wir über Encoding sprechen, kommen wir nicht um Hexadezimalzahlen herum. Hexadezimalzahlen werden als eine Art Abkürzung für Bit-/Byte-Sequenzen verwendet. Die Tabelle zeigt die Funktionsweise des Hexadezimalsystems. Das Hex für das Beispiel des UTF-8 "A" wäre 0100 0001 -> 41.

  Hexadecimal	Bits	Decimal
  0	0000	0
  1	0001	1
  2	0010	2
  3	0011	3
  4	0100	4
  5	0101	5
  6	0110	6
  7	0111	7
  8	1000	8
  9	1001	9
  A	1010	10
  B	1011	11
  C	1100	12
  D	1101	13
  E	1110	14
  F	1111	15

• Unicode vs. UTF-8: Der Unicode-Standard bietet eine Map von Bezeichnern für mehr oder weniger alle Schriftzeichen, die es gibt. Es weist jedem Zeichen einen Bezeichner zu, zum Beispiel lautet der Bezeichner für "A": U+0041. Wie diese Bezeichner in Bytes gespeichert werden, ist Aufgabe des Encodings. UTF-8 ist eines dieser Encodings.

Wo spielt Encoding überhaupt eine Rolle?

Das wird Ihnen nicht gefallen. Die Verschlüsselung spielt im wahrsten Sinne des Wortes überall eine Rolle. Die Datenbank verfügt über ein Encoding, ebenso wie jede Tabelle und jede einzelne Spalte darin. Und sie könnten alle unterschiedlich sein. Der Server, auf dem die Datenbank lebt, sowie der Client, der eine Verbindung zur Datenbank herstellt, der Computer, auf dem der Client ausgeführt wird, und die Verbindung zur Datenbank selbst verfügen alle über Encoding-Einstellungen. Geben Sie hier nicht auf, es gibt ein paar wichtige Punkte, auf die Sie achten müssen. Beachten Sie bitte, dass ich im Folgenden davon ausgehe, dass alle Tabellen und Spalten in einer Datenbank die gleiche Encoding-Einstellung (Zeichensatz und Sortierung) haben – was ich auch dringend empfehle! Kommen wir nun zurück zu der Encoding-Einstellung, auf die Sie achten sollten: Client/Daten, Verbindung und Datenbank/Tabelle.

Client/Daten

Wenn Sie die Daten von Hand in Ihren Client einfügen und an die Datenbank senden, verwenden die Daten die Kodierung des Clients. Wenn PyCharm beispielsweise auf UTF-8 eingestellt ist und ich ein "A" eingebe, um es an die Datenbank zu senden, wird es der Verbindung als 01000001 übergeben. Wenn Sie den Client jedoch verwenden, um die Daten von einem anderen Ort abzurufen, beispielsweise aus dem Internet, einer Datei oder einer App, verfügen diese möglicherweise über eine andere Kodierung als die Einstellung in Ihrem Client.

Verbindung

Wenn Sie mithilfe eines Clients Daten aus einer Datenbank senden oder abfragen, stellen Sie zunächst eine Verbindung zur Datenbank her. In diesem Zusammenhang gibt es immer ein Encoding. Es handelt sich im Grunde nur um die Informationen, die die Kodierung der Daten enthält, die ich an die Datenbank sende. Wenn Sie dies nicht manuell setzen, dann wird einfach ein Encoding angenommen. Das sollte vermieden werden. Stellen Sie dazu eine Verbindung zur Datenbank her und verwenden Sie die Abfrage "SET NAMES UTF-8;", wenn Sie die Verbindung auf UTF-8 einstellen möchten. Führen Sie anschließend die INSERT- oder Query-Anweisungen aus. Warum ist das so wichtig? Ich komme gleich darauf zurück.

Datenbank/Tabelle

Wie Sie sich erinnern, kann jede Datenbank, jede Tabelle und jede Spalte ihre eigenen Einstellungen in Bezug auf Encoding und Sortierung haben. Stellen Sie sicher, dass Sie auch dies manuell setzen und lassen Sie nicht zu, dass die Standardeinstellungen alles durcheinander bringen. Auch dazu später mehr.

Wie bestimmt man die Kodierung einer Datei oder eines Textes?

Leider kann man die Kodierung eines Textes nicht mit absoluter Sicherheit bestimmen. Je nachdem, womit Sie arbeiten, gibt es Bibliotheken und Befehle, um die Kodierung einer Datei oder eines Textes zu schätzen. Das sind zwar nur begründete Vermutungen, aber das ist besser als nichts und reicht in der Regel aus.

In Python gibt es die Funktion detect aus der chardet-Bibliothek, die die wahrscheinlichste Kodierung Ihrer Datei ermittelt. Unter Linux können Sie das auch ohne Paket auf der Kommandozeile machen, indem Sie zum Beispiel file -i <Name>.txt eingeben.

Encoding Workflow - wo die Puzzleteile zusammenkommen

Ich hoffe, dass ich bis hierhin alle Voraussetzungen behandelt habe, die wichtig sind, um zu verstehen, was hier vor sich geht. An dieser Stelle werde ich nun einige Szenarien im Detail durchspielen. Zuerst schauen wir uns ein ganz normales Szenario "Plain Vanilla" an.

Plain Vanilla - alles UTF-8

Zuerst erstellen Sie eine Datenbankverbindung in Ihrem Client, dann setzen Sie die Kodierung innerhalb der Verbindung auf UTF-8 (erinnern Sie sich an die "SET NAMES"-Abfrage). Wenn Sie nun "Hello" eingeben und mit einer Insert-Anweisung an die Datenbank senden, werden die Zeichen dieser Zeichenkette entsprechend ihrer Kodierung in Bits umgewandelt und an die Verbindung übergeben. Die Bits für "Hello" sind 01001000 01100101 01101100 01101111, was sich kurz als Hexadezimalwert 48 65 6C 6C 6F schreiben lässt. Innerhalb der Verbindung werden diese Bits in die Schriftzeichen gemäß "SET NAMES" umgewandelt, in diesem Fall UTF-8. Das Ergebnis ist hier natürlich "Hello". Diese Zeichen werden an die Datenbank geliefert, die sie wiederum in Bits zurückübersetzt und speichert. Wenn wir darauf eine Select-Abfrage abschicken, erhalten wir das Wort "Hello" zurück. So weit so gut.

Nun, lasst die Spiele beginnen. Damit das Ganze überhaupt Spaß macht, müssen wir zu einem Wort wechseln, das tatsächlich das Potenzial hat, Mojibake zu produzieren. Ich würde gerne mit "Beyoncé" arbeiten, wie in http://mysql.rjweb.org/doc.php/charcoll. Das Binary für "Beyoncé" ist 01000010 01100101 01111001 01101111 01101110 01100011 11000011 10101001 und der Hexadezimalwert ist 42 65 79 6F 6E 63 C3 A9. Das Zeichen, das Probleme machen könnte (und wird), ist das "é", ein 2-Byte-Zeichen in UTF-8 (C3 A9).

Mojibake #1:

Daten: UTF-8, Verbindung: latin-1, Datenbank: latin-1

In diesem Fall nehmen wir an, dass wir uns überhaupt nicht um die Kodierung gekümmert haben und eine Datenbank/Tabelle ohne Kodierungsangabe erstellt haben, so dass sie mit der Standardkodierung latin-1 erstellt wurde. Auch gebe ich in meinem Client über "SET NAMES" nicht an, welche Kodierung zu verwenden ist. Also wird auch hier latin-1 angenommen. Mein Client (d.h. PyCharm) ist auf UTF-8 Kodierung eingestellt. Was passiert nun?

Wie zuvor werden die Zeichen "Beyoncé" in Bits übersetzt und an die Verbindung übergeben. Innerhalb der Verbindung werden die Bits nun mit der falschen Kodierung übersetzt, nämlich Latin-1. Latin-1 ist eine reine 1-Byte-Kodierung und erkennt daher das C3 A9 als zwei Zeichen, die in Latin-1 à und © sind. So werden die Zeichen "Beyoncé" an die Datenbank gesendet und erscheinen als solche, wenn wir darauf einen SELECT absetzen.

Mojibake #2:

Daten: UTF-8, Verbindung: latin-1, Datenbank: UTF-8

In dieser Situation ist unser Client immer noch auf UTF-8 eingestellt und unsere Datenbank/Tabelle ist ebenfalls auf UTF-8-Kodierung eingestellt. Nur in der Verbindung geben wir entweder die falsche Kodierung an oder es wird die falsche Kodierung angenommen, da sie nicht angegeben wurde, nämlich Latin-1.

Wie zuvor werden die Zeichen "Beyoncé" in Bits übersetzt und an die Verbindung übergeben. Innerhalb der Verbindung werden die Bits in Latin-1-Zeichen übersetzt, was dazu führt, dass "Beyoncé" an die Datenbank geliefert wird. Die Datenbank denkt nun in UTF-8 und übersetzt diese Latin-1 Zeichen in die entsprechenden UTF-8-Bits. à wird in C3 83 und © in C2 A9 übersetzt.
Damit speichert die Datenbank 42 65 79 6F 6E 63 C3 83 C2 A9, zwei Bytes mehr als unser Original.

No Mojibake:

Daten: latin-1, Verbindung: latin-1, Datenbank: UTF-8

Ich möchte nun ein Beispiel durchspielen, bei dem alles richtig gemacht wird. Stellen Sie sich vor, ich habe irgendwo aus dem Web Daten bekommen - die überraschenderweise wieder aus dem Wort "Beyoncé" bestehen - und die Kodierung wird (korrekt) als Latin-1 angenommen. Der Hexadezimalwert von Latin-1 für "Beyoncé" ist 42 65 79 6f 6e 63 e9. Die korrekte Latin-1-Übersetzung für é ist also e9 (1110 1001). Wir sagen der Verbindung via "SET NAMES latin1;", dass die Daten, die ich sende, in Latin-1 sein werden. Über die Leitung wird 42 65 79 6f 6e 63 e9 korrekt in "Beyoncé" zurückübersetzt und an die Datenbank übergeben. Die Datenbank denkt UTF-8 und übersetzt die Zeichen zum Speichern in UTF-8-Bytes, also 42 65 79 6F 6E 63 C3 A9. Wenn wir darauf einen SELECT absetzen, dann erhalten wir "Beyoncé". Wunderschön.

Best Practice

• Machen Sie Gebrauch von SET NAMES.
• Stellen Sie die Kodierung Ihrer Datenbank/Tabelle manuell ein und versuchen Sie nicht verschiedene Kodierungen zu nutzen.
• Verwenden Sie überall UTF-8. In MariaDB und MySQL bedeutet die Verwendung von UTF-8 eigentlich UTF-8mb4, was ich im nächsten Abschnitt erklären werde. UTF-8 wurde entwickelt, um die Kodierungsprobleme zu lösen, in das uns das Internet gebracht hat. Wenn Sie etwas mehr über UTF-8 erfahren möchten, kann ich das folgende Video empfehlen: Characters, Symbols and the Unicode Miracle by Computerphile

UTF-8 in MariaDB / MySQL - Achtung!

Bitte beachten Sie, dass UTF-8 in MariaDB und MySQL kein valides UTF-8 ist. In MySQL/MariaDB ist die Option "UTF-8" eine 3-Byte-Kodierung. UTF-8 ist aber per Definition eine 4-Byte-Kodierung. Sie werden also keine Probleme haben, wenn Sie "UTF-8" verwenden, solange Sie sicher sind, dass Sie keine 4-Byte-Zeichen benötigen. Wenn Sie versuchen, 4-Byte-Zeichen in eine Tabelle einzufügen, die "UTF-8"-kodiert ist, erhalten Sie eine wenig aussagekräftige Fehlermeldung. Um eine Tabelle/Datenbank in MariaDB/MySQL zu haben, die wirklich UTF-8 kodiert ist, verwenden Sie die Option utf-8mb4.

Problemlösungstechniken

Wenn Sie in Ihrer Datenbank auf Mojibake stoßen, prüfen Sie, ob die Best Practices angewendet wurden. Versuchen Sie, die Kodierung Ihrer Daten neu zu bewerten, d. h. Sie können entweder die Kodierungseinstellungen Ihres Clients überprüfen (sind die Einstellungen in Ihrem Editor korrekt?) oder eine andere Kodierung ausprobieren, wenn Sie das Gefühl haben, dass es nicht die richtige ist. Sehr hilfreich ist auch ein Blick in die Datenbank, um zu sehen, wie die von Ihnen eingegebenen Daten gespeichert wurden.

SELECT x, HEX(x), LENGTH(x), CHAR_LENGTH(x) FROM ... WHERE ... LIMIT 1

LENGTH(X) bezieht sich auf die Anzahl der Bytes, die benötigt werden um X zu speichern. CHAR_LENGTH(X) ist die Anzahl der Zeichen in X.

Beispiel: In UTF-8 würde ich erwarten, dass X eine LENGTH von 8 Bytes hat, wenn X = Beyoncé. Würde ich statt 8 Bytes 10 Bytes erhalten, würde dies beispielsweise auf den Mojibake-Fall #2 hindeuten. Das bedeutet, dass in der Verbindung die Kodierung nicht richtig eingestellt wurde. Dieser Fall wird als doppelte Kodierung bezeichnet.

Es kann auch hilfreich sein, einen Blick darauf zu werfen, welcher Zeichensatz und welche Sortierung für eine Tabelle und ihre Spalten tatsächlich angegeben sind. Verwenden Sie dazu die Anweisung SHOW CREATE TABLE.

In jedem Fall muss "SET NAMES" mit der tatsächlichen Kodierung der Daten übereinstimmen.

Fazit

Ganz gleich, ob Sie ein kleines privates Projekt oder eine große Datenbank für Ihr Unternehmen verwalten, ein solides Verständnis der Funktionsweise der Kodierung wird Ihnen helfen, Kodierungsprobleme zu vermeiden und sie effizienter zu lösen, wenn sie auftreten. Ich hoffe, dieser Artikel war hilfreich, um ein tieferes Verständnis von Kodierung in MariaDB und MySQL zu erlangen. Unter https://mysql.rjweb.org/doc.php/charcoll von Rick James finden Sie weitere Details und einen tieferen Einblick in dieses Thema.