MySQL中的带字母数字排序

在MySQL中，我们常常需要对包含字母和数字的数据进行排序。比如，在一个包含订单号的表格中，订单号可能为字母和数字的组合，如“ORD001”，“ORD002”，“ORD003”，“ORD01A”，“ORD01B”， “ORD02A”，“ORD02B”，“ORD100”。如何实现按照这种含有数字和字母的顺序排序，使得输出结果满足我们的需求呢？

阅读更多：MySQL 教程

MySQL的默认排序规则

MySQL的默认排序规则是按照字典顺序进行排序。字典顺序是指如果两个字符串从左到右逐个字符比较，第一个不同的字符的ascii码较小的那个字符串排在前面。比如，字符串“a”排在“b”前面，“0”排在“1”前面。

举个例子：

SELECT column1 FROM table1 ORDER BY column1;

上面这个语句会按照column1这个列的内容，按照字典顺序进行排序。如果column1这个列的内容全是数字，那么按照数字大小排序就不是问题。但是，如果column1这个列的内容含有字母，那么就会出现问题。

比如，有如下数据：

进行默认排序后，输出结果如下：

可以看到，这个排序结果和我们想要的不一样。因为MySQL会先比较字母，再比较数字。所以先输出了“10”，再输出“2”，“a”，“b”。

使用函数实现按照字母数字排序

为了实现按照字母数字排序，我们可以使用MySQL提供的一个函数。该函数是一个自定义的函数，需要借助第三方库实现。这个函数叫做“natural sorting”。它能够按照包含字母和数字的字符串的顺序进行排序。

我们在这里使用的是php的一个库叫做“natsort”。

CREATE FUNCTION `natural_sort`(s1 varchar(200), s2 varchar(200))
RETURNS int
NO SQL
BEGIN
    DECLARE s1_num varchar(200);
    DECLARE s2_num varchar(200);
    DECLARE s1_str varchar(200);
    DECLARE s2_str varchar(200);
    DECLARE s1_ptr int;
    DECLARE s2_ptr int;

    SET s1_ptr=1;
    SET s2_ptr=1;

    WHILE s1_ptr<=length(s1) AND s2_ptr<=length(s2) DO
        SET s1_num="";
        SET s2_num="";

        WHILE s1_ptr<=length(s1) AND substring(s1, s1_ptr, 1) >= '0' AND substring(s1, s1_ptr, 1) <= '9' DO
            SET s1_num=concat(s1_num, substring(s1, s1_ptr, 1));
            SET s1_ptr=s1_ptr+1;
        END WHILE;

        WHILE s2_ptr<=length(s2) AND substring(s2, s2_ptr, 1) >= '0' AND substring(s2, s2_ptr, 1) <= '9' DO
            SET s2_num=concat(s2_num, substring(s2, s2_ptr, 1));
            SET s2_ptr=s2_ptr+1;
        END WHILE;

        IF length(s1_num)>0 AND length(s2_num)>0 THEN
            SET s1_num=cast(s1_num as unsigned);
            SET s2_num=cast(s2_num as unsigned);

            IF s1_num<s2_num THEN
                RETURN -1;
            ELSEIF s1_num>s2_num THEN
                RETURN 1;
            ELSE
                SET s1_ptr=s1_ptr+1;
                SET s2_ptr=s2_ptr+1;         
            END IF;
        ELSEIF length(s1_num)>0 THEN
            RETURN -1;
        ELSEIF length(s2_num)>0 THEN
            RETURN 1;
        ELSE
            SET s1_str=substring(s1, s1_ptr, 1);
            SET s2_str=substring(s2, s2_ptr, 1);

            IF s1_str<s2_str THEN
                RETURN -1;
            ELSEIF s1_str>s2_str THEN
                RETURN 1;
            ELSE
                SET s1_ptr=s1_ptr+1;
                SET s2_ptr=s2_ptr+1;         
            END IF;
        END IF;
    END WHILE;

    IF s1_ptr<=length(s1) THEN
        RETURN 1;
    ELSEIF s2_ptr<=length(s2) THEN
        RETURN -1;
    ELSE
        RETURN 0;
    END IF;
END

创建完成之后，我们可以对上面的例子进行排序：

SELECT column1 FROM table1 ORDER BY natural_sort(column1);

这个语句会输出以下结果：

可以看到，这次按照我们的预期进行了排序。

结论

MySQL默认的排序规则只是按照字典顺序进行排序，如果需要按照字母数字的顺序进行排序，则需要使用自定义函数来实现。在实现自定义函数时，我们可以利用php的“natsort”库，通过判断字符串中的数字和字母来实现按照字母数字顺序排序的目的。