让MySQL支持含数字的自然排序

在实际应用中,我们经常需要对数据进行排序,比较常见的是根据ID、时间进行排序。

另外一种场景,就是当排序的字段是字符串,而且字段值中部分为数字时。
此时,我们希望排序后的结果是,数字部分能够按照数字的自然顺序进行排序。
对于这种场景,MySQL默认的排序方式可能会有问题。

假设有一个表test,排序的字段为name,我们希望排序后的结果如下:

test
test1
test5
test9
test10
test11
test12
test13
test21
test30

接下来看看各种排序方法的测试效果。

默认排序

select name from test 
order by name;

结果:
test
test1
test10
test11
test12
test13
test21
test30
test5
test9

由于MySQL默认按照字符进行比较排序,虽然5和9都小于30,但是按照单个字符比较时5和9都大于3,因此排在了30后面。

自然排序

假设我们的数据是固定前缀,后缀为数字。

先根据字符串的”长度”进行排序,再对字段进行默认排序。

select name from test 
order by length(name),name;

结果:
test
test1
test5
test9
test10
test11
test12
test13
test21
test30

可以看到,这个才是我们希望排序后的结果。
先通过长度的比较,可以将数据分为2组,第一组的数字是1位数,第二组的数字是2位数。
因此,1、5和9肯定会排在前面,而10~30会排在后面。
然后,再分别在每一组的内部进行按字符比较排序,最终就得到了我们想要的结果。

下面再扩展一下。

支持对字符串中的负数进行排序

固定前缀

select name4
from test
order by cast(substr(name4,5) as signed);

结果:
code-3
code-2
code0
code1
code2
code3
code4
code8
code10
code12
code15

固定后缀

select name5 from test
order by cast(substring(name5,1,length(name5)-4) as signed);

结果:
-3code
-2code
0code
1code
2code
3code
4code
8code
10code
12code
15code

数字前缀,后缀不固定

不支持负数。

先按数字的二进制的长度排序,相同长度的按数字的二进制”字符串”排序,最后再按非数字部分排序。

SELECT name6, BIN(name6) AS prefix_number
FROM test
ORDER BY length(prefix_number), prefix_number, name6;

先按数字的二进制数值进行排序,再按非数字部分排序。

SELECT name6, cast(BIN(name6) as signed) prefix_number
FROM test
ORDER BY prefix_number, name6;
结果:
0abc    0
0code   0
1abc    1
1code   1
2code   10
3code   11
4code   100
8code   1000
10code  1010
12code  1100
15code  1111
22code  10110
33c     100001

参考

How to Implement Natural Sorting in MySQL


---转载本站文章请注明作者和出处 二进制之路(binarylife.icu),请勿用于任何商业用途---

留下评论