英文:
MySQL bug with rounding decimal values
问题
我想知道为什么MySQL在四舍五入小数点时，对两个不同行产生不同的结果，如我在这里的截图所示。
第一个行的 183.25 * 9.7 被错误地四舍五入，应该四舍五入为 1777.53，而第二个行的 186.55 * 9.7 正确地四舍五入为 1809.54。
我想知道为什么在同一个数据库中，不同行的四舍五入结果不同？
英文:
I wonder why MySQL produce different result when rounding decimal points for 2 different rows as shown in my screenshot here.
The first 183.25 * 9.7 row is rounded incorrectly which should be round to 1777.53 where as the 2nd row 186.55 * 9.7 is rounding correctly to 1809.54.
I wonder why is different result of rounding for different rows in a same database?
答案1
得分: 2
对于您的ROUND()列，MySQL正在按照您的要求进行操作，并提供了3位小数（ROUND()的第二个参数是3）。
然而，您的total_amount列没有进行四舍五入。您看到的四舍五入是由您用于显示结果的GUI工具执行的，似乎不使用MySQL半上舍入。
为了澄清一下，让我们看一下您的查询，它有5列：
SELECT
p.quantity,
p.weight,
p.unit_price,
ROUND(p.quantity * p.weight * p.unit_price, 3),
(p.quantity * p.weight * p.unit_price) as total_amount
FROM ...
正如您所看到的，第5列（您用total_amount作为别名）只是一个算术操作，没有使用ROUND()。您在网格上看到的四舍五入（1809.54）是由您的管理工具执行的，而不是MySQL。
您可以在MySQL命令行界面上验证实际行为：
MySQL [test]> SELECT (183.25 * 9.70);
+-----------------+
| (183.25 * 9.70) |
+-----------------+
| 1777.5250 |
+-----------------+
1 row in set (0.018 sec)
英文:
For your ROUND() column, MySQL is doing what you asked and giving you 3 decimal places (the second argument of ROUND() is 3).
However, your total_amount column is NOT rounded. The rounding that you see is being done by whatever GUI tool you are using to display the results, and it does not seem to use MySQL half-up rounding.
To clarify, lets take a look at your query, which has 5 columns:
SELECT
p.quantity,
p.weight,
p.unit_price,
ROUND(p.quantity * p.weight * p.unit_price, 3),
(p.quantity * p.weight * p.unit_price) as total_amount
FROM ...
As you can see, column 5 (which you aliased as total_amount) is just an arithmetic operation, there is no ROUND() around it. The rounding you see on your grid (1809.54) is being performed by your admin tool, not MySQL.
You can verify the actual behavior on the MySQL CLI:
MySQL [test]> SELECT (183.25 * 9.70);
+-----------------+
| (183.25 * 9.70) |
+-----------------+
| 1777.5250 |
+-----------------+
1 row in set (0.018 sec)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论