大于和小于MySql查询失败，带有十进制字段 [英] Greater Than and Less than MySql query failing with decimal field

问题描述

我有一个包含3列的数据库:

I have a database which has 3 columns:

user_id | lat      | lon
1       |-1.403976 | 53.428692
2       |-1.353276 | 55.224692
etc etc       

lat和lon都设置为十进制字段.我正在运行与此类似的查询，但它不是基于大于和小于给定的经/纬度数字进行过滤:

Both lat and lon are set as decimal fields. I'm running a query similar to this but it isn't filtering based on being greater than and less than the given lat/lon numbers:

SELECT * FROM `table` WHERE `lat` < '-1.399999' AND 'lat' > '-1.300000' 
AND 'lon' < '55.555555' AND > '53.000000'

此查询仅返回表中的每一行，我不知道为什么?与将字段设置为小数点有关吗?

This query just returns every row in the table and I don't know why? Is it something to do with the fields being set as decimals?

我希望有人能提供帮助-如果您知道的话，我可能会知道一个简单的答案.

I hope someone can help - i know its probably a simple answer if you know it.

根据评论-这是创建表:

As per comment - here's the create table:

CREATE TABLE IF NOT EXISTS `members` (
`user_id` int(10) NOT NULL AUTO_INCREMENT,
`lat` decimal(8,6) NOT NULL,
`lon` decimal(8,6) NOT NULL,
UNIQUE KEY `user_id` (`user_id`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=136 ;

推荐答案

问题是您用单引号将列名包装起来，迫使十进制值与字符串文字进行比较.列名和表名都是标识符，而不是字符串文字，因此不应用单引号引起来.

the problem is you are wrapping column names with single quotes forcing decimal values to compare against string literals. Column names as well as tables name are identifiers not string literals so they shouldn't be wrap with single quotes.

AND `lat` > '-1.300000' 
AND `lon` BETWEEN '55.555555' AND '53.000000' -- use between here

这篇关于大于和小于MySql查询失败，带有十进制字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！