SQL Server中REAL转INT超范围安全转换及精度问题咨询

一、精度异常的原因 #

SQL Server中的REAL是单精度浮点数，仅包含24位有效二进制位，对应约7-8位十进制有效数字。而INT类型的最大值2147483647是10位十进制数，超出了REAL的精确表示范围：

• 当你尝试将2147483647转换为REAL时，这个值无法被单精度浮点数精确存储，会被近似舍入到最近的可表示值2147483648（即2.147484E+09），这就是为什么设置值后用STR()查看会变成2147483648。
• 对于2147483xxx这类接近INT最大值的数，REAL的精度不足会导致相邻的十进制数被映射到同一个浮点数表示，比如末三位在[457→584)区间的数会被近似为2147483520，[584→777)区间的数会被近似为2147483658——本质是单精度浮点数的离散特性，无法精确区分这些紧邻的数值。

你的UPDATE语句报错，是因为CASE分支里的CAST(p.Quantity AS INT)执行时，那些被REAL近似后的数值（比如2147483648）已经超过了INT的最大值，即便你在WHEN条件里做了判断，也因为REAL的精度问题导致判断失效——你用来比较的CAST(2147483647 AS REAL)本身已经是2147483648，所以当原Quantity是被近似后的2147483648时，条件判断不成立，进入ELSE分支执行CAST就会触发溢出。

二、安全转换REAL到INT的方案 #

方案1：使用BIGINT作为中间转换层，再限制范围 #

先将REAL转换为BIGINT（避免溢出），再判断是否超过INT最大值，最后转换为INT：

UPDATE p
SET p.Quantity = CASE 
    WHEN CAST(p.Quantity AS BIGINT) > 2147483647 THEN 2147483647
    ELSE CAST(CAST(p.Quantity AS BIGINT) AS INT)
END
FROM Product p WITH(ROWLOCK)

方案2：使用DECIMAL作为中间转换（更精确的浮点转定点） #

利用DECIMAL的高精度特性，先将REAL转换为足够精度的DECIMAL，再做范围判断和转换：

UPDATE p
SET p.Quantity = CASE 
    WHEN CAST(p.Quantity AS DECIMAL(10,0)) > 2147483647 THEN 2147483647
    ELSE CAST(CAST(p.Quantity AS DECIMAL(10,0)) AS INT)
END
FROM Product p WITH(ROWLOCK)

方案3：直接使用INT最大值的REAL安全阈值 #

由于REAL无法精确表示2147483647，我们可以用REAL能精确表示的、小于等于INT最大值的最大数值作为判断阈值，这个值是2147483520（对应二进制可精确表示的边界）：

UPDATE p
SET p.Quantity = CASE 
    WHEN p.Quantity > 2147483520 THEN 2147483647
    ELSE CAST(p.Quantity AS INT)
END
FROM Product p WITH(ROWLOCK)

注意：如果业务允许，也可以考虑直接将Quantity列的数据类型修改为BIGINT，从根源避免这类精度和溢出问题。

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