MySQL中TRUNCATE函数导致计算错误的排查与解决

成因 #

• 核心是二进制浮点数的精度缺陷：MySQL里的FLOAT/DOUBLE类型基于IEEE 754二进制浮点数标准，像19.91、0.08这类十进制小数无法用二进制精确存储，会存在微小的存储误差。执行19.91 + 0.08时，实际计算结果并非精确的19.99，而是一个略小于19.99的数值（比如19.989999999999995）。
• TRUNCATE()函数的特性是直接截断指定小数位，不会进行四舍五入。对这个略小于19.99的数值截断两位小数时，就会得到19.98，而非预期的19.99。
• 移除TRUNCATE或直接输入19.99正常的原因：MySQL会对接近目标值的浮点数做隐式近似处理，直接输入19.99会被识别为精确目标值；不加TRUNCATE时，微小的精度误差会被近似逻辑掩盖，最终显示或存储为19.99。

解决方法 #

• 改用DECIMAL类型存储数值：DECIMAL是精确的十进制类型，能精准存储十进制小数，从根源避免浮点数精度问题。比如将balance字段定义为balance DECIMAL(10,2)，此时19.91 + 0.08会得到精确的19.99，再使用TRUNCATE也能得到正确结果。
• 用ROUND()替代TRUNCATE()：如果必须使用浮点数类型，ROUND(19.91 + 0.08, 2)会对计算结果进行四舍五入，即使存在微小误差，也能得到预期的19.99。注意ROUND是四舍五入逻辑，TRUNCATE是截断逻辑，需根据业务需求选择。
• 先转换为DECIMAL再计算：在计算前将浮点数转为DECIMAL类型消除精度误差，例如执行TRUNCATE(CAST(19.91 AS DECIMAL(10,2)) + CAST(0.08 AS DECIMAL(10,2)), 2)，计算过程为精确的十进制运算，截断后结果正确。

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