Problem with comparison of float or double column in MySQL
I had a problem in comparing the number with decimal point with a float column in MySql. After some investigation I came to know that, in most of the computer architecture, the value of floating point numbers is stored as approximation. So if you compare the 21.40 with the given column, it actually compare the value with 21.3999999999999986 !! So, if you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can’t use equality (=) comparisons of MySql.
Converting the float to double also won’t solve the problem. Then I tried to use ‘like’ of MySql to comparison and it worked for some case. But, in MySql if you use ‘like’, it converts (or cast) the values in string, it won’t match the 21.4 with 21.40 .
Then I converted the column type to ‘Decimal’ and that was worked for me. As Decimal type, in MySql, doesn’t use the floating point unit of CPU, the arithmetic calculation might take more time compare to the Float or Double. Here you have to compromise with the speed to get accuracy.
http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value. For example, if we agree that floating-point numbers should be regarded the same if they are same within a precision of one in ten thousand (0.0001), the comparison should be written to find differences larger than the tolerance value:
Conversely, to get rows where the numbers are the same, the test should find differences within the tolerance value:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a – b) <= 0.0001;
Thanks dear,
I had also use the float type but camparision with this filed was not working. then I convert that filed with decimal,now is working.