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

Can someone explain why this is happening?

manual解释

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

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

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           |

参见demo