less than 1 minute read

The problem is that Ukrainian letters are not sorted correctly by the alphabet, ie “і” and “є” are preceded by “а”, “б”, while using the ORDER BY operator in MySQL.

The possible reason is that a mistaken collation value has been used. To correctly sort the Ukrainian letters, the value must be utf8_unicode_ci.

Note! It’s worth pointing out that utf8_unicode_ci is more accurate than utf8_general_ci for Bulgarian, Belarusian, Macedonian, Russian, Serbian and Ukrainian languages. While utf8_general_ci is fine only for Russian and Bulgarian subset of Cyrillic.

We can specify the collation directly in a query

SELECT *
FROM <table_name>
ORDER BY <column_name>
COLLATE utf8_unicode_ci;

or change it permanently.

1) Сhange column collation:

ALTER TABLE <table_name>
MODIFY <column_name> VARCHAR(255)
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

2) Change table collation:

ALTER TABLE <table_name>
CONVERT TO
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

3) Change database collation:

ALTER DATABASE <db_name>
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

Update

You have to use utf8mb4 rather than utf8. Confusingly, utf8 is a flawed UTF-8 implementation from early MySQL versions which remains only for backward compatibility. The fixed version was given the name utf8mb4. Read more >

Leave a comment