MySQL Sorting – Wrong Order Using utf8_unicode_ci and Swedish Characters

character-setcollationencodingMySQLmysql-5.7

I am confused about what encoding and collation I should use so my queries returns the correct sort order.

Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and lastname.

And the sort order would be something like this:

firstname  lastname
aaa        aaa
ööö        ööö
ooo        ooo
ppp        ppp
qqq        qqq

ööö ööö should be after qqq qqq

Changing collation to utf8_swedish_ci works correctly:

firstname  lastname
aaa        aaa
ooo        ooo
ppp        ppp
qqq        qqq
ööö        ööö

But utf8_unicode_520_ci give me the same result as utf8_unicode_ci, shouldn't it work too?

The funny part is if I only change firstname collation to utf8_unicode_520_ci it seems to work.

Best Answer

Interestingly, someone addressed this apparent difference.

Matthias Bynens answered the post MySQL Collation utf8_unicode differences abour 4 years ago.

He mentioned "Weight Keys"

For more information, go to The Unicode Consortium.

Related Question