c mysql自动排序_如何在mysql中实现自然排序

熟悉mysql的同学应该清楚，mysql在对字符串做order by排序时是按照字典序进行排序的，但是如果字符串中包含数字的话(我们称这种类型的字符串为alphanumeric)，仅按照字典序的排序结果对用户不太友好。我们举个例子，假设我们在mysql中存了一张files表，里面记录了文件的id以及文件的name，表里的数据如下：

id

name

1

1测试2

2

测试

3

1

4

1测试12

5

1测试1

6

1测试20

name字段目前是乱序的，现在我们对该表执行order by查询，即 SELECT * FROM files ORDER BY name ：

id

name

3

1

5

1测试1

4

1测试12

1

1测试2

6

1测试20

2

测试

我们重点关注以“1测试”开头的那四个name，他们末尾都带有了数字，但因为mysql默认采取字典序排序，所以排序的结果是”1” < “12” < “2” < “20”，这显然不太友好，我们更期望数字部分是根据数字大小排序的。这种非数字部分按照字典序排序，数字部分按照数字大小进行排序的方式我们就称之为 自然排序(natural sort) 。

与字典序排序相比，自然排序的结果更加 人性化 ，对用户更加友好。现代操作系统其实已经实现了文件名的自然排序，我们以Mac为例：

现代的编程语言也都内置了自然排序算法，比如php的 natsort 方法。但是由于mysql中没有内置对应的函数，我们只能通过其他的办法来实现mysql的自然排序。

思路

要想对mysql做一些扩展，一共有以下三种方法：

修改底层源码。

编写mysql扩展(plugin)。

编写存储函数(stored function)。

显然，要想实现自然排序，我们势必要对order by做一些手脚。如果是第一种方法，不仅难度大，而且不利于mysql的版本升级。如果是第二种方法，mysql扩展又不支持扩展语法层面的能力。那么我们只能采用第三种方法了，也就是存储函数或者又称之为UDF。

如果采用存储函数，那么其实我们在排序时还是用的字典序，所以我们需要借助存储函数将原来待排序的字段(比如例子中的name字段)转换成就算按照字典序排序也能达到自然排序效果的字段。

我们再来看看自然排序的核心思想： 非数字部分按照字典序排序，数字部分按照数字大小排序 。所以我们只要将数字部分转换成可按大小排序的字符串即可。我们以上例中的”1测试12“和”1测试2“为例，我们将末尾”12“和”2“转化为定长的字段，比如”0000012“和”0000002“。此时”0000012“按照字典序比”0000002“大，这就实现字典序下的自然排序。

好了说了这么多，show me the code：

DELIMITER ;;

CREATE FUNCTION NatSort (Varstring VARCHAR(50))

RETURNS VARCHAR(1000)

READS SQL DATA

DETERMINISTIC

BEGIN

DECLARE v_length INT DEFAULT 0;

DECLARE v_num VARCHAR(50) DEFAULT '';

DECLARE v_index INT DEFAULT 1;

DECLARE v_result VARCHAR(1000) DEFAULT '';

DECLARE v_flag INT DEFAULT 0;

DECLARE v_char CHAR(1) DEFAULT '';

SET v_flag=0;

SET v_index=1;

SET v_length=CHAR_LENGTH(Varstring);

-- 遍历字符串

WHILE v_index <= v_length DO

SET v_char = mid(Varstring,v_index,1);

IF (ASCII(v_char)>=48 AND ASCII(v_char)<=57) THEN

SET v_num=concat(v_num,mid(Varstring,v_index,1)); -- 获取字符串里的数字

SET v_flag = 1;

ELSE

IF v_flag = 1 THEN

SET v_flag=0;

SET v_result=concat(v_result,lpad(cast(v_num AS UNSIGNED),10,'0')); -- 将数字转成定长字符串

SET v_num=''; -- 重置v_num

END IF;

SET v_result=concat(v_result, v_char);

END IF;

SET v_index = v_index + 1;

END WHILE;

IF v_flag=1 THEN

SET v_result=concat(v_result,lpad(cast(v_num AS UNSIGNED),10,'0'));

END IF;

RETURN v_result;

END;;

在上述代码中，我们将所有数字转成了共10位的定长字符串。我们看一下函数的具体效果，我们执行 SELECT *, NatSort(name) as name_sort FROM files ORDER BY name_sort ：

id

name

name_sort

3

1

0000000001

5

1测试1

0000000001测试0000000001

1

1测试2

0000000001测试0000000002

4

1测试12

0000000001测试0000000012

6

1测试20

0000000001测试0000000020

2

测试

测试

从结果中我们可以看到，已经实现了自然排序，经过实际测试，性能还行。

注意和优化

细心的同学可能已经发现了，上述算法并不完美。我们在代码中将数字扩充为了定长为10位的字符串，那么如果原字符串中的数字长度大于10位，那么算法就失效了。所以在实际使用过程中，要根据具体的业务场景设定定长的位数。

另外，为了提高查询性能，我们可以事先就将转换后的字符串存储在表中，这样就不需要每次查询时都需要调用存储函数。这也是常用的一种以“空间换时间”的优化手段。