Extra Ukrainian letters are not sorted well
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