Why does MySQL return unexpected results when comparing floating-point numbers?

MySQL Floating Point Comparison Anomalies

Floating-point numbers are generally notorious for causing unexpected results in comparisons due to their imprecise nature. MySQL is no exception to this behavior.

Imagine this scenario: a MySQL table with a column named "points" that stores floating-point values. When executing a query such as:

SELECT COUNT(*) FROM `users` WHERE `points` > "12.75"

you might expect it to return 3, as there are four values greater than 12.75. However, MySQL might only return 2.

This behavior is a consequence of the way floating-point arithmetic is implemented in computers. Despite storing values as "12.75" or "50.12" in our code, these values are often stored internally as approximations.

Internals of Floating-Point Arithmetic Mistake

To illustrate this, let's take the simple sum of some floating-point numbers:

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;

This query might return a result like "159.94000005722" instead of "159.94". That extra "0.00000005722" is the result of rounding errors internal to floating-point arithmetic.

Fixing the Issue with DECIMAL Type

To avoid such inaccuracies, it's recommended to use the DECIMAL datatype. DECIMAL represents values as strings with a fixed number of digits, rather than relying on floating-point approximations.

ALTER TABLE a MODIFY num DECIMAL(6,2);

SELECT SUM(num) FROM a;

With this change, the SUM query will return the expected result of "159.94".

Conclusion

While floating-point types can be convenient for some applications, their comparison behaviors can be unreliable in MySQL. For precise comparisons and calculations, it's highly recommended to use the DECIMAL datatype instead.

The above is the detailed content of Why does MySQL return unexpected results when comparing floating-point numbers?. For more information, please follow other related articles on the PHP Chinese website!