Why Does My MySQL SELECT Query Fail When Comparing Floats, But Works When I Remove the Comparison?

Resolving Float Comparison Issues in MySQL SELECT Queries

In MySQL SELECT queries, precision problems can arise when comparing float values. To address this issue, let's explore the following question:

Question:

Why does a SELECT query with a float equality comparison fail, while removing the comparison returns the correct row? How can we cast the float to 2 digits before the operation for a successful match?

Answer:

The problem stems from the inherent floating-point precision in MySQL. To resolve this, we can cast the float column to a decimal type, which ensures precision to a specific number of digits.

Solution:

The following query successfully matches the float value by casting it to a decimal:

SELECT * FROM `table` WHERE CAST(`price` AS DECIMAL) = CAST(101.31 AS DECIMAL);

Alternative Solution:

Alternatively, you may consider changing the price column to DECIMAL, which is more suited for handling monetary values and offers better precision control.

The above is the detailed content of Why Does My MySQL SELECT Query Fail When Comparing Floats, But Works When I Remove the Comparison?. For more information, please follow other related articles on the PHP Chinese website!