在FLOAT数据上使用SUM [英] Using SUM on FLOAT data

问题描述

一张桌子:
物品(编号，价格)

One table:
items(id, price)

有一行:
id:1，价格:565.8

Has one row:
id: 1, price: 565.8

SELECT price FROM items给出565.8
SELECT SUM(price) FROM items给出了565.799987792969而不是我期望的565.8.

SELECT price FROM items gives 565.8
SELECT SUM(price) FROM items gives 565.799987792969 instead of 565.8 which I'd expect.

565.799987792969的来源是什么?

推荐答案

我不确定您使用的是哪个版本，但这听起来像

I'm not sure what version you are using, but it sounds like this link describes what you are experiencing.

通过链接:

mysql> select * from aaa;
+----+------------+------+
| id | month_year | cost |
+----+------------+------+
|  1 | 2002-05-01 | 0.01 |
|  2 | 2002-06-01 | 1.65 |
|  3 | 2002-07-01 | 0.01 |
|  4 | 2002-01-01 | 0.01 |
+----+------------+------+

mysql> select id, sum(cost) from aaa group by id;
+----+---------------------+
| id | sum(cost)           |
+----+---------------------+
|  1 | 0.00999999977648258 |
|  2 |    1.64999997615814 |
|  3 | 0.00999999977648258 |
|  4 | 0.00999999977648258 |
+----+---------------------+

The SUM function changes 0.01 to 0.00999999977648258.

浮点数存储为近似值；如果要存储价格，最好使用存储精确值的十进制数据类型.

Floating point numbers are stored as approximate values; if you are storing a price, you are better off using a decimal datatype which stores precise values.

这篇关于在FLOAT数据上使用SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！