mysql附近好友排序

mysql附近好友排序

昵称排序顺序要求

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

实现思路 :

1.按照昵称首字符的unicode 值进行排序。(解决第一点需求)

根据unicode编码对照表, 大小写英文字母的范围为 (十进制) 65 ~ 122，而数字以及常用标点符号则在 (十进制)65以下。

所以需要处理的就只有65以下的编码符号,将这些编码的符号提升比中文要高的位置即可(这里只是考虑到昵称只含有中文，而不包含韩文，日文其他语言的字符)。

2.MySql拼音排序方法

在MySql 里是通过gbk字符进行拼音排序的。

所以需要将排序的字段nickname 转换成gbk进行排序。

order by convert(nickname using gbk) asc 方法。

mysql 通常储存格式为utf8 ，如果有emoji 标签符号数据，则使用utf8mb4格式。

(注:utf8最多只支持3个字节,即3*8位,而emoji 长度最长会超过3个字节,utf8mb4就是utf8 的超集, 扩展了utf8的可变长度 )

3.查询gbk 拼音为z的最后一个汉字

根据gbk编码范围B0A1-F7FE,创建一张用于存储所有gbk编码汉字的表。编写以下存储过程实现上述操作:

set @gbkStart = conv(hex(0xB0A1),16,10);

set @gbkEnd = conv(hex(0xF7FE),16,10);

set @i = @gbkStart;

drop table if exists t_gbk;

create table t_gbk(

charcode varchar(200),

charword varchar(10)

) charset gbk;

-- 插入gbk汉字

while @i<=@gbkEnd do

set @charcode = conv(@i,10,16);

set @charword = convert(unhex(@charcode) using gbk);

set @i = @i+1;

insert into t_gbk(charcode,charword) values(@charcode,@charword);

end while;

查找最后一个拼音为Z的汉字: "糳" gbk编码为 BC64

(注意这个gbk字符集所认可的 bc64 为"糳")

select * from t_gbk order by charword desc

111.png

SQL实现:

-- 拼音z排序最后的汉字:糳 utf8编码为 xe7xb3xb3(即0xe7b3b3),gbk编码为BC64

-- 0x41 即十进制65 大写字母A

select nickname,hex(left(nickname,1)) as raw_charcode,

convert(

unhex(

case

when hex(left(nickname,1))

when

left(nickname,1)>=0xf09f9880 -- 0xf09f9880 表示 emoji 表情😀，utf8编码为 xf0x9fx98x80 (即0xf09f9880)

then hex(0xe7b3b3) --将emoji表情转换对应的汉字 "糳"

ELSE hex(left(nickname,1))

end

)

using utf8)

as HanZi

from user

order by

convert(HanZi using gbk) asc, -- 这一步已经能把emoji表情符号，非中英文的符号排序到最后,

raw_charcode asc -- 由于emoji 是在原有汉字库基础上再出的，所以它的编码值肯定比基础编码范围要大，这样就可以把emoji排在最后

** 注:0x41 即十进制65 大写字母A **

参考:

由于这里只考虑昵称只含有中文,所以只需要将65以下的编码符号提升大于最后中文的编码(即9FBF)即可。由于9FBF为unicode, 并且mysql 存储的是utf8,所以需要把9FA5转换成utf8编码。最简单的转换方式：在上面链接里找到9FA5对应的中文字,黏贴到文本里，然后以utf8格式保存，最后以16进制查看该文件的内容。

也可以在python里将最后一个中文字转换为utf8:

s = u"u9FBF";