Why Are MySQL Floating-Point Comparisons Inaccurate?

MySQL Floating Point Comparison Imperfections

While introducing floating point columns to a MySQL database schema proves useful, comparisons involving floating point values can occasionally yield unreliable outcomes.

To demonstrate:

Consider a table with a points column containing the following values:

1 - 50.12
2 - 34.57
3 - 12.75
4 - ...(all other values are less than 12.00)

Executing the query:

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

unexpectedly returns "3."

It's a known fact that MySQL comparisons of floating point values are prone to inconsistencies, and that the decimal data type is a better choice for such comparisons.

Can You Continue Using the Float Type?

While it's generally recommended to use the DECIMAL data type for precise floating point comparisons, you can still proceed with the float type if you're aware of its potential pitfalls. One such pitfall is the potential for slight precision loss during calculations.

For instance, let's consider the following table:

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);

Running the query:

SELECT SUM(num) FROM a;

yields the result:

159.94000005722

Note the unexpected "0.00000005722." Such rounding errors can lead to discrepancies in comparisons.

To mitigate such issues, you can convert the float columns to a DECIMAL data type with a specified precision and scale. For example:

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

SELECT SUM(num) FROM a;

The result now becomes:

159.94

By using the DECIMAL data type, you can ensure accurate comparisons and avoid the precision issues associated with floating point values in MySQL.

The above is the detailed content of Why Are MySQL Floating-Point Comparisons Inaccurate?. For more information, please follow other related articles on the PHP Chinese website!