select * from Table where data=2.6

After running the above query, db returned an empty result set. But Seen from table, there are many rows matching data=2.6 criterion.

Here the type of 'data' column is FLOAT. If changing the value to an integer like 'where data=2', the matched result sets will be returned.

Please let me know if anyone see the error on the query? Many thanks!

Try using LIKE


You should not compare explicit values with float values. Due to rounding and precision issues the value of a float can only be of a limited subset of all real numbers. It is stored as the number which is closest to the input value. Therefore mysql does not store 0.6 in a float field but a value very close to it.
Try this:

create table f(f float);
insert into f values (0.6);
select * from f where f - 0.6 > 0.0000001;
-- Empty set (0.00 sec)
select * from f where f - 0.6 > 0.00000001;
-- f = 0.6
select * from f where f = 0.6;
-- Empty set (0.00 sec)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.