Pracując z danymi tekstowymi, często wymagane jest wsparcie dla różnych języków i alfabetów. W tym celu w SQL używane są zasady sortowania — COLLATION. COLLATION określa, w jaki sposób porównywane i sortowane są ciągi. Ważne jest, aby poprawnie ustawiać COLLATION na poziomie kolumny, tabeli lub zapytania, aby poprawnie działać z cechami językowymi (np. ё ≠ е).
SELECT * FROM users ORDER BY username COLLATE 'ru_RU.UTF8';
To zapytanie sortuje użytkowników według rosyjskiego alfabetu. W różnych SGBD składnia collate może się różnić.
COLLATE utf8mb4_unicode_ci lub utf8mb4_ru_0900_as_cs, aby uwzględnić wielkość liter i język.COLLATE Cyrillic_General_CS_AS — wsparcie dla języka rosyjskiego, różnica wielkości liter (CS = case sensitive, AS = accent sensitive).Ważne: COLLATION wpływa również na wyszukiwanie (LIKE, porównania), nie tylko na sortowanie!
Jakie istnieją problemy przy sortowaniu ciągów z różnym COLLATION w jednym zapytaniu i czy można agregować dane z różnymi kolejnościami sortowania bez jawnych konwersji?
BŁĄD: jeśli COLLATION różni się (na przykład, jedna kolumna ma utf8mb4_unicode_ci, a druga utf8mb4_bin), to przy próbie wykonania UNION lub bezpośredniego porównania, zostanie zgłoszony błąd niekompatybilności COLLATION.
Prawidłowo: zawsze przekształcaj ciągi do jednolitego COLLATION za pomocą konstrukcji COLLATE.
SELECT name COLLATE 'utf8mb4_unicode_ci' FROM customers UNION SELECT name COLLATE 'utf8mb4_unicode_ci' FROM suppliers;
Historia 1
Na dużej platformie e-commerce podczas eksportu do Excel listy klientów w języku rosyjskim zauważono, że użytkownicy z imionami zaczynającymi się na 'Ё' wyświetlają się na końcu listy, a na 'Е' — na początku. Powodem była różnica w COLLATION — użyto standardowego łacińskiego, a nie rosyjskiego, co skutkowało niezgodnością z trwałym porządkiem alfabetycznym. Użytkownicy skarżyli się na nieczytelną sortowanie.
Historia 2
W systemie medycznym różne tabele zawierały pola tekstowe z różnym COLLATION (domyślnym i wyraźnie wskazującym rosyjski). Po zmianie schematu tabeli raporty z agregacji przestały działać, zapytanie zaczęło zgłaszać "konflikt COLLATION". Dział wsparcia musiał w setkach zapytań jawnie określać COLLATE.
Historia 3
API do wyszukiwania według nazwiska działało tylko z dokładnym zapisem wielkich i małych liter (case sensitive), chociaż użytkownicy oczekiwali odporności na wielkość liter. Okazało się, że kolumna została stworzona z COLLATION *_CS, co czyniło wyszukiwanie wrażliwym na wielkość liter. Naprawiliśmy to na *_CI (case insensitive), co rozwiązało problem.