MySQL轮询查询，结果错误 [英] MySQL round in query, wrong result

问题描述

我有一个关于我在MySQL服务器上运行的查询的问题（v5.5.50-0 + deb8u1）。

  SELECT 12  - （SELECT qty FROM Table WHERE id = 5213）AS amount 
  

is 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 Amount

so Amount value is 12 - 8,5500000000000007 = 3.4499999999999993

But if I run the query:

 SELECT qty FROM Table WHERE id = 5213

it 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轮询查询，结果错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！