Problem with comparison of float or double column in MySQL

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.