在我前些日子写的音乐网站上，有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字段的值。

#coding=gbk  
import MySQLdb as mdb
from pinyin import PinYin

dbconn = mdb.Connection(host = '127.0.0.1', user = 'root', passwd = '******', db = 'music', charset='utf8')
cursor = dbconn.cursor(mdb.cursors.DictCursor)
last_id = 0

py = PinYin()
py.load_word()
while True:
    cursor.execute("select * from musician where id>%s limit 1", last_id)
    row = cursor.fetchone()
    if row  is None:
        print 'all musicians have been updated.'
        break
    last_id = row['id']
    m_name = row['name']
    ret = py.all2pinyin(m_name)
    print 'update id', last_id, ''.join(ret).encode('gbk', 'ignore')
    cursor.execute('UPDATE musician_ordered set pinyin = %s where id = %s', (''.join(ret).lower(),last_id))
    dbconn.commit()

dbconn.close()

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

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

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

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

97和122是小写字母a和z