Answer

When working with textual data, it is often necessary to support various languages and alphabets. For this, SQL uses collation rules — COLLATION. COLLATION defines how strings are compared and sorted. It is important to set COLLATION correctly at the column, table, or query level to correctly handle language-specific features (for example, ё ≠ е).

Example:

SELECT * FROM users
ORDER BY username COLLATE 'ru_RU.UTF8';

This query sorts users according to the Russian alphabet. The syntax for collate may vary across different DBMSs.

• For MySQL: COLLATE utf8mb4_unicode_ci or utf8mb4_ru_0900_as_cs for case and language sensitivity.
• For MSSQL: COLLATE Cyrillic_General_CS_AS — support for the Russian language, case sensitivity (CS = case sensitive, AS = accent sensitive).

Note: COLLATION affects search operations (LIKE, comparisons), not just sorting!

Trick Question

What problems arise when sorting strings with different COLLATIONs in a single query, and can data with different collation orders be aggregated without explicit conversions?

ERROR: if COLLATIONs are different (for example, one column has utf8mb4_unicode_ci and another has utf8mb4_bin), attempting to perform a UNION or directly compare them will throw a collation incompatibility error.

Correct: always convert strings to a single COLLATION using the COLLATE clause.

SELECT name COLLATE 'utf8mb4_unicode_ci' FROM customers
UNION
SELECT name COLLATE 'utf8mb4_unicode_ci' FROM suppliers;

Examples of real errors due to ignorance of the topic

---

Story 1

In a large e-commerce platform, when exporting a list of customers in Russian to Excel, it was noticed that users with names starting with 'Ё' appeared at the very end of the list, while those starting with 'Е' were at the beginning. The reason was the difference in COLLATION — a standard Latin collation was used instead of the Russian one, and the sorting did not match the familiar alphabetical order. Users complained about the non-obvious sorting.

---

Story 2

In a medical system, different tables contained string fields with different COLLATIONs (default and explicitly specified Russian). After the table schema was changed, aggregated reports stopped working, and the query began to return "COLLATION conflict". Technical support had to explicitly specify COLLATE in hundreds of queries.

---

Story 3

An API for searching by last name only worked with the exact case of letters (case sensitive), while users expected case insensitivity. It turned out that the column was created with a *_CS COLLATION, making the search case sensitive. It was corrected to *_CI (case insensitive), which solved the problem.