Why Doesn\'t My MySQL Query Find My Float Value?

Troubleshooting MySQL FLOAT Selection Issue

When attempting to retrieve a row from a MySQL table based on a float value, a common issue arises when the matching value returns zero rows. This problem can manifest when the floats in the table and the query have different precision.

For instance, consider the following query:

SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31';

Even though the table contains a row with ident='ident23' and price='101.31', the query might not match it due to different precision. To address this, you can cast the price column to a DECIMAL type:

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

This will ensure that both values are compared with the same precision, allowing the query to retrieve the correct row.

Alternatively, you can consider altering the price column to a DECIMAL type permanently. DECIMAL is generally the preferred data type for handling monetary values due to its higher precision compared to FLOAT.

The above is the detailed content of Why Doesn\'t My MySQL Query Find My Float Value?. For more information, please follow other related articles on the PHP Chinese website!