Character Sets und Collations richtig wählen: utf8mb4, Sorting, Suche

Das utf8-Problem: Warum MySQL's utf8 unvollständig ist

Eines der hartnäckigsten Missverständnisse in der MySQL-Welt: Der Character Set utf8 in MySQL ist kein vollständiges UTF-8. MySQL's utf8 ist eine 3-Byte-Implementierung, die nur Unicode-Zeichen bis U+FFFF unterstützt. Das bedeutet: Emojis, viele chinesische, japanische und koreanische Zeichen (CJK) sowie seltene Schriftzeichen, die 4 Bytes im UTF-8-Encoding benötigen, können mit MySQL's utf8 nicht gespeichert werden. Der Versuch, ein Emoji in ein utf8-Feld zu schreiben, führt entweder zu einem Fehler oder — schlimmer — zum stillen Abschneiden des Wertes.

Das ist kein kleines Edge-Case-Problem. In modernen E-Commerce-Projekten sind Produktbeschreibungen mit Emojis, Kundennamen mit chinesischen Zeichen und Bewertungstexte mit 4-Byte-Zeichen absolut realistisch. Für Magento 2 ist utf8mb4 daher der korrekte und einzige empfohlene Character Set.

MySQL's interner Alias utf8 ist historisch bedingt: Als MySQL UTF-8-Support einführte, wurde nur der damalige UTF-8-Dreibyte-Bereich implementiert. Später wurde utf8mb4 als vollständige 4-Byte-UTF-8-Implementierung hinzugefügt. MySQL hat angekündigt, dass utf8 in zukünftigen Versionen als Alias für utf8mb4 behandelt werden soll — aber so lange das nicht der Fall ist, muss explizit utf8mb4 angegeben werden.

-- Check current character set settings on server and database
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Check character set of a specific Magento table
SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'magento_db'
  AND TABLE_COLLATION NOT LIKE 'utf8mb4%'
ORDER BY TABLE_NAME;

-- Find columns that are NOT utf8mb4
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'magento_db'
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME != 'utf8mb4'
ORDER BY TABLE_NAME, COLUMN_NAME;

utf8mb4: 4-Byte-Support für Emoji und CJK

utf8mb4 ist das vollständige UTF-8-Encoding für MySQL und unterstützt alle 1.112.064 gültigen Unicode-Codepoints, also alle Emojis, CJK-Zeichen und seltene Schriftzeichen. Der Name "mb4" steht für "multibyte 4" — die maximale Byte-Länge pro Zeichen. In der Praxis benötigen die meisten lateinischen Zeichen nach wie vor nur 1–3 Bytes; 4 Bytes werden nur für Zeichen oberhalb von U+FFFF verwendet.

Ein wichtiger Nebeneffekt: Mit utf8mb4 sind VARCHAR-Spalten in MySQL etwas speicherintensiver als mit Latin1, weil MySQL für den Index reservierten Speicher auf Basis der maximalen Byte-Länge des Zeichensatzes berechnet. Ein VARCHAR(100) utf8mb4-Index belegt bis zu 400 Bytes Indexspeicher statt 100 Bytes bei Latin1. Das hat Auswirkungen auf die maximale Schlüssellänge für kombinierte Indizes.

-- Demonstrate utf8 limitation vs utf8mb4
-- Try inserting a 4-byte emoji character
CREATE TABLE test_charset_utf8 (txt VARCHAR(100)) CHARACTER SET utf8;
CREATE TABLE test_charset_utf8mb4 (txt VARCHAR(100)) CHARACTER SET utf8mb4;

-- This will fail or truncate with utf8:
INSERT INTO test_charset_utf8 VALUES ('Hello ????');   -- ERROR or truncation

-- This works perfectly with utf8mb4:
INSERT INTO test_charset_utf8mb4 VALUES ('Hello ????'); -- OK

-- Check actual byte length vs character length
SELECT
  txt,
  CHAR_LENGTH(txt) AS char_length,
  LENGTH(txt) AS byte_length
FROM test_charset_utf8mb4;

Collations im Überblick: unicode_ci vs 0900_ai_ci

Eine Collation definiert, wie Zeichen verglichen und sortiert werden. Bei utf8mb4 stehen mehrere Collations zur Wahl, die unterschiedliche Trade-offs bieten. Die wichtigsten für Magento-Projekte sind:

utf8mb4_unicode_ci: Basiert auf dem Unicode Collation Algorithm (UCA) Version 5.2. Case-insensitive, accent-insensitive. Kompatibel mit älteren MySQL-Versionen, breite Tool-Unterstützung. Das ist die Standard-Collation für Magento 2 und in den meisten Fällen die richtige Wahl für bestehende Projekte.

utf8mb4_unicode_520_ci: UCA 5.2.0, verbesserte Sortierung gegenüber der älteren unicode_ci.

utf8mb4_0900_ai_ci: MySQL 8-only, basiert auf UCA 9.0.0. "ai" steht für accent-insensitive, "ci" für case-insensitive. Schneller als utf8mb4_unicode_ci durch bessere Implementierung. Für neue MySQL-8-Projekte empfehlenswert, aber nicht rückwärtskompatibel mit MariaDB oder MySQL 5.7.

utf8mb4_bin: Binärer Vergleich, case-sensitive. Performant für exakte Byte-Vergleiche, aber für Textsortierung meist ungeeignet.

-- Compare sorting behavior of different collations
SELECT DISTINCT TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'magento_db'
ORDER BY TABLE_COLLATION;

-- Test case-sensitivity difference: ci vs bin
CREATE TABLE collation_test (
  val VARCHAR(50) COLLATE utf8mb4_unicode_ci,
  val_bin VARCHAR(50) COLLATE utf8mb4_bin
);

INSERT INTO collation_test VALUES ('Hallo', 'Hallo'), ('hallo', 'hallo'), ('HALLO', 'HALLO');

-- ci: all three match
SELECT * FROM collation_test WHERE val = 'hallo';      -- returns 3 rows

-- bin: only exact match
SELECT * FROM collation_test WHERE val_bin = 'hallo';  -- returns 1 row

Collation-Effekte auf Sorting und WHERE-Klauseln

Die gewählte Collation beeinflusst nicht nur die Sortierung (ORDER BY), sondern auch, welche Zeilen ein WHERE-Vergleich zurückgibt. Mit einer case-insensitiven Collation wie utf8mb4_unicode_ci matcht WHERE name = 'max' auch Zeilen mit "MAX", "Max" oder "mAx". Das ist für die meisten Such- und Filterfunktionen in Magento gewünscht — ein Kunde der "Nike" sucht, soll auch "NIKE" und "nike" finden.

Gleichzeitig kann dieses Verhalten in manchen Kontexten unerwünscht sein: Wenn du URLs oder Codes speicherst, die case-sensitiv sind, solltest du diese Spalten mit einer _bin- oder _cs-Collation definieren oder auf Anwendungsebene normalisieren.

-- Sorting with utf8mb4_unicode_ci: accent-insensitive by default
-- 'e', 'é', 'è', 'ê' are all treated as equivalent in WHERE
SELECT * FROM catalog_product_entity_varchar
WHERE value LIKE '%café%' COLLATE utf8mb4_unicode_ci;

-- Force case-sensitive search within a ci-collated column
SELECT * FROM customer_entity
WHERE BINARY email = 'User@Example.com';    -- exact case match

-- Compare ORDER BY output with different collations
SELECT value
FROM catalog_product_entity_varchar
WHERE attribute_id = 71  -- name attribute
ORDER BY value COLLATE utf8mb4_unicode_ci
LIMIT 20;

Collation und Index-Nutzung

Ein häufig übersehener Aspekt: Wenn du in einer WHERE-Klausel oder einem JOIN eine andere Collation verwendest als die Tabellen-Collation, kann MySQL den Index nicht nutzen. Das führt zu Full Table Scans statt Index Lookups — ein erheblicher Performance-Einbruch in Magento-Tabellen mit Millionen von Zeilen.

Das passiert beispielsweise, wenn du eine Zeichenkette mit einer Collation vergleichst, die von der Spalten-Collation abweicht. MySQL muss dann jede Zeile einzeln konvertieren, um den Vergleich durchzuführen. Erkenne dieses Problem im EXPLAIN-Output durch "Using where" ohne "Using index" an einem Spaltenvergleich.

-- Problem: collation mismatch causes index skip
-- Table column: VARCHAR(255) COLLATE utf8mb4_unicode_ci  (has index)
-- Literal string with mismatched collation:

-- BAD: forces collation conversion, index not used
SELECT entity_id FROM customer_entity
WHERE email = 'user@example.com' COLLATE utf8mb4_general_ci;

-- GOOD: matches column collation, uses index
SELECT entity_id FROM customer_entity
WHERE email = 'user@example.com';  -- uses column's collation implicitly

-- Detect collation mismatches that cause index skips
EXPLAIN
SELECT entity_id FROM customer_entity
WHERE email = 'user@example.com' COLLATE utf8mb4_0900_ai_ci;
-- Look for "Using where" without "Using index" in Extra column

Deutsche Umlaute korrekt sortieren

Für deutschsprachige Magento-Shops ist die korrekte Sortierung von Umlauten (ä, ö, ü, ß) besonders wichtig. Mit utf8mb4_unicode_ci werden ä, ö, ü als ihre Basis-Buchstaben a, o, u behandelt — für Datenbanksuche funktioniert das gut, für alphabetische Sortierung in Produktlisten aber nicht immer wie erwartet. Die deutsche DIN 5007-Norm sieht vor, dass ä wie ae, ö wie oe und ü wie ue sortiert werden — das ist mit keiner der Standard-MySQL-Collations direkt möglich.

Die pragmatische Lösung: Für die Datenbank-Speicherung und -Suche bleibt utf8mb4_unicode_ci die beste Wahl. Für Anzeige-Sortierungen in der Anwendung (PHP) kann Collator aus der PHP-Intl-Extension genutzt werden, um korrekte deutsche Sortierung auf Anwendungsebene zu erzwingen. Das ist in Magento über Custom Sorting-Logic in ViewModels realisierbar.

-- Demonstrate umlaut sorting in utf8mb4_unicode_ci
SELECT name FROM (
  SELECT 'Ärger' AS name UNION ALL
  SELECT 'Apfel' UNION ALL
  SELECT 'Öl' UNION ALL
  SELECT 'Orange' UNION ALL
  SELECT 'Überraschung' UNION ALL
  SELECT 'Ulm'
) words
ORDER BY name COLLATE utf8mb4_unicode_ci;

-- Result order: Apfel, Ärger, Öl, Orange, Ulm, Überraschung
-- (ä treated as a, ö as o, ü as u in this collation)

-- For truly German DIN 5007 sorting, sort in PHP with Collator:
-- $collator = new Collator('de_DE');
-- $collator->sort($array); -- handles ä=ae, ö=oe, ü=ue correctly

Magento 2: Migration zu utf8mb4

Magento 2 setzt seit Version 2.3.1+ standardmäßig utf8mb4 als Character Set für neue Installationen voraus. Ältere Installationen, die noch mit utf8 erstellt wurden, sollten migriert werden. Die Migration erfolgt mit ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci — aber Achtung: Dieser Befehl sperrt die Tabelle für die Dauer der Konvertierung. Für große Magento-Tabellen (sales_order, catalog_product_entity_varchar) kann das bei direkter Ausführung zu Downtime führen.

-- Step 1: Convert database default charset
ALTER DATABASE magento_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Step 2: Convert individual tables (example for key Magento tables)
ALTER TABLE catalog_product_entity
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE catalog_product_entity_varchar
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE customer_entity
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Step 3: Verify conversion
SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'magento_db'
  AND TABLE_COLLATION NOT LIKE 'utf8mb4%'
LIMIT 20;

-- Step 4: Update Magento env.php connection charset
-- In app/etc/env.php under 'db' > 'connection' > 'default':
-- 'charset' => 'utf8mb4'

Für große Tabellen empfehle ich, die Migration mit pt-online-schema-change oder gh-ost durchzuführen, um Downtime zu vermeiden. Beide Tools erzeugen eine Schattentabelle mit der neuen Collation, kopieren Daten in Batches und führen einen atomaren Rename durch. Nach der Migration muss der Magento-Cache geleert und die Verbindungskonfiguration in env.php auf utf8mb4 aktualisiert werden.

Connection Charset und Anwendungsebene

Auch wenn Tabellen und Spalten korrekt auf utf8mb4 stehen, kann es zu Problemen kommen, wenn die Verbindung zwischen Magento (PHP) und MySQL nicht ebenfalls utf8mb4 verwendet. Die Verbindungs-Collation wird beim Aufbau der Verbindung festgelegt und bestimmt, wie MySQL Zeichenketten interpretiert, die über diese Verbindung ankommen.

In Magento 2 wird der Connection-Charset in app/etc/env.php unter db > connection > default > charset konfiguriert. Stelle sicher, dass hier utf8mb4 eingetragen ist. Zusätzlich sollte der MySQL-Server-Default in my.cnf auf utf8mb4 gesetzt sein, damit neue Verbindungen ohne explizite SET NAMES-Anweisung korrekt arbeiten.

-- Verify connection character set in a MySQL session
SHOW VARIABLES LIKE 'character_set_connection';
SHOW VARIABLES LIKE 'collation_connection';

-- If not utf8mb4, set it explicitly for the current session
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

-- Recommended server defaults in /etc/mysql/mysql.conf.d/mysqld.cnf:
-- [mysqld]
-- character-set-server = utf8mb4
-- collation-server = utf8mb4_unicode_ci
-- [client]
-- default-character-set = utf8mb4

-- Check all connection-level charset settings at once
SELECT * FROM performance_schema.session_variables
WHERE VARIABLE_NAME LIKE 'character_set%'
   OR VARIABLE_NAME LIKE 'collation%';

FAQ: utf8mb4 und Collations