mysql根据中文拼音分组_【改进篇】使用MySql实现好友昵称按拼音排序或分组

昵称排序顺序要求

1. 大小写英文字母排序排在前面
2. 中文按拼音排序
3. 数字,符号以及emoji符号

后来发现原来的思路是有问题的，参与拼音排序的就只有中文，英文是没有参与排序的，原来的效果:

昵称

Apple

Boy

阿布

宝宝

要实现的效果是：

昵称

Apple

阿布

Boy

宝宝

发现问题以后，我换了一个思路：

不再使用原来过于复杂的做法--利用gbk编码自带的拼音来排序：

这种方法对于大小写字母没有作用，不符合前面提到的需求；

自行将要排序的字符，转换成对应的字母：

对于需要排在后面的字符转换为 小写字母 z (小写字母z比大写的ascii码要大)，

例如常用标点符号, emoji 表情符号都会转换成 z，然后使用升序方式排序。

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

效果截图:

dd.png

SQL语句用到两个重要的自定义函数

转换中文字的首字母拼音 自定义函数

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 ;

转换每个中文字对应的拼音 自定义函数

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 ;

3.进一步排序:

前面两点基本上可以实现了拼音排序,但因为将要 非中英文字符 排在后面而将这些字符归类到 小写字母 z 这一组，所以对于 会跟首字母拼音为z或者首个英文单词为z 混在一起。

bb.png

需要进一步排序去划分。使用权重规则实现，对应不同的分组(这里主要指是那些 非中英字符)文字符 的被分到的z 组)分配不同的权重。

权重分配:

-- 进一步排序权重

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

完整的SQL语句:

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

效果图:

cc.png

就写到这里,如果发现问题，请作出指点。