MySQL中如何对VARCHAR类型存储的十进制数排序

要实现你需要的自然排序，核心是将无小数点的记录的小数部分视为0，而不是误将整个字符串当作小数部分。以下是正确的SQL语句：

SELECT number_of_points
FROM meetings_points
WHERE meeting = 1026
ORDER BY
    -- 第一优先级：按小数点前的整数排序
    CAST(SUBSTRING_INDEX(number_of_points, '.', 1) AS UNSIGNED),
    -- 第二优先级：按小数点后的整数排序，无小数则取0
    CAST(
        CASE 
            WHEN INSTR(number_of_points, '.') > 0 THEN SUBSTRING_INDEX(number_of_points, '.', -1)
            ELSE '0'
        END AS UNSIGNED
    );

或者用更简洁的IF函数写法：

SELECT number_of_points
FROM meetings_points
WHERE meeting = 1026
ORDER BY
    CAST(SUBSTRING_INDEX(number_of_points, '.', 1) AS UNSIGNED),
    CAST(IF(INSTR(number_of_points, '.'), SUBSTRING_INDEX(number_of_points, '.', -1), '0') AS UNSIGNED);

为什么你之前的方法失效？ #

你之前的所有尝试都忽略了一个关键问题：对于没有小数点的记录（比如15），SUBSTRING_INDEX(number_of_points, '.', -1)会返回整个字符串15，转成整数后是15，比15.1的小数部分1大。这就导致15被排在所有15.x的后面，和预期相反。

而上面的解决方案通过CASE/IF判断是否存在小数点，无小数时强制将小数部分设为0，确保15（小数部分0）排在15.1（小数部分1）、15.11（小数部分11）等所有带小数的记录前面。

验证结果 #

执行上述SQL后，会得到你想要的排序结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
15.1
15.2
15.3
15.4
15.5
15.6
15.7
15.8
15.9
15.10
15.11
16
17
18

内容的提问来源于stack exchange，提问作者Václav Kraus