MySQL MySQL浮点比较问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2567434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL floating point comparison issues
提问by Sharief Shaik
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.
我在 MySQL 数据库模式中引入浮点列时遇到了一个问题，即对浮点值的比较并不总是返回正确的结果。
1 - 50.12
2 - 34.57
3 - 12.75
4 - ...(rest all less than 12.00)
1 - 50.12
2 - 34.57
3 - 12.75
4 - ...（其余均小于 12.00）
SELECT COUNT(*) FROM `users` WHERE `points` > "12.75"
This returns me "3".
这将返回“3”。
I have read that the comparisons of floating point values in MySQL is a bad idea and decimal type is the better option.
我读过 MySQL 中浮点值的比较是一个坏主意，十进制类型是更好的选择。
Do I have any hope of moving ahead with the float type and get the comparisons to work correctly?
我是否有希望继续使用浮点类型并使比较正常工作？
回答by Daniel Vassallo
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 |
+-----------------+
There's an extra 0.00000005722spread between some of those rows. Therefore some of those values will return false when compared with the value they were initialized with.
其中0.00000005722一些行之间有一个额外的传播。因此，与它们初始化时使用的值相比，其中一些值将返回 false。
To avoid problems with floating-point arithmetic and comparisons, you should use the DECIMALdata type:
为避免浮点运算和比较出现问题，您应该使用DECIMAL数据类型：
ALTER TABLE a MODIFY num DECIMAL(6,2);
SELECT SUM(num) FROM a;
+----------+
| SUM(num) |
+----------+
| 159.94 |
+----------+
1 row in set (0.00 sec)
回答by intellidiot
I did face the similar issue once. Convert the 'float' field to 'decimal'. It'll definitely solve the problem.
我曾经遇到过类似的问题。将“float”字段转换为“decimal”。肯定能解决问题。
回答by ninja
I do this
我这样做
WHERE abs(value - 12.75)<0.001
but I agree, any language can compare float equality and if stored values equals exact numbers values you you inserted, there should not be any issue
但我同意，任何语言都可以比较浮点相等性，如果存储的值等于您插入的确切数字值，则应该没有任何问题
with only a couple of decimals and exact matching values, precision errors does not sounds like an obvious reason for such mismatches in MySQL
只有几个小数和精确匹配值，精度错误听起来并不是 MySQL 中这种不匹配的明显原因
回答by Frank Heikens
It's a floating point, so what's the problem? 3 could be the correct result, depends on what the database thinks about 12.75. Is it 12.75 or just a little more?
这是一个浮点数，那么有什么问题呢？3 可能是正确的结果，这取决于数据库对 12.75 的看法。是 12.75 还是多一点？
Use DECIMAL if you want exact numbers.
如果您想要确切的数字，请使用 DECIMAL。
回答by Andrey
There is a problems with comparison of floats for equality. This may give unpredicted results. This is due to internal implementation of floating point arithmetics.
比较浮点数是否相等存在问题。这可能会产生不可预测的结果。这是由于浮点运算的内部实现。
回答by dkretz
Comparing a number with a string?
将数字与字符串进行比较？
回答by Anil Dhandar
Use REAL instead of FLOAT or DECIMAL.
使用 REAL 而不是 FLOAT 或 DECIMAL。
