如何在SQL中实现类似C语言strverscmp()的版本号排序？

问题背景 #

当对存储版本号的列versnum使用ORDER BY versnum排序时，得到的是字典序结果：

2.4.11
2.4.12
2.4.5
2.4.6

需要实现类似C语言strverscmp()的自然排序，得到正确顺序：

2.4.5
2.4.6
2.4.11
2.4.12

标准SQL能否实现？ #

标准SQL没有内置的自然排序函数，默认字符串排序采用字典序，会将"2.4.11"中的"1"视为小于"5"，导致排序错误。无法仅通过标准SQL操作符或内置函数直接实现自然排序，需要依赖数据库特定功能或自定义逻辑。

自定义版本号格式化函数（按4位补零方案） #

以下是将版本号各段扩展为4位数字的自定义函数示例（以MySQL为例）：

DELIMITER //
CREATE FUNCTION format_version(vers VARCHAR(255)) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE parts TEXT;
    DECLARE result TEXT DEFAULT '';
    DECLARE part VARCHAR(255);
    DECLARE pos INT DEFAULT 1;
    
    -- 将版本号按.分割为数组
    SET parts = CONCAT(vers, '.');
    WHILE LOCATE('.', parts, pos) > 0 DO
        SET part = SUBSTRING(parts, pos, LOCATE('.', parts, pos) - pos);
        -- 补零到4位，拼接结果
        SET result = CONCAT(result, LPAD(part, 4, '0'), '.');
        SET pos = LOCATE('.', parts, pos) + 1;
    END WHILE;
    -- 移除末尾的.
    RETURN LEFT(result, LENGTH(result) - 1);
END //
DELIMITER ;

使用方式：

SELECT versnum 
FROM your_table 
ORDER BY format_version(versnum);

该函数会将2.4.5转换为0002.0004.0005，2.4.11转换为0002.0004.0011，此时字典序排序即可得到正确的自然排序结果。

方案总结 #

1. 存储规范化版本号 #

• 方案：在插入或更新数据时，同时存储原始版本号和格式化后的规范化版本号（如上述4位补零格式）。
• 优势：排序时无需实时计算，性能最优。
• 劣势：需要额外存储字段，数据写入时需要处理格式化逻辑。

2. 自定义SQL函数 #

• 方案：编写如上述的自定义函数，排序时动态格式化版本号。
• 优势：无需修改表结构，适配大多数数据库。
• 劣势：排序时需要实时计算，大数据量下可能影响性能。

3. MySQL专属版本号排序方案 #

MySQL提供了SUBSTRING_INDEX函数，可以直接拆分版本号各段并转换为数字排序：

SELECT versnum
FROM your_table
ORDER BY
    CAST(SUBSTRING_INDEX(versnum, '.', 1) AS UNSIGNED),
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(versnum, '.', 2), '.', -1) AS UNSIGNED),
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(versnum, '.', 3), '.', -1) AS UNSIGNED);

这种方式无需自定义函数，直接通过拆分版本号为数字段排序，适合固定段数的版本号（如三段式）。如果版本号段数不固定，可以结合自定义函数或更复杂的拆分逻辑。

内容的提问来源于stack exchange，提问作者Wiimm