MySQL 中的 DOUBLE 与 DECIMAL

Question

好的，所以我知道有大量文章指出我不应该使用 DOUBLE 在 MySQL 数据库上存储资金，否则我最终会遇到棘手的精度错误。重点是我不是在设计一个新的数据库，而是要求我找到优化现有系统的方法。新版本包含 783 个 DOUBLE 类型列，其中大部分用于存储金钱或计算金额的公式。

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

• 我们不对数据库执行任何计算。所有操作均在 Java 中使用 BigDecimal 完成，MySQL 仅用作结果的普通存储。
• DOUBLE 提供的 15 位精度已经足够了，因为我们主要存储 2 位小数的金额，偶尔也存储 8 位小数的小数字作为公式参数。
• 我们在生产中拥有 6 年的记录，没有因 MySQL 端精度损失而出现任何已知错误问题。

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

SELECT columnName * 1.000000000000000 FROM tableName; 的

操作来显示精度损失，但我无法找到一种方法将其变成小数点后第二位的错误。我在互联网上发现的大多数实际问题都是 2005 年及更早版本的论坛条目，我无法在 5.0.51 MySQL 服务器上重现它们中的任何一个。

因此，只要我们不执行任何 SQL 算术操作（我们不打算这样做），仅在 DOUBLE 列中存储和检索金额是否会出现任何问题？

Answer 1

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

Answer 2

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)

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

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

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

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)

预期的结果是空集。

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

关于 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)

Answer 3

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

Answer 4

“仅在 DOUBLE 列中存储和检索金额是否会出现任何问题？”

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

所以我会说不。

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

Answer 5

从你的评论来看，

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

使用评论中的示例，我可能会预见到 400 件商品的销售额为 1.47 美元的情况。税前销售额为 588.00 美元，税后销售额为 636.51 美元（税费为 48.51 美元）。然而，0.121275 美元* 400 的销售税将是48.52 美元。

这是一种强制实现一便士差价的方法，尽管是人为的。

我要指出的是，美国国税局 (IRS) 提供工资税表格，他们并不关心错误是否低于一定金额（如果没记错的话，0.50 美元）。

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

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

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