Querying float fields in mysql

Common mistake when querying float fields is to use the equal sign eg.

select * from table where amt = 19.99

This never works because the precision level is never going to be exact. So a better way is :

select * from table where abs(amt) – 19.99 < 0.001

You can change the precision level to something higher than 0.001 if you are doing scientific calculations but generally for money amounts, this precision is enough.

Of course, if a field is supposed to store a currency value then its best to make it as decimal instead of float since a decimal field will automatically truncate the value to 2 significant digits after the decimal.