【改进篇】使用MySql实现好友昵称按拼音排序或分组
昵称排序顺序要求
<ol>
<li>大小写英文字母排序排在前面</li>
<li>中文按拼音排序</li>
<li>数字,符号以及emoji符号</li>
</ol>
这是我原来的实现思路:
http://www.jianshu.com/p/29243714a9d3
后来发现原来的思路是有问题的，参与拼音排序的就只有中文，英文是没有参与排序的，原来的效果:
昵称
Apple
Boy
阿布
宝宝
要实现的效果是：
昵称
Apple
阿布
Boy
宝宝
发现问题以后，我换了一个思路：
-
不再使用原来过于复杂的做法--利用gbk编码自带的拼音来排序：
这种方法对于大小写字母没有作用，不符合前面提到的需求； -
自行将要排序的字符，转换成对应的字母：
对于需要排在后面的字符转换为 小写字母 z (小写字母z比大写的ascii码要大)，
例如常用标点符号, emoji 表情符号都会转换成 z，然后使用升序方式排序。
<pre>
select NICKNAME,
-- 按照拼音排序
case
when left(nickname,1)<0x41 then 'z'
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then left(nickname,1)
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 'z'
when
left(nickname,1)>=0xf09f9880
then 'z'
ELSE fristPinyin(nickname)
end
as PinYin
from user
order by PinYin asc,raw_charcode desc
</pre>
效果截图:
dd.png
SQL语句用到两个重要的自定义函数
转换中文字的首字母拼音 自定义函数
<pre>
delimiter $$
CREATE FUNCTION fristPinyin(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
delimiter ;
</pre>
转换每个中文字对应的拼音 自定义函数
<pre>
delimiter $$
CREATE FUNCTION pinyin(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE V_COMPARE VARCHAR(255);
DECLARE V_RETURN VARCHAR(255);
DECLARE I INT;
SET I = 1;
SET V_RETURN = '';
while I < LENGTH(P_NAME) do
SET V_COMPARE = SUBSTR(P_NAME, I, 1);
IF (V_COMPARE != '') THEN
#SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);
SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
#SET V_RETURN = fristPinyin(V_COMPARE);
END IF;
SET I = I + 1;
end while;
IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
SET V_RETURN = P_NAME;
END IF;
RETURN V_RETURN;
END
delimiter ;
</pre>
3.进一步排序:
前面两点基本上可以实现了拼音排序,但因为将要 非中英文字符 排在后面而将这些字符归类到 小写字母 z 这一组，所以对于 会跟首字母拼音为z或者首个英文单词为z 混在一起。
bb.png
需要进一步排序去划分。使用权重规则实现，对应不同的分组(这里主要指是那些 非中英字符)文字符 的被分到的z 组)分配不同的权重。
权重分配:
<pre>
-- 进一步排序权重
case
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then 13 -- 字母 第一位
when left(nickname,1)>=0xf09f9880 then 11 -- 表情 第三位
when left(nickname,1)<0x41 then 10
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 10
ELSE 12 -- 其他(主要是中文第二)
end
as weight
</pre>
完整的SQL语句:
<pre>
select nickname,
-- 按照拼音排序
case
when left(nickname,1)<0x41 then 'z'
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then left(nickname,1)
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 'z'
when
left(nickname,1)>=0xf09f9880
then 'z'
ELSE fristPinyin(nickname)
end
as PinYin,
-- 进一步排序权重
case
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then 13 -- 字母 第一位
when left(nickname,1)>=0xf09f9880 then 11 -- 表情 第三位
when left(nickname,1)<0x41 then 10
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 10
ELSE 12 -- 其他(主要是中文第二)
end
as weight
from user
order by PinYin asc,weight desc
</pre>
效果图:
cc.png
就写到这里,如果发现问题，请作出指点。
