MySQL 查询未显示正确的数据
大家好,我有一个 MySQL 查询,
SELECT * FROM `product` WHERE `price` BETWEEN '60' AND '999' ORDER BY `product` DESC LIMIT 12 OFFSET 0
这显然选择了 12 个价格在 60 到 999 之间的产品。这正确地显示了行。
但是,如果我像这样的查询增加限制,
SELECT * FROM `product` WHERE `price` BETWEEN '60' AND '1000' ORDER BY `product` DESC LIMIT 12 OFFSET 0
则不会显示任何行。有什么想法为什么会发生这种情况吗? “价格”字段是浮点型字段
Hay Everyone, i have a MySQL query
SELECT * FROM `product` WHERE `price` BETWEEN '60' AND '999' ORDER BY `product` DESC LIMIT 12 OFFSET 0
This obviously select 12 products where the price is between 60 and 999. This shows the rows correctly.
However if i increase the limit like this query
SELECT * FROM `product` WHERE `price` BETWEEN '60' AND '1000' ORDER BY `product` DESC LIMIT 12 OFFSET 0
No rows get shown. Any ideas why this happens? The 'price' field is float type field
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
删除 ' 因为字段类型是数字,而不是字符。
Remove the ' because the field types are numbers, not chars.
您将
'60'
和'1000'
视为字符。使用数字:如果您使用 varchar 字符串,则没有行可以匹配
Between
'60' 和 '1000'条件,因为
'在 varchar 中,1000'小于
'60'`。它与
'999'
一起使用,因为可能有几个价格介于'6'
和'9'
之间。You are treating
'60'
and'1000'
as characters. Use numbers:If you use varchar strings, there is no row that will match a
between
'60' and '1000'condition, since
'1000'is smaller than
'60'` in varchars.It was working with
'999'
because there are probably a couple of prices ranged between'6'
and'9'
.60、999 和 1000 被视为字符而不是 float 或 int,
它与 999 一起使用的原因是因为比较发生在 ascii 字符上。
对于 1000,您不会得到任何价格以 ASCII 开头在 6 到 1 之间的行,因为 6 的值高于 1。
The 60, 999 and 1000 are getting treated as chars instead of float or int,
The reason it works with 999 is because the comparision happens on ascii chars.
For 1000, you wont get any rows which will have price with ascii starting between 6 and 1 since 6 has higher value than 1.