我有一个关于我在MySQL服务器上运行的查询的问题(v5.5.50-0 + deb8u1)。
SELECT 12 - (SELECT qty FROM Table WHERE id = 5213)AS amountis 12 - 8,5500000000000007 = 3.4499999999999993
但是如果我运行查询:
SELECT qty FROM Table WHERE id = 5213它返回8.55,在记录中,所以我预期第一个查询返回3.45。
表表中的qty列是一个双精度。 b $ b
如何可能?如何从查询获得正确的答案? 提前感谢
解决方案这是只是浮动数字的方式。
浮点数有时会导致混乱,因为它们是近似值,不会存储为精确值。在SQL语句中写为的浮点值可能与内部表示的值不同。
这种说法也适用于许多编程语言。一些数字甚至没有确切的表示。以下是 python手册中的内容
这个问题更容易在基础10中理解。考虑 fraction 1/3。您可以将其作为基数10分数:
0.3或更好,
0.33或更好,
0.333等。无论你愿意写多少个数字,结果将不会是正好1/3,但将是一个越来越好的近似1/3。
同样,无论你愿意使用多少基数2,十进制值0.1不能精确地表示为基数2 分数。在基数2中,1/10是无限重复部分
所以简单来说,一般是 float1 = float2 比较类型是一个坏主意,但每个人都会忘记它。
I have a question about a query that I'm running on a MySQL Server (v5.5.50-0+deb8u1).
SELECT 12 - (SELECT qty FROM Table WHERE id = 5213) AS Amountso Amount value is 12 - 8,5500000000000007 = 3.4499999999999993
But if I run the query:
SELECT qty FROM Table WHERE id = 5213it returns 8.55 that is the correct number written in the record, so I was expecting that the first querty returned 3.45.
The "qty" column in the table "Table" is a DOUBLE.
How is it possibile? How can I get the right answer from the query? thanks in advance
解决方案Well that's just the way floating numbers are.
Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally.
This statement holds true for many programming languages as well. Some numbers don't even have an exact representation. Here's something from the python manual
The problem is easier to understand at first in base 10. Consider the fraction 1/3. You can approximate that as a base 10 fraction:
0.3 or, better,
0.33 or, better,
0.333 and so on. No matter how many digits you’re willing to write down, the result will never be exactly 1/3, but will be an increasingly better approximation of 1/3.
In the same way, no matter how many base 2 digits you’re willing to use, the decimal value 0.1 cannot be represented exactly as a base 2 fraction. In base 2, 1/10 is the infinitely repeating fraction
So in short generally doing is float1 = float2 type of comparison is a bad idea but everyone keeps forgetting it.
更多推荐
MySQL轮询查询,结果错误
发布评论