MySql 5 problem with floats

moroc111
moroc111 used Ask the Experts™
on
I have a stange problem with Mysql 5, i have a big table with many float fields. I can see the fields in the tables filled with information, but when i query the table i get empty sets but not for all values.

example: i can see in the table(using ems sql manager)  that there are values 1.8 in a field called BGH,when i query
select * from table where BGH=1.8 i get an empty result set.

with some other values i get the correct results sets. Can anybody help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2005
Commented:
This is a problem inherent with most floating-point representations, in MySQL and elsewhere.  There are a few possible solutions:
a) Use DECIMAL instead of FLOAT
b) If all your values have the same precision, you can use INT and multiply/divide by the appropriate power of 10 when inserting/selecting
c) Don't check for equality, check for a small delta (e.g., SELECT * FROM table WHERE ABS(BGH - 1.8) < 0.1)

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial