MySQL浮点比较问题 [英] MySQL floating point comparison issues

问题描述

我在 MySQL 数据库架构中引入浮点列时遇到了一个问题，即对浮点值的比较并不总是返回正确的结果.

I ran into an issue by introducing floating point columns in the MySQL database schema that the comparisons on floating point values don't return the correct results always.

1 - 50.12
2 - 34.57
3 - 12.75
4 - ...(12:00以内休息)

1 - 50.12
2 - 34.57
3 - 12.75
4 - ...(rest all less than 12.00)

SELECT COUNT(*) FROM `users` WHERE `points` > "12.75"

这会返回3".

我读过 MySQL 中浮点值的比较是一个坏主意，十进制类型是更好的选择.

I have read that the comparisons of floating point values in MySQL is a bad idea and decimal type is the better option.

我是否有希望继续使用浮点类型并使比较正常工作?

Do I have any hope of moving ahead with the float type and get the comparisons to work correctly?

推荐答案

你注意到下面的问题了吗?

Do you notice the problem below?

CREATE TABLE a (num float);

INSERT INTO a VALUES (50.12);
INSERT INTO a VALUES (34.57);
INSERT INTO a VALUES (12.75);
INSERT INTO a VALUES (11.22);
INSERT INTO a VALUES (10.46);
INSERT INTO a VALUES (9.35);
INSERT INTO a VALUES (8.55);
INSERT INTO a VALUES (7.23);
INSERT INTO a VALUES (6.53);
INSERT INTO a VALUES (5.15);
INSERT INTO a VALUES (4.01);

SELECT SUM(num) FROM a;
+-----------------+
| SUM(num)        |
+-----------------+
| 159.94000005722 | 
+-----------------+

在其中一些行之间有一个额外的 0.00000005722 分布.因此，与它们初始化时使用的值相比，其中一些值将返回 false.

There's an extra 0.00000005722 spread between some of those rows. Therefore some of those values will return false when compared with the value they were initialized with.

为避免浮点运算和比较出现问题，您应该使用 DECIMAL 数据类型:

To avoid problems with floating-point arithmetic and comparisons, you should use the DECIMAL data type:

ALTER TABLE a MODIFY num DECIMAL(6,2);

SELECT SUM(num) FROM a;
+----------+
| SUM(num) |
+----------+
|   159.94 | 
+----------+
1 row in set (0.00 sec)

这篇关于MySQL浮点比较问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！