MySQL中CASE、RAND函数使用结果不符问题求助

核心原因 #

你的查询存在两个关键问题：

• rand() 函数被多次执行：MySQL中，未物化的派生表（子查询）里的 rand()，在外部查询的多个字段中被引用时，会被重复计算。也就是说，你看到的 r 列值是一次随机结果，而 r > '0.5'、CASE 语句中使用的是另一次（甚至多次）生成的随机值，导致结果完全不符。
• 字符串与数值的隐式比较：你用字符串字面量（如 '0.5'）和数值类型的 r 比较，虽然MySQL会做隐式转换，但这种写法容易引发意外问题，建议直接使用数值（如 0.5）进行比较。

修复后的SQL #

方法1：使用CTE（MySQL 8.0及以上版本） #

CTE会物化结果，确保rand()只计算一次：

WITH t AS (
    SELECT id, rand() r FROM users
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY id) row_num,
    r,
    r > 0.5,
    r > 0.2,
    r > 0.1,
    CASE
        WHEN r > 0.5 THEN '0.5'
        WHEN r > 0.2 THEN '0.2'
        WHEN r > 0.1 THEN '0.1'
        ELSE '0'
    END AS actual
FROM t;

方法2：强制派生表物化（兼容低版本MySQL） #

通过GROUP BY让优化器物化派生表，固定rand()结果：

SELECT 
    ROW_NUMBER() OVER (ORDER BY id) row_num,
    r,
    r > 0.5,
    r > 0.2,
    r > 0.1,
    CASE
        WHEN r > 0.5 THEN '0.5'
        WHEN r > 0.2 THEN '0.2'
        WHEN r > 0.1 THEN '0.1'
        ELSE '0'
    END AS actual
FROM (
    SELECT id, rand() r FROM users GROUP BY id
) t;

验证说明 #

修复后，r列的值与所有比较、CASE语句中使用的是同一个随机数，结果会符合预期。比如当r=0.3050642603124159时，会正确返回：

1   0.3050642603124159  0   1   1   0.2

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