MySQL中英混合首字母排序归类

在我前些日子写的音乐网站上,有9571个单独的歌手信息页面。列表位于:

http://www.fachun.net/musician/

为方便用户筛选歌手,我需要添加过滤器。

可以采用一种比较常见的过滤器,即根据首字母、或者中文的拼音首字母来筛选。

但需要考虑:

1. 中文的拼音如何获取

2. 其他语言的字符如何处理

 

在我的数据库中,这个表叫musician_ordered,character set统一为utf8,collation统一为utf8_general_ci。

为了让数据自身根据name字段预排序,我选择了(name, id)这个复合主键。

但还有个问题,数据是以utf8_general_ci进行的整理,而不是根据gbk排序,

英文和中文可能会混排在一起。

 

最简单的排序方法

最简单的排序方法只需要一条 SQL语句:

select * from musician_ordered order by convert(name using gbk) collate gbk_chinese_ci;

首先,对name字段进行gbk编码,然后,对编码后的内容根据gbk_chinese_ci进行整理排序。

这样得到的结果,英文是排在中文前面的,而且是根据拼音排序的。

 

使用Python脚本转换拼音

我选择了添加额外的一列pinyin来保存歌手名的拼音。

在网上找了一段代码来转换拼音:

https://github.com/wklken/pinyin.py/tree/4f73a7ba297455f30594eeffab6224acd4e9dadb

然后自己写了点代码逐行更新pinyin字段的值。

这样pinyin字段就被填充好了歌手名的拼音。

在做过滤器的时候,直接查询pinyin字段的首字母就可以了。

而过滤器中那个“其他”的链接,是非字母或中文开头的歌手,使用类似这样的查询:

select id,name from musician_ordered where ascii(left(pinyin,1)) not between 97 and 122

97和122是小写字母a和z

One Response

  1. chinawinxp与编程
    chinawinxp与编程 2016 年 1 月 19 日 at 11:21 | | Reply

    很好的例子!@!!

Leave a Reply