求助：MySQL实现smsfilename列按带点数字+字母排序

原表数据 #

期望排序结果 #

失败的尝试语句 #

select * from smsproceduresfiles order by cast('/' + replace(smsFileName , '.', '/') + '/' as UNSIGNED) ASC;

为什么这个语句不行？ #

MySQL里+是算术加法运算符，不是字符串拼接符，所以你写的'/' + replace(...)会被强制转换成数值计算，结果完全不符合预期。另外，就算换成字符串拼接，把版本号转成带/的格式后再转UNSIGNED，也只会提取第一个数字部分，没法处理多级版本号的排序需求。

正确的实现方法 #

方法1：拆分版本号分段排序（通用所有MySQL版本） #

因为字段格式是「版本号 + 空格 + 文本」，我们可以把版本号按.拆分成多段数字，分别排序，最后再按空格后的文本排序：

SELECT *
FROM smsproceduresfiles
ORDER BY
    -- 提取版本号第一段并转成数字
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(smsfilename, ' ', 1), '.', 1) AS UNSIGNED),
    -- 提取版本号第二段，没有的话用第一段的值（比如1.1的第二段就是1）
    CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(smsfilename, ' ', 1), '.', -2), SUBSTRING_INDEX(smsfilename, ' ', 1)) AS UNSIGNED),
    -- 提取版本号第三段，没有的话用0
    CAST(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(smsfilename, ' ', 1), '.', -1), 0) AS UNSIGNED),
    -- 最后按空格后的文本排序
    SUBSTRING_INDEX(smsfilename, ' ', -1);

方法2：补位版本号实现字符串排序（适合固定层级版本） #

把版本号的每一段都补成固定长度（比如3位），这样字符串排序就能和数字排序效果一致：

SELECT *
FROM smsproceduresfiles
ORDER BY
    -- 把版本号转成001.001.010这种格式，字符串排序就会正确
    CONCAT(
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(smsfilename, ' ', 1), '.', 1), 3, '0'),
        '.',
        LPAD(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(smsfilename, ' ', 1), '.', -2), SUBSTRING_INDEX(smsfilename, ' ', 1)), 3, '0'),
        '.',
        LPAD(IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(smsfilename, ' ', 1), '.', -1), 0), 3, '0')
    ),
    SUBSTRING_INDEX(smsfilename, ' ', -1);

方法3：MySQL 8.0+ 简化写法 #

利用正则提取版本号，再分段处理：

SELECT *
FROM smsproceduresfiles
ORDER BY
    -- 正则提取版本号部分
    CAST(SUBSTRING_INDEX(REGEXP_SUBSTR(smsfilename, '^[0-9.]+'), '.', 1) AS UNSIGNED),
    CAST(IFNULL(SUBSTRING_INDEX(REGEXP_SUBSTR(smsfilename, '^[0-9.]+'), '.', -2), REGEXP_SUBSTR(smsfilename, '^[0-9.]+')) AS UNSIGNED),
    CAST(IFNULL(SUBSTRING_INDEX(REGEXP_SUBSTR(smsfilename, '^[0-9.]+'), '.', -1), 0) AS UNSIGNED),
    REGEXP_SUBSTR(smsfilename, '\\s.*$');

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