Navicat 博客

MySQL 中的浮点数舍入错误 2021 年 9 月 3 日,由 Robert Gravelle 撰写

尽管 MySQL DECIMAL 和 NUMERIC 数据类型都是固定点值,但它们仍然容易出现舍入错误。 原因是,无论一个类型可以容纳多少位数(DECIMAL 的最大位数是 65!),该数字仍然是固定的。此外,DECIMAL 列可以被分配精度或小数位数,这可能会对允许的位数产生截断的潜在影响。

Navicat 表设计器中的 DECIMAL 列
decimal_column_in_navicat (43K)

当一位读者问我为什么几个类似的查询在计算中返回 DECIMAL 值时略有不同,我意识到 MySQL 中潜在的舍入错误。这促使我踏上了探索之旅。在今天的文章中,我想分享一些我对 MySQL 中浮点数舍入的了解。

两个查询的故事

以下是我用来显示差异的查询:

使用子查询计算每小时付款
subquery (37K)
使用 GROUP BY WITH ROLLUP 计算每小时付款
group_by (57K)

注意:从 GROUP BY WITH ROLLUP 查询中删除了一些行以降低图像的高度。

读者正在计算员工工资,但我没有一个相同的表可以查询,所以我使用了我能找到的最相似的表,那就是 classicmodels 示例数据库的 payments 表:

payments_table (114K)

在这种情况下,计算每小时付款可能没有多大意义,但查询确实突出了两个 SELECT 语句之间的舍入差异(4256.653475 与 4256.653476)。

那么,为什么使用子查询和 GROUP BY WITH ROLLUP 进行 SUM 时会产生不同的结果?

浮点近似值与固定点精确值

浮点(近似值)类型是 FLOAT、REAL 和 DOUBLE,而固定点(精确值)类型是 INTEGER、SMALLINT、DECIMAL 和 NUMERIC。浮点数意味着小数点可以放在相对于数字有效数字的任何位置,实际位置单独表示。同时,定点值是按特定因子缩放的整数。

在 5.5 版本中,MySQL 增加了对精确数学的支持,其中包括一个用于固定点数算法的库,该库取代了基础的 C 库,并允许在不同平台上以相同的方式处理操作。自此更新以来,如果在计算中没有使用近似值或字符串,则使用精度为 65 位的 DECIMAL 精确值算术计算表达式。对于 GROUP BY 函数,STDDEV() 和 VARIANCE() 返回 DOUBLE,一种近似浮点类型,而 SUM() 和 AVG() 为精确值参数返回 DECIMAL,为近似值返回 DOUBLE。

用于固定点數算术的新 MySQL 库的另一个分支是现在使用浮点值处理类型转换因此,类型转换的结果可能会有所不同,并可能受到计算机体系结构、编译器版本甚至优化级别等因素的影响。避免这些问题的一种方法是使用显式 CAST() 而不是隐式转换。

哪个结果是正确的?

回到最初的查询,哪个值更准确,哪个查询是获取 SUM 的正确方法?事实是,两者都不准确,但是,通过使用一些代数,可以简化查询以产生准确的结果:

simplified_query (30K)

令四舍五入精确的关键是尽可能在初始步骤中使用整数。

Navicat 文章
频道条目
分享
文章归档