MySQL“大于”条件有时会返回等值的行 [英] MySQL "greater than" condition sometimes returns row with equal value

问题描述

这是我的表：

pre> id |评级
1 | 1317.17
2 | 1280.59
3 | 995.12
4 | 973.88


现在，我试图找到评级列大于某个值。如果我尝试以下查询：

pre $ SELECT * FROM（`users`）WHERE`rating`> '995.12'


正确地返回 2 。

但是，如果我尝试

  SELECT * FROM（`users `）在哪里`评级`> '973.88'
  

它返回 4 ！所以就好像它认为表中的973.88大于973.88，但与995.12不一样。无论我是从PHP脚本还是在phpMyAdmin中运行查询，都会发生这种情况。

有什么想法？

解决方案

决定和结果

这是因为您决定使用浮点数据类型而导致的后果。浮动不准确。这意味着：是的，你可以导致> a = true

例如，第四行：

 
 mysql> SELECT * FROM t WHERE id = 4; 
 + ------ + -------- + 
 | id |评级| 
 + ------ + -------- + 
 | 4 | 973.88 | 
 + ------ + -------- + 
 1行（0.00秒）
 

我已经发布了数据类型，它是 FLOAT 。这里我们是：

 
 mysql> SELECT rating> 973.88 FROM t WHERE id = 4; 
 + --------------- + 
 |评级> 973.88 | 
 + --------------- + 
 | 1 | 
 + --------------- + 
 1行（0.00秒）
 

糟糕！

屏幕背后

为什么呢？要理解为什么这样，你应该意识到如何表示浮点数据类型。长篇故事是 - 但我会注意，不可能用2来精确地表示它。

结果是：即使数字在十进制小数上是精确的，仍然可能无法精确地表示它 - 因此，它将被近似存储。这就是它是如何工作的，事实上，这是打算 - 因为浮动结构本身。

做什么

固定精确度

那么，首先，你应该问自己：你真的需要浮动吗？ ？注意：我说：浮动。因为 - 也有固定的点数。它们将用 fixed 精度表示数字。说起来很简单：使用定点数据类型，您可能确定您将存储您在屏幕上看到的内容。所以如果它是 973.88 - 那么它是 973.88 而不是 973.8800000439234 。移动到交易：

 
 mysql> ALTER TABLE t CHANGE评级评级DECIMAL（8,2）; 
 Query OK，4 rows affected，4 warnings（0.47 sec）
 Records：4 Duplicates：0 Warnings：4 
 

和

 
 mysql> SELECT rating> 973.88从t WHERE id = 4; 
 + --------------- + 
 |评级> 973.88 | 
 + --------------- + 
 | 0 | 
 + --------------- + 
 1行（0.00秒）
 

TADA！发生了魔法。您的电话号码现在以固定的精确度存储，因此这样的比较失败了。

使用 float 可能是，当用户使用浮动功能时会有用例（但是，如果是DBMS，则很难我甚至记得一个这样的用例 - 如果只是不是大量计算的情况下，可能会导致性能影响，请参阅下面的说明）。那么还有办法让它工作。你应该决定什么精度适用于你。那就是：你会从哪个角度将数字视为平等。

您只存储两位有效数字，所以我认为 1E-5 的精度会超过足够。然后，您的查询将如下所示：

 
 mysql> set @ eps = 1E-5; 
 Query OK，0 rows affected（0.00 sec）
 

并用于：

  SELECT * FROM t WHERE rating> 973.88+@eps 
  

会导致

 
 + ------ + --------- + 
 | id |评级| 
 + ------ + --------- + 
 | 1 | 1317.17 | 
 | 2 | | 1280.59 | 
 | 3 | 995.12 | 
 + ------ + --------- + 
 

哪个更好为了实现这一点，你需要再次看到封面。我已经简要地概述了 float 数据类型是什么以及它为什么不精确。但是， fixed 数据类型也有它的弱点。可能不是我们应该在DBMS环境中担心的事情，但是我会提到它：一般来说， fixed 数据类型，会造成性能影响。这将取决于您将在数据库管理系统中进行多少计算。

在MySQL中， fixed 点数据类型（例如 DECIMAL ）被实现为< BCD字符串（所以长话短说 - 再次，这里的维基链接）。这意味着与 float 相比，它会导致性能问题。但是如果你不会经常在DBMS中进行计算，那么这种影响就不会那么明显 - 我已经提到过，因为浮点型和定点型都有它们自己的问题。

$ b

结论

DBMS和所有其他电脑一样，并不完美。只是使用一些内部的东西来完成这项工作。这意味着：在某些情况下，你必须意识到这些内部事物是如何工作的，以了解为什么你会得到一些奇怪的结果。特别是浮点数不精确。是的，互联网上有很多这样的答案，但我会重复。他们是不是精确的。你应该不是关于浮动的依赖精度。而且 - 几乎所有的DBMS都有定点数据类型。而且 - 在像你这样的情况下，你应该使用它们。他们会做同样的工作，但是对他们来说，你会确定精确度。然而，你可能想要使用浮点数 - 如果你要在你的DBMS中进行太多的计算。但另一方面，这是关于 - 你为什么要这样做？为什么不使用应用程序来产生这些计算（因此，避免使用定点数据类型的性能影响和浮点数的问题 - 因为使用固定点的平均计算量是可以的）

I'm running into a baffling issue with a basic MySQL query.

This is my table:

id | rating
1  | 1317.17
2  | 1280.59
3  | 995.12
4  | 973.88

Now, I'm attempting to find all rows where the rating column is larger than a certain value. If I try the following query:

SELECT * FROM (`users`) WHERE `rating` > '995.12'

It correctly returns 2.

But, if I try

SELECT * FROM (`users`) WHERE `rating` > '973.88'

it returns 4! So it's as if it thinks the 973.88 in the table is greater than 973.88, but it doesn't make the same mistake with 995.12. This happens regardless of whether I run the query from a PHP script or in phpMyAdmin.

Any ideas?

解决方案

Decisions and consequences

This is the consequences that you've got because you decided to use floating-point data type. Floats are not precise. And that means: yes, you can result in a>a = true

For instance, your fourth row:

mysql> SELECT *  FROM t WHERE id=4;
+------+--------+
| id   | rating |
+------+--------+
|    4 | 973.88 |
+------+--------+
1 row in set (0.00 sec)

I've left data type as you've posted, it's FLOAT. Here we are:

mysql> SELECT rating>973.88 FROM t WHERE id=4;
+---------------+
| rating>973.88 |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

Oops!

Behind the screen

Why? To understand why it is so, you should realize how floating-point data type is represented. Long story is here. But - I'll take a brief overview.

Here how it is represented: where:

• s is the sign
• b is the base. It's meaning is same as radix
• e is the exponent.

That means we can represent one number in different ways - and that depends of which base we'll chose. Most common is b=2. But not all real numbers can be represented exactly with this base, even if in decimal base they look "good". Famous example is 0.1 - which can not be represented in b=2 precisely - so it is stored approximately. Again, long story you can see here - but I'll just note, that it's impossible to represent it precisely with base 2.

The result is: even if number is precise in decimal radix, it still may be impossible to represent it precisely - and, therefore, it will be stored approximately. That's how it works and, in fact, this is intended - because of structure of floats itself.

What to do

Fixed precision

Well, first, you should ask yourself: do you really need float? Attention: I said: float. Because - there are also fixed point numbers. They will represent number with fixed precision. To say it easy: with fixed-point data type you may be sure that you'll store exactly what you see on the screen. So if it's 973.88 - then it's 973.88 and not 973.8800000439234. Moving to the deal:

mysql> ALTER TABLE t CHANGE rating rating DECIMAL(8,2);
Query OK, 4 rows affected, 4 warnings (0.47 sec)
Records: 4  Duplicates: 0  Warnings: 4

and ..

mysql> SELECT rating>973.88 FROM t WHERE id=4;
+---------------+
| rating>973.88 |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

TADA! Magic happens. Your number is now stored with fixed precision, thus, such comparison failed.

Using float

Then, may be there are use-cases when you're stuck with floats (however, in case of DBMS it's hard for me to remember even one such use-case - if only not the case with large amount of calculations, which may cause performance impact, see description below). Then there's still a way to make it work. You should decide what precision is applicable for you. That is: from which point will you treat numbers as equals.

You're storing only two significant digits, so I assume that precision of 1E-5 would be more than enough. Then, your query will look like:

mysql> set @eps=1E-5;
Query OK, 0 rows affected (0.00 sec)

and use it with:

SELECT * FROM t WHERE rating>973.88+@eps

which will result in

+------+---------+
| id   | rating  |
+------+---------+
|    1 | 1317.17 |
|    2 | 1280.59 |
|    3 |  995.12 |
+------+---------+

Which is better?

To realize this, you'll need to look under cover once again. I've given a brief overview of what float data type is and why it isn't precise. However, fixed data type also has it's weakness. May be it's not the thing of which we should worry in context of DBMS, but I'll mention it: fixed data type, in general, will cause performance impact. And it will depend of how much calculations will you do in your DBMS.

In MySQL, fixed-point data types (such as DECIMAL) are implemented as BCD strings (so to make long story short - again, here's wiki link). That means in comparison to float it will cause performance issues. But if you're not going to do calculations in DBMS too often, then that impact won't be even noticeable - I've mentioned it because both types, float- and fixed-point have their own issues.

Conclusion

DBMS, like all the other computer stuff, isn't perfect. It's just using some internal things to do the work. That means: in some cases you'll have to realize how that internal things work to understand why did you got some odd result.

In particular, floats are not precise. Yes, there are tons of answers like this in the Internet, but I'll repeat. They are not precise. You should not rely on precision when it's about floats. And - in almost all DBMS there are fixed-point data types. And - in cases like yours you should use them. They will do just same work, but with them you'll be sure about selected precision.

However, you may want to use floats - if you're going to make too much calculations in your DBMS. But, on the other hand, that is about - why are you going to do that? Why do not use application to produce those calculations (and, therefore, avoid both performance impact of using fixed-point data-types and presicion problems with floats - because using fixed-point with average amount of calculations is ok)

这篇关于MySQL“大于”条件有时会返回等值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！