用于存储在字符串中的小数的 MySQL Round() 函数

Question

MySQL版本是5.66.22

当我对存储在 varchar 中的十进制数字使用 ROUND 函数时，我发现所有 .5 数字都有奇怪的行为

Select round(0.5)
1

Select round('0.5')
0

Select round('26.5' + 0.00)
26

但是

Select round(1.5)
2

Select round('1.5')
2

Select round(0.55, 1)
0.6

Select round('0.55', 1)
0.6

我检查了 Oracle DB (12c) 中的 ROUND 函数，它按预期工作

Select round('0.5') from dual 
1

Select round(0.5) from dual 
1

有人知道怎么解释吗？

所描述的 mysql round() 函数行为导致应用程序中出现“舍入”问题。为了解决我使用的问题:

Select round (CAST('0.5' AS DECIMAL(10,2)))
1

我知道在 varchar 中存储数字是糟糕的设计，但是这个应用程序是很久以前写的，现在没有人想重构代码

Answer 1

很有趣。该行为可以解释如下:

1) MySQL 在数字上下文 (ref) 中使用时将字符串转换为浮点值:

CREATE TABLE test AS (
    SELECT 0.5, '0.5' * 1 AS str_to_numeric
);

DESCRIBE test;

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| 0.5            | decimal(2,1) | NO   |     | 0.0     |       |
| str_to_numeric | double       | NO   |     | 0       |       |
+----------------+--------------+------+-----+---------+-------+

2) 如手册中所述:

Rounding Behavior

The ROUND() function rounds differently depending on whether its argument is exact or approximate:

• For exact-value numbers, ROUND() uses the “round half up” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

• For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the “round to nearest even” rule: A value with any fractional part is rounded to the nearest even integer.

下面是一些说明 ROUND 函数最终行为的测试:

CREATE TABLE test(
   fix DECIMAL(10,2),
   arb DOUBLE
);

INSERT INTO test(fix, arb) VALUES
    (0.5, 0.5),
    (1.5, 1.5),
    (2.5, 2.5),
    (3.5, 3.5);

SELECT fix, ROUND(fix) fix_roundex, arb, ROUND(arb) arb_rounded
FROM test

+------+-------------+------+-------------+
| fix  | fix_roundex | arb  | arb_rounded |
+------+-------------+------+-------------+
| 0.50 |           1 |  0.5 |           0 |
| 1.50 |           2 |  1.5 |           2 |
| 2.50 |           3 |  2.5 |           2 |
| 3.50 |           4 |  3.5 |           4 |
+------+-------------+------+-------------+

您的解决方案(将数字字符串显式转换为 DECIMAL)是正确的。