MySQL中SELECT内初始化用户变量为字符串类型的异常及类型指定咨询

核心结论 #

MySQL无法在SELECT语句内直接指定用户变量的数据类型，但可以通过初始化时的类型转换操作，强制变量和目标字段保持一致的类型，彻底解决首次执行的类型不匹配问题。

问题原因 #

首次连接执行查询时，@x被初始化为字符串类型的'0'，和bigint类型的amount做!=比较时，MySQL会把字符串转成数值。但你的amount里包含超大整数（比如111115000000062760），字符串转数值时会出现精度丢失，导致转换后的值和原bigint值不相等，所以错误返回NULL。后续执行时@x被赋值为bigint类型，比较逻辑就恢复正常了。

修复方案 #

在子查询初始化@x时，用类型转换函数把初始值转为bigint类型，比如CAST()或者CONVERT()：

select 
    CASE
         WHEN (@x != amount) THEN amount 
    END result, 
    CASE 
         WHEN ((@x) != amount) THEN @x:=amount 
    END dummy 
from (select amount, @x:=CAST(0 AS UNSIGNED BIGINT) x from a) q;

也可以用简单的数值运算强制类型（写法更简洁）：

select 
    CASE
         WHEN (@x != amount) THEN amount 
    END result, 
    CASE 
         WHEN ((@x) != amount) THEN @x:=amount 
    END dummy 
from (select amount, @x:=0+0 x from a) q;

这样@x从一开始就是bigint类型，和amount比较时不会触发有精度损失的隐式转换，首次执行就能得到正确结果。

额外提示 #

• 也可以在执行查询前单独运行SET @x = CAST(0 AS UNSIGNED BIGINT);来初始化变量，但这种方式需要多执行一条语句，不如子查询内初始化来得方便。
• 如果你的amount值超出了bigint的范围，建议改成字符串存储并做字符串比较，但当前场景用上述方案完全可行。

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