Beim Arbeiten mit Textdaten ist oft die Unterstützung verschiedener Sprachen und Alphabete erforderlich. Dazu werden in SQL Sortierungsregeln — COLLATION — verwendet. COLLATION bestimmt, wie Strings verglichen und sortiert werden. Es ist wichtig, die COLLATION korrekt auf Spalten-, Tabellen- oder Abfrageebene festzulegen, um sprachliche Besonderheiten korrekt zu berücksichtigen (z.B. ё ≠ е).
SELECT * FROM users ORDER BY username COLLATE 'ru_RU.UTF8';
Diese Abfrage sortiert Benutzer nach dem russischen Alphabet. In verschiedenen DBMS kann die Syntax für COLLATE variieren.
COLLATE utf8mb4_unicode_ci oder utf8mb4_ru_0900_as_cs zur Berücksichtigung von Groß-/Kleinschreibung und Sprache.COLLATE Cyrillic_General_CS_AS — Unterstützung der russischen Sprache, Unterscheidung der Groß-/Kleinschreibung (CS = case sensitive, AS = accent sensitive).Wichtig: COLLATION wirkt sich auch auf die Suche (LIKE, Vergleiche) aus, nicht nur auf die Sortierung!
Welche Probleme können bei der Sortierung von Strings mit unterschiedlicher COLLATION in einer Abfrage auftreten, und ist es möglich, Daten mit verschiedenen Sortierungsregeln ohne explizite Umwandlungen zu aggregieren?
FEHLER: Wenn die COLLATION unterschiedlich ist (z.B. eine Spalte — utf8mb4_unicode_ci, die andere — utf8mb4_bin), wird beim Versuch, UNION zu verwenden oder sie direkt zu vergleichen, ein Fehler aufgrund von COLLATION-Inkompatibilität ausgelöst.
Richtig: Führen Sie immer die Strings zu einer einheitlichen COLLATION durch die Konstruktion COLLATE zusammen.
SELECT name COLLATE 'utf8mb4_unicode_ci' FROM customers UNION SELECT name COLLATE 'utf8mb4_unicode_ci' FROM suppliers;
Geschichte 1
Auf einer großen E-Commerce-Plattform stellte man beim Export einer Excel-Liste von Kunden auf Russisch fest, dass Benutzer mit Namen, die mit 'Ё' beginnen, am Ende der Liste angezeigt werden, während die mit 'Е' am Anfang stehen. Der Grund war eine unterschiedliche COLLATION — es wurde der standardmäßige lateinische und nicht der russische verwendet, und die Sortierung entsprach nicht der gewohnten alphabetischen Reihenfolge. Benutzer beklagten sich über die unklare Sortierung.
Geschichte 2
In einem medizinischen System enthielten verschiedene Tabellen Zeichenfolgenfelder mit unterschiedlicher COLLATION (standardmäßig und explizit auf Russisch angegeben). Nach einer Schemaänderung funktionierten die aggregierten Berichte nicht mehr; die Abfrage gab einen "COLLATION conflict" zurück. Der technischen Unterstützung war es notwendig, in Hunderten von Abfragen das COLLATE explizit anzugeben.
Geschichte 3
Die API zur Suche nach Nachnamen funktionierte nur mit exakter Schreibweise von Groß- und Kleinbuchstaben (case sensitive), obwohl die Benutzer eine Unempfindlichkeit gegen Groß-/Kleinschreibung erwarteten. Es stellte sich heraus, dass die Spalte mit COLLATION *_CS erstellt wurde, was die Suche case sensitive machte. Wir haben es auf *_CI (case insensitive) geändert, was das Problem gelöst hat.