LOFTER-网易轻博

MySQL实现微信好友排序效果

<ol>
<li>大小写英文字母排序排在前面</li>
<li>中文按拼音排序</li>
<li>数字,符号以及emoji符号</li>
</ol>

大致实现思路：

1. 获取中英文首字母，按照首字母进行排序

2. 获取首字母是英文的（首字母是[a-z]的）优先排序，其他排序其次

1. 创建自定义函数，实现获取中文首字母

CREATE  FUNCTION `FirstPinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8

BEGIN

DECLARE V_RETURN VARCHAR(255);

SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),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');

    RETURN V_RETURN;

END

SQL中使用 IFNULL(FirstPinyin(t1.realname),substring(t1.realname, 1, 1)) 可获取所有用户的首字母

2. 判断首字母为英文的，并优先排序（使用 IFNULL(FirstPinyin(t1.realname),substring(t1.realname, 1, 1)) REGEXP '[a-z]' 即可验证首字母是否为字母）

3. 使用 firstPY 和 isZimu 进行排序，即可实现微信好友的排序功能

ORDER BY IFNULL(FirstPinyin(t1.realname),substring(t1.realname, 1, 1)) REGEXP '[a-z]' DESC,IFNULL(FirstPinyin(t1.realname),substring(t1.realname, 1, 1)) ASC