Selecting a float in MySQL
Solution 1
Casting to a decimal worked for me:
SELECT * FROM table WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL);
However, you may want to consider just making the price
column a DECIMAL in the first place. DECIMAL is generally considered to be the best type to use when dealing with monetary values.
Solution 2
It doesn't work because a float is inherently imprecise. The actual value is probably something like '101.3100000000001' You could use ROUND() on it first to round it to 2 places, or better yet use a DECIMAL type instead of a float.
Solution 3
Don't ever use floats for money.
Solution 4
Today, I also came across the same situation and get resolved just by using FORMAT function of MySQL, It will return the results that exactly match your WHERE clause.
SELECT * FROM yourtable WHERE FORMAT(col,2) = FORMAT(value,2)
Explanation: FORMAT('col name', precision of floating point number)
Solution 5
Try this: SELECT * FROM table WHERE price like 101.31;
Meep3D
PHP Developer that's actually pretty decent at graphic design + CSS!
Updated on September 27, 2020Comments
-
Meep3D over 3 years
I am trying to do a
SELECT
match on a table based upon an identifier and a price, such as:SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31';
The above returns zero rows, while if you remove the
price='101.31'
bit it returns the correct row.Doing a...
SELECT * FROM `table`;
Returns the same row as above and quite clearly states that
price='101.31'
. Yet select fails to match it. Changing=
to<=
makes it work - but this is not exactly a solution.Is there a way of casting the MySQL float to 2 digits before the operation is performed on it, thus making the above
SELECT
work (or some other solution)?Thanks!