检查MySQL Float字段上的相等性 [英] Check for equality on a MySQL Float field

问题描述

我有一个Joomla系统，我试图改变搜索，以便正确地在数据库中找到浮点值。

所以，我有一个查询这是在运行时构建的，看起来像这样：

pre code $ select'column1'
'some_table'
where'some_float_field'< => '2.18'

这不起作用，即使我看到数据库中有这个值。

所以我试着做这个，而不是：

 从'some_table'中选择'column1'
 
其中'some_float_field'< => 2.18 
  

没有运气，所以我试着铸造成小数（float由于某种原因），所以我试过这个：

pre $ code $ select $'$'$'$'$'$'$'$' '< => CAST（'2.18'AS DECIMAL（20，2））

没有骰子...

请记住，> =或<=返回正确的结果，只是< =>给我提出的问题。

我怎样才能在这里工作？

解决方案

通常这些类型的问题很好提供一个小例子来复制你的结果。

通常测试精确浮点值是一个坏主意，因为浮点精度不是一个精确的科学。使用宽容会更好。

  create table foo1（col1 float）; 
 
插入foo1值（2.18）; 
 select * from foo1 abs（col1-2.18）<= 1e-6 
  

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?

解决方案

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

这篇关于检查MySQL Float字段上的相等性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！