mysql 拼音首字母排序

在处理使用MySQL时，数据表采用utf8字符集，使用中发现中文不能直接按照拼音排序sql

若是数据表tbl的某字段name的字符编码是latin1_swedish_ci编码

select * from `tbl` order by birary(name) asc ;spa

若是数据表tbl的某字段name的字符编码是utf8_general_ci.net

SELECT name FROM `tbl` WHERE 1 ORDER BY CONVERT( name USING gbk ) COLLATE gbk_chinese_ci ASCcode

===================排序

$orderby="convert(`group` USING gbk) COLLATE gbk_chinese_ci,displayorder,navid"utf-8

获取utf-8表的字段首字母ci

SELECT *,
 ELT(INTERVAL(CONV(HEX(LEFT(CONVERT( seriesname USING gbk ) COLLATE gbk_chinese_ci,1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') AS PY
 FROM bookseries;