Check for equality on a MySQL Float field
floating-point, mysql
Solution
Usually with these types of questions it's good to provide a small example to replicate your results.
Usually testing for exact float values is a bad idea since floating point precision isn't an exact science. It's much better to use some tolerance.
create table foo1 (col1 float);
insert into foo1 values (2.18);
select * from foo1 where abs(col1-2.18) <= 1e-6
Problem
I have a Joomla system and I'm trying to change the search so that it correctly finds floating point values in the database. So, I have a query that is built at runtime that looks something like this: ``` select 'column1' from 'some_table' where 'some_float_field' <=> '2.18' ``` This doesn't work, it never matches anything, even though I see records in the db with this value there. So I tried to just do this instead: ``` select 'column1' from 'some_table' where 'some_float_field' <=> 2.18 ``` No luck, so then I tried casting to a decimal (float doesn't work for some reason), so I tried this: ``` select 'column1' from 'some_table' where 'some_float_field' <=> CAST('2.18' AS DECIMAL(20, 2)) ``` No dice... Keep in mind that >= or <= returns the proper results, just <=> gives me issues. How do I get equality to work here?