MySQL 中的字母数字/自然排序问题

在 MySQL 中实现真正的字母数字排序（自然排序）确实是一个常见挑战，而且解决方案通常涉及递归或类似递归的技术。让我解释为什么会出现这种情况以及如何解决。

为什么需要递归方法

字母数字排序（如 "item1", "item2", "item10" 按数字顺序排列）在 MySQL 中不是默认行为的原因：

1. 字符串比较机制：MySQL 默认使用字典序比较字符串，逐个字符比较
2. 混合数据类型：字符串中的数字部分需要被识别并转换为数值进行比较
3. 可变长度模式：数字部分可能出现在任意位置且长度不固定

解决方案

1. 使用正则表达式提取数字

SELECT column_name 
FROM table_name 
ORDER BY 
    CAST(REGEXP_SUBSTR(column_name, '[0-9]+') AS UNSIGNED),
    column_name;

2. 递归技术（MySQL 8.0+ 使用 CTE）

WITH RECURSIVE split_string AS (
    SELECT 
        column_name,
        REGEXP_REPLACE(column_name, '[0-9]+', '') AS text_part,
        REGEXP_REPLACE(column_name, '[^0-9]+', '') AS num_part
    FROM table_name
)
SELECT column_name
FROM split_string
ORDER BY text_part, CAST(num_part AS UNSIGNED);

3. 自定义函数方法

对于更复杂的情况，可以创建存储函数：

DELIMITER //
CREATE FUNCTION natural_sort_key(s VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 1;
    DECLARE len INT;
    DECLARE c CHAR(1);
    DECLARE is_num BOOLEAN DEFAULT FALSE;

    SET len = CHAR_LENGTH(s);

    WHILE i <= len DO
        SET c = SUBSTRING(s, i, 1);
        IF c BETWEEN '0' AND '9' THEN
            IF NOT is_num THEN
                SET result = CONCAT(result, ' ');
                SET is_num = TRUE;
            END IF;
        ELSE
            SET is_num = FALSE;
        END IF;
        SET result = CONCAT(result, c);
        SET i = i + 1;
    END WHILE;

    RETURN result;
END //
DELIMITER ;

-- 使用
SELECT column_name FROM table_name ORDER BY natural_sort_key(column_name);

为什么递归是常见答案

1. 分而治之：需要将字符串分解为文本和数字部分分别处理
2. 模式识别：需要识别字符串中的数字和非数字模式
3. 组合结果：需要将处理后的部分重新组合以进行正确排序

在 MySQL 8.0 之前，没有内置函数可以直接实现这种排序，因此开发者需要创造性地使用字符串函数组合或存储过程来模拟递归处理。

MySQL 8.0+ 的 CTE 功能使得这种递归处理更加直观和高效。