如何在MySQL中强制DIFF字段类型为DECIMAL(23,0)而非DECIMAL(270,30)

首先注意到你SQL里的笔误：@diff := @query - @location应该是@diff := @foo - @bar吧？先修正这个问题，再给你几种解决类型不一致的方法：

方法1：直接使用SUM结果计算（推荐） #

不用用户变量中转，直接对两个SUM的结果做减法，这样DIFF的类型会自动和FOO、BAR保持一致：

SELECT 
    SUM(t.data LIKE '%foo%') AS FOO,
    SUM(t.data LIKE '%bar%') AS BAR,
    SUM(t.data LIKE '%foo%') - SUM(t.data LIKE '%bar%') AS DIFF
FROM MyTable t GROUP BY groupId

原因是SUM()返回的DECIMAL(23,0)类型值相减后，结果类型会继承原类型的精度，不会出现不必要的高精度扩展。

方法2：用CAST强制转换类型（保留变量用法） #

如果必须使用用户变量，可以通过CAST()或CONVERT()将差值强制转换为DECIMAL(23,0)：

SELECT 
    @foo := SUM(t.data LIKE '%foo%') AS FOO,
    @bar := SUM(t.data LIKE '%bar%') AS BAR,
    @diff := CAST(@foo - @bar AS DECIMAL(23,0)) AS DIFF
FROM MyTable t GROUP BY groupId

这种方式明确指定了DIFF的类型，和FOO、BAR完全匹配。

为什么原来的写法会出问题？ #

MySQL的用户变量类型是动态推导的，当你用变量做减法运算时，MySQL可能会自动提升精度以避免溢出，导致生成DECIMAL(270,30)这种超大精度类型；而直接使用SUM()的结果运算时，因为两个操作数都是DECIMAL(23,0)，运算结果的类型会保持一致，不会触发不必要的精度提升。

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