MySQL中的DOUBLE与DECIMAL [英] DOUBLE vs DECIMAL in MySQL

问题描述

好的，所以我知道有大量的文章说我不应该用DOUBLE来储存一个MySQL数据库的钱，否则我最终会遇到棘手的错误。关键是我没有设计一个新的数据库，我要求找到优化现有系统的方法。较新版本包含783个DOUBLE类型的列，其中大多数用于存储钱或公式来计算金额。

所以我对此主题的第一个意见是我应该强烈推荐在下一个版本中从DOUBLE转换为DECIMAL，因为MySQL文档和大家都这么说。但是，由于以下三个原因，我找不到任何好的论据来证明这一点：

• 我们不对数据库执行任何计算。所有操作都是使用BigDecimal在Java中完成的，MySQL只是作为一个简单的存储结果使用。


• DOUBLE提供的15位数精度足够大，因为我们主要存储2位小数数字，偶尔的小数字，配有公式参数的8位十进制数字。


• 由于MySQL方面的精度损失，我们在生产中有6年的记录，没有发现错误。

即使通过对18M行的表执行操作，如SUM和复数乘法，我无法执行缺少精确。而我们实际上并没有在生产中做这样的事情。我可以通过执行像

SELECT columnName * 1.000000000000000 FROM tableName;

$来显示精度损失b
$ b

但是我无法找出一种将其变成第二位数字的错误的方法。我在互联网上发现的大多数真实问题是2005年和更早的论坛条目，我无法在5.0.51 MySQL服务器上复制任何内容。

所以只要我们不执行我们不打算做的任何SQL算术运算，是否有任何问题，我们应该期望只存储和退回金额在DOUBLE列？

解决方案

其实与之不同。 DOUBLE导致四舍五入的问题。如果你这样做一些类似 0.1 + 0.2 的东西，它会给你一些类似 0.30000000000000004 的东西。我个人不会相信使用浮点数学的财务数据。影响可能很小，但谁知道。我宁愿拥有我所知道的可靠数据，而不是近似的数据，特别是在处理金钱价值时。

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.

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 :

• We do not perform any calculation on the database. All operations are done in Java using BigDecimal, and MySQL is just used as a plain storage for results.
• The 15 digits precision a DOUBLE offers is plenty enough since we store mainly amounts with 2 decimal digits, and occasionaly small numbers wit 8 decimal digits for formula arguments.
• We have a 6 years record in production with no known issue of bug due to a loss of precision on the MySQL side.

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;

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.

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 ?

解决方案

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.

这篇关于MySQL中的DOUBLE与DECIMAL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！