doudang4568
doudang4568
2016-05-13 21:41
浏览 60
已采纳

Mysql自然排序与特殊字符

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.

图片转代码服务由CSDN问答提供 功能建议

早上好,这是我的第一个主题,感谢所有得到的帮助。

我正在使用MySQL数据库的Yii应用程序。 此数据库有一个国家/地区表,其中包含名为name的列,其中包含utf8_general_ci排序规则。 此列由具有特殊字符的数据填充为Åland群岛

使用Mysql常规排序,列名称没有正确的排序行为,例如:

正确:
阿尔巴尼亚
âland群岛

实际: \ n
ÅlandIslands
Albania

参考ASCII表,'Å'是197而不是'A'是65。 \ n

我尝试使用Web上的许多解决方案(包括REGEXP),但没有成功。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douyue9704
    douyue9704 2016-05-13 22:53
    已采纳

    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 Ä.

    点赞 评论

相关推荐