giancy9 giancy9 - 25 days ago 13
MySQL Question

mysql compare float numbers ignored

I am trying to compare two floating numbers.
I have already seen a number of questions here on stackoverflow but I didn't find a solution yet.

$sql = "SELECT price FROM list
WHERE price != '".(float)$price."'";


Price is "6.30"
In DB I want to get all results except the one with price 6.30. But it ignores it completely.

I know it has something to do with floats. I cannot edit DB table, this is not an option unfortunately.

Is there any other way to do this by just using mysql?

Answer

It is very dangerous to compare floating point numbers, because values that look the same could be slightly different. One method is:

WHERE ABS(price - $price) < 0.001

The problem with this is that it cannot use an index. You can instead use:

WHERE price >= $price - 0.001 and price > $price + 0.001

Of course, 0.001 is an arbitrary measure of tolerance. You might want a smaller or larger value.

And, I should mention that decimal/numeric values are safe for comparison.