Why Does My MySQL SELECT Statement Return No Rows When Comparing a Float Value to a Constant?

Resolving Floating-Point Discrepancies in MySQL Selects

When executing a SELECT statement with a WHERE clause that compares a float value to a constant, users may encounter unexpected results. For instance, the following query returns no rows:

<code class="sql">SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31';</code>

However, removing the price comparison condition yields the correct row:

<code class="sql">SELECT * FROM `table`;</code>

This discrepancy can be attributed to the way MySQL handles floating-point precision. While the value retrieved from the database shows '101.31', it may differ slightly from the actual constant used in the WHERE clause due to floating-point arithmetic inaccuracies.

Solution: Casting to DECIMAL

To resolve this issue, one can cast the price column to the DECIMAL type in the WHERE clause:

<code class="sql">SELECT * FROM `table` WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL);</code>

This ensures that both the database value and the constant are represented as DECIMALs, eliminating any potential precision issues.

Alternative Solution: Changing Column Type

As an alternative, consider changing the price column's data type to DECIMAL directly. DECIMAL provides greater precision and scale when working with monetary values, eliminating the need for casting in SELECT queries.

The above is the detailed content of Why Does My MySQL SELECT Statement Return No Rows When Comparing a Float Value to a Constant?. For more information, please follow other related articles on the PHP Chinese website!