英文:
Why Mysql does not find records that exists. It is about Lat&Lng database search
问题
Mysql无法找到已存在的记录可能的原因是什么。这与Lat&Lng数据库搜索有关。我有这个简单的查询：
select id from addresses where lat = "42.5632172" and lng = "27.5266380"
这个查询返回0个结果，但很明显记录是存在的。以下是我的数据库结构和记录：
提前感谢您的时间！
英文:
what could possible be the reason for Mysql does not find records that exists. It is about Lat&Lng database search. I have this simple query:
select id from addresses where lat = "42.5632172" and lng = "27.5266380"
which returns 0 results but its obvious that record exists.
Here is my database structure and records:
Thanks in advance for your time!
答案1
得分: 2
由于您的lat和lng列的数据类型是float，这不是一个精确的数据类型，因此在这种数据类型上进行比较通常会导致意外的结果。最好在比较这些float列的值时使用一些容差。
SELECT id
FROM addresses
WHERE ABS(lat - 42.5632172) <= 0.0000001
AND ABS(lng - 27.5266380) <= 0.0000001
但如果您能将列的数据类型更改为精确数字数据类型，例如DECIMAL，那将更好。
查看演示这里。
英文:
Since data type of your lat and lng columns is float which is not a precision data type, so comparing on this data type often lead to unexpected result. It's better to use some tolerance when comparing values of these float columns.
SELECT id
FROM addresses
WHERE ABS(lat - 42.5632172) <= 0.0000001
AND ABS(lng - 27.5266380) <= 0.0000001
But it's much better if you could change data type of your columns to precision number data type, such as DECIMAL.
See demo here.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论