在我前些日子写的音乐网站上,有9571个单独的歌手信息页面。列表位于:
为方便用户筛选歌手,我需要添加过滤器。
可以采用一种比较常见的过滤器,即根据首字母、或者中文的拼音首字母来筛选。
但需要考虑:
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来保存歌手名的拼音。
在网上找了一段代码来转换拼音:
然后自己写了点代码逐行更新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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#coding=gbk
importMySQLdbasmdb
frompinyinimportPinYin
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()
whileTrue:
cursor.execute("select * from musician where id>%s limit 1",last_id)
row=cursor.fetchone()
ifrowisNone:
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