让MySQL支持含数字的自然排序
在实际应用中，我们经常需要对数据进行排序，比较常见的是根据ID、时间进行排序。
另外一种场景，就是当排序的字段是字符串，而且字段值中部分为数字时。
此时，我们希望排序后的结果是，数字部分能够按照数字的自然顺序进行排序。
对于这种场景，MySQL默认的排序方式可能会有问题。
假设有一个表test，排序的字段为name，我们希望排序后的结果如下：
接下来看看各种排序方法的测试效果。
由于MySQL默认按照字符进行比较排序，虽然5和9都小于30，但是按照单个字符比较时5和9都大于3，因此排在了30后面。
假设我们的数据是固定前缀，后缀为数字。
先根据字符串的”长度”进行排序，再对字段进行默认排序。
可以看到，这个才是我们希望排序后的结果。
先通过长度的比较，可以将数据分为2组，第一组的数字是1位数，第二组的数字是2位数。
因此，1、5和9肯定会排在前面，而10~30会排在后面。
然后，再分别在每一组的内部进行按字符比较排序，最终就得到了我们想要的结果。
下面再扩展一下。
不支持负数。
先按数字的二进制的长度排序，相同长度的按数字的二进制”字符串”排序，最后再按非数字部分排序。
先按数字的二进制数值进行排序，再按非数字部分排序。
https://www.copterlabs.com/natural-sorting-in-mysql/
题图：pexels.com
二进制之路 发起了一个读者讨论 还有其他更好的方法吗？
此时，我们希望排序后的结果是，数字部分能够按照数字的自然顺序进行排序。
对于这种场景，MySQL默认的排序方式可能会有问题。
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
自然排序
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