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 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屋！