MySQL Query 似乎没有输出预期 [英] MySQL Query doesn't seem to be outputting expectations

问题描述

有人能帮我吗?除非我错过了令人眼花缭乱的显而易见的东西，否则我完全被难住了.

Can anyone help me here please. Unless I'm missing the blindingly obvious, i'm total stumped.

当我要求在价格列中所有小于或等于 6 的所有值以及在分钟列中大于或等于 500 的所有值时，此查询如何返回该行.

How is this query returning that line, when I'm asking for everything less or equal to 6 in the price column, and every greater or equal to 500 in the minutes column.

1. 分钟是一个 varchar
2. 价格是一个整数

价格是整数

CREATE TABLE `gg_Crates` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `price` int(11) NOT NULL,
 `minutes` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `sms` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `data` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `url` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `bb` int(5) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

推荐答案

你得到那一行是因为你在比较字符串."500" >="60" 为真，因为 ASCII 字符顺序.

You are getting that row because you are comparing strings. "500" >= "60" is true, because of ASCII characters order.

过滤数据时，您必须更改minutes 列的类型或解析值.例如.

You have to change the type of the minutes column or parse the value when filtering data. Eg.

SELECT *, CONVERT(minutes,UNSIGNED INTEGER) AS minutes_int
...
WHERE
...
AND `minutes_int` >= 600
...

As 也可以尝试直接将字符串值与整数值进行比较，例如.

As could also try comparing the string value to the integer value directly, eg.

AND `minutes` >= 600

通过删除逗号，但如果可能，我建议您考虑更改列格式，因为将分钟表示为 varchar(11) 是不正确的，并且还会使您占用大量无缘无故的空间.

by removing the commas, but I suggest you to think about changing the column format, if possible, since representing minutes as a varchar(11) is not correct and will also make you occupy lots of space without reason.

这篇关于MySQL Query 似乎没有输出预期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！