MySQL中能否在WHERE子句使用CASE WHEN并为变量赋值？

MySQL本身不会禁止在WHERE子句中写@var := CASE...END这类赋值语法，但你遇到的返回0行结果、赋值不生效的问题，是MySQL用户变量的固有运行机制导致的，不属于语法书写错误。

• MySQL官方对@开头的用户自定义变量的求值顺序没有做稳定语义保证：变量赋值的实际执行时机，会受优化器生成的执行计划、表的索引选择、表达式类型影响，不保证WHERE子句中的赋值操作，一定在SELECT子句读取该变量之前逐行完成。
• 测试@test:=1能正常返回结果，是因为常量赋值会被查询优化器提前静态求值，不需要逐行计算，不会触发时序问题。而CASE...WHEN...THEN属于逐行计算的动态表达式，优化器不会提前执行，此时一方面赋值操作和SELECT读变量的顺序不可控，另一方面CASE返回的字符串值（'numeric'/'non numeric'）会被隐式转换为布尔值作为WHERE过滤条件，双重影响下就会出现返回0行、变量值不符合预期的问题。
• 所有在WHERE、GROUP BY、ORDER BY等子句中通过:=给用户变量赋值、再在其他子句读取的写法，都属于MySQL的非标准兼容用法，官方不承诺行为一致性，跨版本、换索引都可能出现结果变化，生产环境不建议使用。

如果要实现逐行判断赋值同时返回结果，选择以下两种可保证结果一致的写法即可：

1. 把CASE赋值逻辑下推到子查询的SELECT中，外层再读取变量或字段

SELECT 
    id, @test
FROM (
    SELECT 
        id,
        @test:= CASE
            WHEN ratetype_id IN (1 , 2, 7, 9) THEN 'numeric'
            ELSE 'non numeric'
        END AS test_flag
    FROM ratetypes
) t
-- 若需要按判断结果过滤，直接在外层加WHERE条件即可，例如 WHERE test_flag = 'numeric'

2. 如果不需要在后续逻辑中复用该变量值，完全没必要使用用户变量，直接在SELECT中写CASE表达式即可，可读性和稳定性更高：

SELECT 
    id,
    CASE
        WHEN ratetype_id IN (1 , 2, 7, 9) THEN 'numeric'
        ELSE 'non numeric'
    END AS test
FROM ratetypes

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