am530

Beim Arbeiten mit MySQL kommt immer wieder die Frage auf, auf welchem Zeichensatz (CHARACTER SET) die Datenbank, Tabelle oder Zelle eingestellt werden soll – und für viele scheint die Angabe zur Sortierfolge (COLLATE) ein noch viel größeres Mysterium darzustellen. Gerade MySQL ist in Sachen Zeichensatz und Sortierfolge wirklich mächtig. MySQL erlaubt das Speichern von Strings in einer Vielzahl von Zeichensätzen, das Vergleichen von Strings unter Verwendung einer Vielzahl von Sortierfolgen. Man kann Zeichensätze und Sortierfolgen beliebig mischen, sei es auf Server-, Datenbank- oder Tabellen-Ebene. Wenn einem das System schon so viele Möglichkeiten liefert, dann sollte man versuchen diese auch effizient zu nutzen.

Was ist ein Zeichensatz genau, und was versteht man unter Sortierfolgen?

Die Dokumentation von MySQL liefert auf diese Fragen ein sehr anschauliches Beispiel. Ein Zeichensatz (CHARACTER SET) ist eine Gesamtmenge von Symbolen bzw. Elementen – das können sichtbare Symbole wie Buchstaben, Zahlen, Sonder- oder Satzzeichen sein, aber auch unsichtbare Steuerzeichen (wie bspw. Zeilenumbruch) – und deren Kodierung (Zuordnung). So hat im deutschen Alphabet der Buchstabe “Ü” die Kodierung 220. Wechselt man den Zeichensatz, so kann die gleiche Kodierung einem ganz anderen Symbol zugeordnet sein. Im EBCDIC-Zeichensatz kodiert der Wert 220 bspw. die geschweifte Klammer “}“. Auch die Anzahl der darstellbaren Zeichen kann von Zeichensatz zu Zeichensatz stark variieren. ASCII kennt 95 darstellbare Zeichen, Unicode (Standard 5.1) hingegen 100.000.
Je komplexer ein Zeichensatz, desto mehr Regeln braucht man für die Sortierung bzw. zum Vergleich dieser Zeichen. Der einfachste Vergleich von zwei Zeichen bzw. Zeichenketten (Strings) ist die Binärsortierung. Dabei wird verglichen, ob die Kodierung von A (65) größer ist als die von B (66). Soweit – so einfach, aber wie fällt der Vergleich von B (66) mit a (97) aus? In diesem Falle würde der Großbuchstabe B vor dem kleinen a erscheinen. Der Zeichensatz braucht also eine weitere Regel, die Groß- und Kleinschreibung nicht berücksichtigt. Zu Erkennen sind diese Sortierfolgen in MySQL an dem _ci in Namen, kurz für case insensitive. Sonderzeichen erzwingen weitere Regeln: Das deutsche “Ü” stehen in der Sortierfolge für UE, und wie sieht es mit é und è aus, welche Regeln gelten in diesem Fall?
Kurz gesagt: COLLATE legt fest, wie Strings miteinander verglichen werden, und das kann ein durchaus komplexes Regelwerk sein.

Welches ist der “richtige” Zeichensatz?

Trival ist diese Frage nicht, betrachtet man folgendes Beispiel: Es soll einer Tabelle USER angelegt werden, die u.a. folgende Informationen aufnehmen soll: Benutzername, Name, Passwort, E-Mail und Land. Bei allen genannten Feldern handelt es sich Textfelder – aber für jedes gelten andere Voraussetzungen:

• Das Passwortfeld sollte verschlüsselt sein und für die Verschlüssung wird häufig MD5 verwendet. Nun liefert MD5 immer eine Zeichenkette von 32 Zeichen, und diese 32 Zeichen sind immer ASCII.
• Die E-Mail hingegen darf seit der Einführung der Internationalen Domain-Namen 92 Sonderzeichen außerhalb des reinen ASCII-Codes enthalten, so sind bspw. deutsche Sonderzeichen im Domänenteil einer E-Mail Adresse durchaus erlaubt. Dies gilt zwar nicht für den eigentlichen Mailverkehr – hier müssen diese Zeichen kodiert werden – aber kaum ein User wird die “kodierte” Fassung einer solchen Adresse bei seiner Anmeldung verwenden. Im lokalen Teil der E-Mail (vor dem @-Zeichen) sind alle Zeichen oberhalb des ASCII-Codes 127 generell verboten (also auch Umlaute), laut Spezifikation müssen aber Groß- und Kleinschreibung unterschieden werden. Bei den Adressen hans.mueller@email.de und Hans.Mueller@email.de könnte es also um zwei verschiedene Adressen handeln (auch wenn dieses in der Praxis kaum der Fall ist). Und die Länge – aus wievielen Zeichen kann eine E-Mail Adresse maximal bestehen? Legt man RFC 5321 zu Grunde, dann darf der lokale Teil aus maximal 64 Zeichen bestehen und der Domänenteil aus 255 Zeichen, kommt noch das @ Zeichen dazu, ergibt sich eine Gesamtlänge von 320 Zeichen. Ein theoretischer Wert, RFC-konforme SMTP-Server können nur mit einer maximalen Länge von 255 Zeichen umgehen, denn die Definitionen des Path-Elementes im RFC 5321 (in dem die E-Mail Adresse steht), erlaubt nur eine entsprechend reduzierte Zeichenlänge.
• Beim Namen, dem “realen Namen” des Nutzers kann – je nachdem wie international die Seite ist – so ziemlich jeder Buchstabe aus jedem Alphabet auftauchen. Ganz gleich ob es ein hebräischer, russischer oder japanischer User ist, die Spalte sollte mit entsprechenden Zeichen umgehen können.
• Das Länderkürzel besteht in vielen Projekten aus zwei oder drei Zeichen, in denen keine Sonderzeichen erlaubt sind, wie bei der Passwort-Spalte sind diese Zeichen immer Bestandteil des ASCII Zeichensatz.
• Für den Benutzernamen gelten zumeist striktere Regeln, als für den realen Namen. Zeichen, die nicht in der Latin-1 (ISO-8859-1) oder US-ASCII Kodierung vorhanden sind, werden selten zugelassen.

Es ist also durchaus möglich, dass innerhalb einer Tabelle Daten gespeichert werden, die in verschiedenen Zeichensätzen repräsentiert sein könnten. In dem Beispiel ließen sich ASCII, Latin-1 und UTF8 unterscheiden, aber welchen Vorteil würden unterschiedliche Zeichensätze überhaupt bieten, oder entstehen daraus am Ende sogar Nachteile?

Im Web setzt sich UTF8 immer mehr durch, seit 2008 ist es der meist verwendete Zeichensatz im World Wide Web. Mit UTF8 lässt sich fast jedes Alphabet abbilden, kein Wunder also, dass mehr und mehr Seiten auf diesen Zeichensatz wechseln. Der Standard Zeichensatz von MySQL ist Latin-1, gerade mal 256 darstellbare Zeichen (zu denen noch nicht einmal das € Euro Zeichen gehört), Das UTF8 Kodierungsschema, das mit einer variablen Länge von 1-4 Bytes pro Zeichen arbeitet repräsentiert einen ungleich größeren Zeichenraum. Ist es nicht die effizienteste Lösung, dann alle Zeichenfelder, die gesamte Datenbank Kommunikation auf UTF8 umzustellen?

Die UTF8 Kodierung hat ihren Preis

UTF8 kodierte Zeichen aus dem ASCII Raum benötigen 1 Byte an Speicher, für Sprachen wie Hebräisch oder Türkisch, aber auch deutsche Sonderzeichen belegt es 2 Bytes und Japanisch oder Chinesisch beanspruchen 3 Bytes (MySQL unterstützt zur Zeit nur bis zu 3 Byte-Sequenzen). Latin-1 oder ASCII benötigt immer nur 1 Byte. Definiert man bspw. das Passwortfeld wie folgt:

PASSWORD CHAR(32) CHARSET utf8

Ergibt sich folgendes: Eine CHAR Spalte ist eine fixierte Spalte, d.h. es wird immer die gleiche Länge verwendet, sollten weniger Zeichen eingegeben werden, dann füllt MySQL diese fehlenden Zeichen mit einem Leerzeichen auf. MySQL muß in diesem Fall also gewährleisten, dass bis zu 32 Zeichen in diese Spalte passen, bei maximal 3 Bytes pro Zeichen belegt die Spalte damit immer 32*3 (96) Bytes, wäre die Spalte in ASCII kodiert hingegen nur 32 Bytes, bei vielen Datensätzen und vielen solcher Spalten kann sich das im Speicherbedarf durchaus bemerkbar machen. Halb so wild – wechselt man den Typ von CHAR auf VARCHAR, dann wechselt man von einem fixierten Spaltentyp auf einen dynamischen Spaltentyp. VARCHAR braucht immer nur soviel Bytes wie tatsächlich in der Zelle notwendig sind (plus 1 bis 2 Byte für die Anzahl der Zeichen). In diesem Fall sind alles ASCII Zeichen, damit also 32 Byte + 2 Byte für die Länge, also 34 gegen 96 Byte für jeden Eintrag.

Einfach auf CHAR verzichten und ganz auf VARCHAR wechseln?

Ist es nicht sogar so, dass es MySQL mit CHAR und VARCHAR gar nicht so genau nimmt, den Spaltentyp zum Teil selbstständig wechselt?
Hab ich auch so gelernt, aber es stimmt so nicht mehr: Früher galt, dass VARCHAR Spalten, die mit weniger als 4 Zeichen definiert werden, automatisch in ein CHAR konvertiert wurden. Umgedreht galt für CHAR-Spalten mit einer Definition von mehr als 3 Zeichen (unter der Voraussetzung, dass die Tabelle eine dynamsiche Spalte enthielt) die Wandlung in den Typ VARCHAR. Seit der MySQL Version 5 ergibt:

CREATE TABLE testVARCHAR_CHAR (
  col1 CHAR(40),
  col2 VARCHAR(2),
  col3 CHAR(3)
);
DESC testVARCHAR_CHAR;

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1  | char(40)   | YES  |     | NULL    |       |
| col2  | varchar(2) | YES  |     | NULL    |       |
| col3  | char(3)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

Also keine implizite Typenkonvertierung mehr, dieses Argument entfällt. Aber warum überhaupt fixierte Spalten, wenn dynamische Spalten doch effizienter mit dem Speicherplatz umgehen? Der Unterschied ergibt sich über die Geschwindigkeit. Bei PHPPerformance findet man einen Artikel zum Thema und die dort genannten Zahlen sprechen für sich. Aktualisierungen oder Löschvorgänge können bei dynamischen Tabellen zur Fragmentierung der Information führen, und dies hat eine Auswirkung auf die Performance – bei dem genanntem Test lag der Abfrageunterschied zwischen der dynamsichen und statischen Variante bei 0,6 zu 0,1 Sekunden.

Um bei dem hier gewählten Beispiel zu bleiben, die Passwortspalte ist immer gleich lang, sie besteht immer aus 32 Zeichen, der Typ CHAR ist hier demzufolge die optimale Lösung. Die Zeichen sind immer ASCII – also ist der ideale Zeichensatz für diese Spalte ASCII und nicht UTF8, man gewinnt an Performance und spart zudem noch 1 bis 2 Byte pro Eintrag. Bei der Spalte für das Länderkürzel lässt sich ähnlich argumentieren.
Für die Felder E-Mail, Name und Benutzername hingegen sollte UTF8 verwendet werden. Auch wenn beim Benutzernamen ggf. ausschließlich Zeichen aus dem Latin-1 Zeichenraum verwendet werden, würde hier eine unterschiedliche Kodierung spätestens bei der Ausgabe auf einer Seite Probleme bereiten. Da in allen Zellen dieser Spalten sehr variable Werte stehen können, werden alle als VARCHAR definiert:

CREATE TABLE user (
  password CHAR(32) CHARSET ascii,
  username VARCHAR(20) CHARSET utf8,
  name VARCHAR(50) CHARSET utf8,
  email VARCHAR(255) CHARSET utf8,
  lang CHAR(3) CHARSET ascii,
  UNIQUE KEY (username)
);

Der UNIQUE KEY auf das Feld “username” erzwingt einen eindeuten Benutzernamen (für den Fall, dass dieser Wert für den Nutzer nicht veränderbar ist, könnte man die Spalte “username” auch zum PRIMARY KEY machen.). Aber wie eindeutig ist dieser Name in dieser Form eigentlich? Macht es einen Unterschied ob ich der Nutzer “axel”, “Axel” oder “AXEL” bin?

Warum man auf die Sortierfolge achten sollte

Bei einem Benutzernamen kann man sicher darüber streiten, ob es sinnvoll ist, Groß- und Kleinschreibung zu unterscheiden – also jede Schreibvariante als einen eigenen Namen zu verstehen. In anderen Fällen, bspw. für den Fall, dass Sie mit unverschlüsselten Passwörtern arbeiten (müssen), sollte die Schreibweise unbedingt eine Rolle spielen. Anstatt (und das ist die Standard Einstellung) Groß- und Kleinschreibung nicht zu berücksichtigen, sollte in diesen Fällen die COLLATE Eigenschaft so verändert werden, dass unterschieden wird:

[...]
  username VARCHAR(20) CHARSET utf8 COLLATE utf8_bin,
[...]

COLLATE Angaben, die auf bin oder cs enden, sind case sensitive – berücksichtigen Groß- und Kleinschreibung. Mit dieser Veränderung liessen sich die Nutzer axel”, “Axel” oder “AXEL” in die Tabelle eintragen, anderenfalls gäbe es einen Fehler: #1062 - Duplicate entry 'Axel' for key 'username'.
Die Spalte “name” hatte den Zeichensatz UTF8 erhalten, die Standard-Sortierfolge ist damit utf8_general_ci, für viele Vergleiche korrekt – aber nicht für alle. Ein Beispiel: Angezeigt werden sollen alle deutsche Nutzer, in alphabetischer Reihenfolge:

SELECT name FROM user WHERE lang='DE' ORDER BY name;

+-------------+
| name        |
+-------------+
| Aachen      |
| Aechten     |
| Affe        |
| Ärger       |
| Äußern      |
| Axel Michel |
+-------------+

Die Sortierung entspricht nicht den deutschen Sortierungsregeln, Ein Ä steht für AE, und nicht für A, wie es bei UTF8 der Fall ist. Auch hier kann einem die Sortierfolge weiterhelfen:

SELECT name FROM user
  WHERE lang='DE'
  ORDER BY
   CAST(name AS CHAR CHARACTER SET latin1)
   COLLATE latin1_german2_ci;

+-------------+
| name        |
+-------------+
| Aachen      |
| Aechten     |
| Ärger       |
| Äußern      |
| Affe        |
| Axel Michel |
+-------------+

Nun ist das Ergebnis richtig (im Sinne des deutschen Alphabets) sortiert. Damit der Vergleich richtig funktioniert, wird der Zeichensatz für die Sortierung auf latin1 gewechselt, und dann die entsprechende Sortierfolge gewählt.

Zeichensatz und Sortierfolge sind keine starren, “unveränderbaren” Eigenschaften einer Datenbank. Sie sind im hohen Maße abhängig von den Inhalten und Typ der jeweiligen Tabellenspalten und – wie im letzten Beispiel aufgezeigt – von dem, was man “mit den Daten anfangen will”.