MySQL 中的 DOUBLE 与 DECIMAL

DOUBLE vs DECIMAL in MySQL

提问by user327961

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 在 MySQL 数据库上存储资金，否则我最终会遇到棘手的精度错误。关键是我不是在设计一个新的数据库，我被要求找到优化现有系统的方法。较新的版本包含 783 个 DOUBLE 类型的列，其中大部分用于存储货币或计算金额的公式。

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 :

所以我对这个主题的第一个意见是我应该强烈推荐在下一个版本中从 DOUBLE 转换为 DECIMAL，因为 MySQL 文档和每个人都这么说。但后来我找不到任何好的论据来证明这一建议的合理性，原因有以下三个：

• 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.

• 我们不对数据库执行任何计算。所有操作都使用 BigDecimal 在 Java 中完成，而 MySQL 只是用作结果的普通存储。
• DOUBLE 提供的 15 位精度已经足够了，因为我们主要存储 2 位十进制数字的数量，偶尔存储带有 8 位十进制数字的小数字作为公式参数。
• 我们有 6 年的生产记录，没有由于 MySQL 端精度损失而导致的已知错误问题。

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

即使在 1800 万行的表上执行操作，如 SUM 和复杂的乘法，我也无法执行缺乏精度的错误。我们实际上并没有在生产中做这种事情。我可以通过做类似的事情来显示精度损失

SELECT columnName * 1.000000000000000 FROM tableName;

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.

但我想不出办法把它变成第二个十进制数字的错误。我在 Internet 上发现的大多数实际问题是 2005 年及更早的论坛条目，我无法在 5.0.51 MySQL 服务器上重现它们中的任何一个。

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 ?

因此，只要我们不执行任何我们不打算执行的 SQL 算术运算，我们是否应该期望仅在 DOUBLE 列中存储和检索金额会出现任何问题？

采纳答案by bash-

Actually it's quite different. DOUBLE causes rounding issues. And if you do something like 0.1 + 0.2it 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.

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

回答by broadband

The example from MySQL documentation http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html(i shrink it, documentation for this section is the same for 5.5)

MySQL 文档中的示例http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html（我缩小了它，本节的文档与 5.5 相同）

mysql> create table t1 (i int, d1 double, d2 double);

mysql> insert into t1 values (2, 0.00  , 0.00),
                             (2, -13.20, 0.00),
                             (2, 59.60 , 46.40),
                             (2, 30.40 , 30.40);

mysql> select
         i,
         sum(d1) as a,
         sum(d2) as b
       from
         t1
       group by
         i
       having a <> b; -- a != b

+------+-------------------+------+
| i    | a                 | b    |
+------+-------------------+------+
|    2 | 76.80000000000001 | 76.8 |
+------+-------------------+------+
1 row in set (0.00 sec)

Basically if you sum a you get 0-13.2+59.6+30.4 = 76.8. If we sum up b we get 0+0+46.4+30.4=76.8. The sum of a and b is the same but MySQL documentation says:

基本上，如果你对 a 求和，你会得到 0-13.2+59.6+30.4 = 76.8。如果我们对 b 求和，我们得到 0+0+46.4+30.4=76.8。a 和 b 的总和相同，但 MySQL 文档说：

A floating-point value as written in an SQL statement may not be the same as the value represented internally.

SQL 语句中写入的浮点值可能与内部表示的值不同。

If we repeat the same with decimal:

如果我们用十进制重复相同的操作：

mysql> create table t2 (i int, d1 decimal(60,30), d2 decimal(60,30));
Query OK, 0 rows  affected (0.09 sec)

mysql> insert into t2 values (2, 0.00  , 0.00),
                             (2, -13.20, 0.00),
                             (2, 59.60 , 46.40),
                             (2, 30.40 , 30.40);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select
         i,
         sum(d1) as a,
         sum(d2) as b
       from
         t2
       group by
         i
       having a <> b;

Empty set (0.00 sec)

The result as expected is empty set.

预期的结果是空集。

So as long you do not perform any SQL arithemetic operations you can use DOUBLE, but I would still prefer DECIMAL.

所以只要你不执行任何 SQL 算术运算，你就可以使用 DOUBLE，但我仍然更喜欢 DECIMAL。

Another thing to note about DECIMAL is rounding if fractional part is too large. Example:

关于 DECIMAL 的另一件事是如果小数部分太大则四舍五入。例子：

mysql> create table t3 (d decimal(5,2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t3 (d) values(34.432);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t3;
+-------+
| d     |
+-------+
| 34.43 |
+-------+
1 row in set (0.00 sec)

回答by Mark

We have just been going through this same issue, but the other way around. That is, we store dollar amounts as DECIMAL, but now we're finding that, for example, MySQL was calculating a value of 4.389999999993, but when storing this into the DECIMAL field, it was storing it as 4.38 instead of 4.39 like we wanted it to. So, though DOUBLE may cause rounding issues, it seems that DECIMAL can cause some truncating issues as well.

我们刚刚经历了同样的问题，但反过来。也就是说，我们将美元金额存储为 DECIMAL，但现在我们发现，例如，MySQL 计算的值是 4.389999999993，但是当将其存储到 DECIMAL 字段时，它将其存储为 4.38 而不是我们想要的 4.39它到。因此，尽管 DOUBLE 可能会导致舍入问题，但 DECIMAL 似乎也会导致一些截断问题。

回答by rajah9

From your comments,

从你的评论来看，

the tax amount rounded to the 4th decimal and the total price rounded to the 2nd decimal.

税额四舍五入到小数点后第四位，总价四舍五入到小数点后第二位。

Using the example in the comments, I might foresee a case where you have 400 sales of $1.47. Sales-before-tax would be $588.00, and sales-after-tax would sum to $636.51 (accounting for $48.51in taxes). However, the sales tax of $0.121275 * 400 would be $48.52.

使用评论中的示例，我可能会预见到您有 400 份销售额为 1.47 美元的情况。税前销售额为 588.00 美元，税后销售额总计为 636.51 美元（税前销售额为48.51 美元）。但是，0.121275 美元 * 400 美元的销售税为 48.52 美元。

This was one way, albeit contrived, to force a penny's difference.

这是一种方法，尽管是人为的，可以迫使一分钱的差异。

I would note that there are payroll tax forms from the IRS where they do not care if an error is below a certain amount (if memory serves, $0.50).

我会注意到，美国国税局有一些工资税表，他们不在乎错误是否低于一定数额（如果没记错的话，0.50 美元）。

Your big question is: does anybody care if certain reports are off by a penny?If the your specs say: yes, be accurate to the penny, then you should go through the effort to convert to DECIMAL.

你的大问题是：有人关心某些报告是否差一分钱吗？如果您的规格说：是的，精确到一分钱，那么您应该努力转换为 DECIMAL。

I have worked at a bank where a one-penny error was reported as a software defect. I tried (in vain) to cite the software specifications, which did not require this degree of precision for this application. (It was performing many chained multiplications.) I also pointed to the user acceptance test. (The software was verified and accepted.)

我曾在一家银行工作，那里报告了一个一分钱的错误作为软件缺陷。我试图（徒劳地）引用软件规范，该规范不需要此应用程序的这种精度。（它正在执行许多链式乘法。）我还指出了用户验收测试。（该软件已通过验证并接受。）

Alas, sometimes you just have to make the conversion. But I would encourage you to A) make sure that it's important to someone and then B) write tests to show that your reports are accurate to the degree specified.

唉，有时你只需要进行转换。但我鼓励您 A) 确保它对某人很重要，然后 B) 编写测试以证明您的报告准确到指定的程度。

回答by Steve Wellens

"are there any issue we should expect from only storing and retreiving a money amount in a DOUBLE column ?"

“我们应该期待仅在 DOUBLE 列中存储和检索金额有什么问题吗？”

It sounds like no rounding errors can be produced in your scenario and if there were, they would be truncated by the conversion to BigDecimal.

听起来在您的场景中不会产生舍入错误，如果有，它们将被转换为 BigDecimal 截断。

So I would say no.

所以我会说不。

However, there is no guarantee that some change in the future will not introduce a problem.

但是，不能保证将来的某些更改不会带来问题。