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

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.

  • 写回答

1条回答 默认 最新

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题