mysql 大浮点_MySQL浮点比较问题

你注意到下面的问题吗？

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 |

+-----------------+

这些行之间有一个额外的0.00000005722差距.因此,与初始化的值相比,这些值中的某些值将返回false.

为了避免浮点算术和比较的问题,您应该使用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)