Mysql自然排序与特殊字符

Question

Good morning, this is my first topic, so thanks for all outcoming help.

I'm working in a Yii application with a MySQL database. This database has a countries table that has a column called name with a utf8_general_ci collation. This column is filled by data with special characters as Åland Islands

Using the Mysql regular sorting, the column name doesn't have a correct sorting behaviour, for example:

Correct:
Albania
Åland Islands

Actual:
Åland Islands
Albania

Referencing to ASCII table, 'Å' is 197 instead of 'A' is 65.

I tried to use many solutions from the Web (including REGEXP), without success.

Answer 1

In utf8_general_ci, the order of the different sorts of a (a, A, à, ä, etc) is not differenciated : they are all equal in sorting, you can see in the precedent linked page that they are all in white boxes after the letter A.

If you want a sort order corresponding to a specific language, you need to choose the collation corresponding to that language, here may be the utf8_swedish_ci where you can see that the ä and å are at the end of the alphabet.

This is because the sorting order is not the same in different languages, and utf8_general_ci cannot choose one and not the other. For example, in utf8_swedish_ci, Å is before Ä, and in utf8_icelandic_ci, the Å is after the Ä.