为什么这个sql查询不会返回比较浮点数的任何结果？ [英] Why doesn't this sql query return any results comparing floating point numbers?

问题描述

我有一个mysql表中的这个：

id 和 bolag_id 是 int 。 lat 和 lngitude 是 double 。

如果我使用 lngitude 列，则不会返回任何结果：

lngitude 查询： SELECT * FROM location_forslag WHERE lngitude = 13.8461208

但是，如果我使用 lat 列，它

lat 查询： SELECT * FROM location_forslag WHERE lat = 58.3902782

lngitude 列？

解决方案

通常不是一个比较好的主意浮点数与 = 等于运算符。

• 正确比较两个圆形浮点数运用==运算符




• 处理浮点数的准确性问题

对于您的应用程序，您需要考虑您希望答案的接近程度。

1度约112公里，0.00001度约为1.1米（赤道）。你是否真的希望你的应用程序说不相等，如果两点不同，那么0.00000001度= 1mm？

  set @EPSLION = 0.00001 / *赤道1.1米* / 
 
 SELECT * FROM location_forslag 
 WHERE`lngitude`> = 13.8461208  -  @ EPSILON 
 AND`lngitude`< = 13.8461208 + @EPSILON 
  

这将返回点数，其中lngitude在 @epsilon 所需值的度数。
您应该选择适合您的应用程序的epsilon值。

I have this in a mysql table:

id and bolag_id are int. lat and lngitude are double.

If I use the the lngitude column, no results are returned:

lngitude Query: SELECT * FROM location_forslag WHERElngitude= 13.8461208

However, if I use the lat column, it does return results:

lat Query: SELECT * FROM location_forslag WHERElat= 58.3902782

What is the problem with the lngitude column?

解决方案

It is not generally a good idea to compare floating point numbers with = equals operator.

• Is it correct to compare two rounded floating point numbers using the == operator?

• Dealing with accuracy problems in floating-point numbers

For your application, you need to consider how close you want the answer to be.

1 degree is about 112km, and 0.00001 degrees is about 1.1 metres (at the equator). Do you really want your application to say "not equal" if two points are different by 0.00000001 degrees = 1mm?

set @EPSLION = 0.00001  /* 1.1 metres at equator */

SELECT * FROM location_forslag 
WHERE `lngitude` >= 13.8461208 -@EPSILON 
AND `lngitude` <= 13.8461208 + @EPSILON

This will return points where lngitude is within @epsilon degrees of the desired value. You should choose a value for epsilon which is appropriate to your application.

这篇关于为什么这个sql查询不会返回比较浮点数的任何结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！