MySQL四舍五入

Question

我正在做一个发票模块，当我计算整笔金额时遇到了一些奇怪的情况，我很难把头绕在周围。

在mysql shell中执行此查询时，会得到不同的结果：

SELECT @amount := 1.005 AS decimalAmount, @rounded := ROUND(@amount) AS rounded, @diff := ROUND(@rounded - @amount, 2) AS roundOff, ROUND(@diff * 1e2) centsRounded;

第三次运行查询时，它会显示我正在寻找的结果，但由于某种原因，第一次和第二次没有这样做。

+---------------+---------+----------+--------------+
| decimalAmount | rounded | roundOff | centsRounded |
+---------------+---------+----------+--------------+
|         1.005 |       1 |    -0.00 |           -0 |
+---------------+---------+----------+--------------+
1 row in set (0.000 sec)

+---------------+---------+----------+--------------+
| decimalAmount | rounded | roundOff | centsRounded |
+---------------+---------+----------+--------------+
|         1.005 |       1 |    -0.00 |           -0 |
+---------------+---------+----------+--------------+
1 row in set (0.000 sec)

+---------------+---------+----------+--------------+
| decimalAmount | rounded | roundOff | centsRounded |
+---------------+---------+----------+--------------+
|         1.005 |       1 |    -0.01 |           -1 |
+---------------+---------+----------+--------------+
1 row in set (0.000 sec)

有人能解释一下为什么会这样吗？

Answer 1

有人能解释一下为什么会这样吗？

手册解释

涉及用户变量的表达式的计算顺序未定义。例如，不能保证选择@a，@a:=@a+1首先计算@a，然后执行赋值。

非常肯定您的查询中的评估是错误的。

如果您真的不需要MySQL用户变量，那么避免它们是很棘手的。

我更想重写这个查询。

SELECT
    @amount := 1.005 AS decimalAmount
  , @rounded := ROUND(@amount) AS rounded
  , @diff := ROUND(@rounded - @amount, 2) AS roundOff
  , ROUND(@diff * 1e2) centsRounded;

或多或少地类似于(有更多的重写选项)来避免使用MySQL的用户变量。

SELECT 
   record.amount
 , ROUND(record.amount) AS rounded
 , ROUND(ROUND(record.amount) - record.amount, 2) AS roundOff
 , ROUND(ROUND(record.amount) - record.amount, 2) * 1e2  AS centsRounded
FROM (
  SELECT 
   1.005 AS amount
) AS record

结果

| amount | rounded | roundOff | centsRounded |
| ------ | ------- | -------- | ------------ |
| 1.005  | 1       | -0.01    | -1           |

请参阅演示