Bij het werken met tekstgegevens is het vaak nodig om ondersteuning voor verschillende talen en alfabetten te bieden. Hiervoor worden in SQL sorteervoorkeuren gebruikt — COLLATION. COLLATION bepaalt hoe rijen worden vergeleken en gesorteerd. Het is belangrijk om COLLATION correct in te stellen op kolom-, tabel- of query-niveau, zodat rekening wordt gehouden met taalspecifieke kenmerken (bijvoorbeeld, ё ≠ е).
SELECT * FROM users ORDER BY username COLLATE 'ru_RU.UTF8';
Deze query sorteert gebruikers op basis van het Russische alfabet. De syntaxis van collate kan verschillen tussen verschillende databasesystemen.
COLLATE utf8mb4_unicode_ci of utf8mb4_ru_0900_as_cs voor het rekening houden met hoofdlettergebruik en taal.COLLATE Cyrillic_General_CS_AS — ondersteuning voor de Russische taal, hoofdlettergevoeligheid (CS = case sensitive, AS = accent sensitive).Belangrijk: COLLATION beïnvloedt ook de zoekopdracht (LIKE, vergelijkingen), niet alleen de sortering!
Wat zijn de problemen bij het sorteren van rijen met verschillende COLLATION in één query, en is het mogelijk om gegevens met verschillende sorteervolgordes samen te voegen zonder expliciete conversies?
FOUT: als COLLATION verschilt (bijvoorbeeld, bij de ene kolom — utf8mb4_unicode_ci, bij de andere — utf8mb4_bin), dan zal er een fout van COLLATION-incompatibiliteit worden gegenereerd bij het proberen om UNION uit te voeren of ze rechtstreeks te vergelijken.
Correct: zorg ervoor dat je rijen naar een uniforme COLLATION converteert via de constructie COLLATE.
SELECT name COLLATE 'utf8mb4_unicode_ci' FROM customers UNION SELECT name COLLATE 'utf8mb4_unicode_ci' FROM suppliers;
Verhaal 1
Bij een groot e-commerce platform werd bij het exporteren naar Excel van klantenlijsten in het Russisch opgemerkt dat gebruikers met namen die beginnen met 'Ё', aan het einde van de lijst stonden, terwijl die met 'Е' aan het begin stonden. De oorzaak was het verschil in COLLATION — er werd een standaard Latijnse gebruikt in plaats van de Russische, en de sortering kwam niet overeen met de gebruikelijke alfabetische volgorde. Gebruikers klaagden over de onduidelijke sortering.
Verhaal 2
In een medisch systeem hadden verschillende tabellen tekstvelden met verschillende COLLATION (zowel standaard als expliciet met Russische instelling). Na het wijzigen van het tabellenchema stopten de geaggregeerde rapporten met werken, en de query gaf "COLLATION conflict". De technische ondersteuning moest in honderden queries expliciet COLLATE opgeven.
Verhaal 3
De API voor het zoeken op achternaam werkte alleen met exacte hoofdletters en kleine letters (case sensitive), terwijl gebruikers verwachtte dat het ongevoelig zou zijn voor hoofdletters. Het bleek dat de kolom was aangemaakt met COLLATION *_CS, wat de zoekopdracht hoofdlettergevoelig maakte. We hebben het gewijzigd naar *_CI (case insensitive), wat het probleem oploste.