SQL Server 2005中float列看似相等但查询无结果的原因及解决方法

嘿，这个问题我太熟了——这完全是float类型的近似数值特性在搞鬼！

问题根源 #

Float（包括real）是SQL Server里的近似数值类型，它遵循IEEE 754标准存储二进制浮点数。很多十进制数（哪怕是整数）都无法被精确表示，只能存储一个接近的近似值。你看到的column1和column2显示都是1000，但实际上它们在二进制存储层面可能存在极其微小的差异（比如一个是1000.0000000001，另一个是999.9999999999），只是客户端显示的时候自动做了四舍五入，让你误以为完全相等。

至于转成decimal后二者相等，是因为decimal是精确数值类型，转换过程中会把float的近似值截断/四舍五入成精确的十进制表示，抹掉了那些微小的差异，但原始的float存储值本身还是不一样的。

解决办法 #

针对这个问题，有几个靠谱的处理方案：

• 用范围比较替代等值判断：不要直接用=，而是判断两个值的差的绝对值小于一个极小的业务允许阈值。比如：

  SELECT * FROM table 
  WHERE ref = 1 
  AND ABS(column1 - column2) < 0.000001 -- 阈值根据你的业务精度需求调整
  

• 替换为精确数值类型：如果你的业务场景需要精确的存储和比较，直接把column1和column2的类型改成DECIMAL或NUMERIC。这是从根源上解决问题的办法，适合不需要浮点运算的场景。
• 查询时转换为精确类型再比较：如果不能修改表结构，查询时先把float转成decimal再做等值判断，注意设置合适的精度和小数位数：

  SELECT * FROM table 
  WHERE ref = 1 
  AND CAST(column1 AS DECIMAL(18,6)) = CAST(column2 AS DECIMAL(18,6))
  

  这里的DECIMAL(18,6)可以根据你实际数据的数值范围和精度调整。

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