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

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

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

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

test1 test5 test9 test10 test11 test12 test13 test21 test30

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

select name from test 
order by name;
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;
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
www.copterlabs.com/natural-sor…
题图：pexels.com
独立博客：binarylife.icu/
996极客：996geek.com/