MySQL 中的 DOUBLE 与 DECIMAL
好的，所以我知道有很多文章指出我不应该使用 DOUBLE 在 MySQL 数据库上存储资金，否则我最终会遇到棘手的精度错误.关键是我不是在设计一个新的数据库，我被要求找到优化现有系统的方法.较新的版本包含 783 个 DOUBLE 类型的列，其中大部分用于存储货币或计算金额的公式.
OK, so I know there are tons of articles stating I shouldn't use DOUBLE to store money on a MySQL database, or I'll end up with tricky precision bugs. The point is I am not designing a new database, I am ask to find way to optimise an existing system. The newer version contains 783 DOUBLE typed columns, most of them used to store money or formula to compute money amount.
所以我对这个主题的第一个意见是我强烈建议在下一个版本中从 DOUBLE 转换为 DECIMAL，因为 MySQL 文档和每个人都这么说.但后来我找不到任何好的论据来证明这一建议的合理性，原因有三个:
So my first opinion on the subject was I should highly recommend a conversion from DOUBLE to DECIMAL in the next version, because the MySQL doc and everybody say so. But then I couldn't find any good argument to justify this recommandation, for three reasons :
- 我们不对数据库执行任何计算.所有操作均使用 BigDecimal 在 Java 中完成，而 MySQL 仅用作结果的普通存储.
- DOUBLE 提供的 15 位精度已经足够了，因为我们主要存储 2 位十进制数字的数量，偶尔也会存储带有 8 位十进制数字的小数字作为公式参数.
- 我们有 6 年的生产记录，没有因 MySQL 端精度损失而导致的已知错误问题.
即使在 1800 万行的表上执行操作，如 SUM 和复杂的乘法，我也无法执行精度不足的错误.我们实际上并没有在生产中做这种事情.我可以通过执行诸如
Even by performing operations on a 18 millons rows table, like SUM and complex multiplications, I couldn't perform a bug of lack of precision. And we don't actually do this sort of things in production. I can show the precision lost by doing something like
SELECT columnName * 1.000000000000000 FROM tableName;
但我想不出办法把它变成第二个十进制数字的错误.我在互联网上发现的大多数实际问题都是 2005 年及更早的论坛条目，我无法在 5.0.51 MySQL 服务器上重现其中任何一个.
But I can't figure out a way to turn it into a bug at the 2nd decimal digit. Most of the real issues I found on the internet are 2005 and older forum entries, and I couldn't reproduce any of them on a 5.0.51 MySQL server.
因此，只要我们不执行任何我们不打算执行的 SQL 算术运算，我们是否应该期望仅在 DOUBLE 列中存储和检索金额会出现任何问题?
So as long as we do not perform any SQL arithmetic operations, which we do not plan to do, are there any issue we should expect from only storing and retreiving a money amount in a DOUBLE column ?
推荐答案
实际上是完全不同的.DOUBLE 会导致舍入问题.如果你做类似 0.1 + 0.2 的事情，它会给你类似 0.30000000000000004 的东西.我个人不会相信使用浮点数学的财务数据.影响可能很小，但谁知道呢.我宁愿拥有我所知道的可靠数据而不是近似数据，尤其是在处理货币价值时.
Actually it's quite different. DOUBLE causes rounding issues. And if you do something like 0.1 + 0.2 it gives you something like 0.30000000000000004. I personally would not trust financial data that uses floating point math. The impact may be small, but who knows. I would rather have what I know is reliable data than data that were approximated, especially when you are dealing with money values.
相关文章