从int列查询字符串？

I have a table:

我有一张桌子:

CREATE TABLE `ids` (
    id int(11) not null auto_increment,
    PRIMARY KEY (id)
);

It contains some IDs: 111, 112, 113, 114 etc.

它包含一些ID:111,112,113,114等。

I made a query:

我提出了一个问题:

SELECT * FROM `ids` WHERE id = '112abcdefg'

I expected nothing but I've got a result, a row with ID of 112. Seems that MySQL quietly converted my string to integer and then compared it against column values.

我没想到,但我得到了一个结果,一行ID为112.似乎MySQL悄悄地将我的字符串转换为整数,然后将其与列值进行比较。

How can I change the query so that querying the same string from id column will give no results as I expect? Is there a strict comparison modifier in MySQL?

如何更改查询以便从id列查询相同的字符串将不会产生我期望的结果? MySQL中是否有严格的比较修饰符?

4 个解决方案

#1

One option is to CAST the 112 to CHAR to get a proper match:

一种选择是将112转换为CHAR以获得正确的匹配:

WHERE CAST(id AS CHAR(12)) = '112abcdefg'

The 12 in CHAR is a guess; it should be large enough for your biggest id.

CHAR中的12是一个猜测;它应该足够大到你最大的身份。

That will probably kill any chance of optimization, so another option (though one I'm not 100% sure of) is to use a BINARY comparison. I've tried this with a few different values and it works:

这可能会杀死任何优化的机会,所以另一种选择(虽然我不是100%肯定)是使用BINARY比较。我尝试了几个不同的值,它的工作原理:

WHERE BINARY id = '112abcdefg'

#2

You are comparing a string, just put the number with no quotes:

你正在比较一个字符串,只是把数字加上没有引号:

SELECT * FROM `ids` WHERE id = 112

If you dont, it will convert the string '112abcdefg' to a number and say its 112

如果你不这样做,它会将字符串'112abcdefg'转换为数字并说出它的112

The response you are seeing is because you are trying to compare an integer column to a string value. In that case, MySQL will type-cast the string literal value to an integer, and when it does that it starts from the left of the string and as soon as it reaches a character that cannot be considered part of a number, it strips out everything from that point on. So trying to compare "256abcd" to an integer column will result in actually comparing the number 256.

您看到的响应是因为您尝试将整数列与字符串值进行比较。在这种情况下,MySQL会将字符串文字值类型转换为整数,当它执行该操作时,它从字符串的左边开始,一旦到达不能被视为数字一部分的字符,它就会消失从那一点开始的一切。因此,尝试将“256abcd”与整数列进行比较将导致实际比较数字256。

So your options (or at least a few of them) would be: Validate the input string in your application code and reject it if it's not an integer (see the ctype_digit function in PHP). Change the column type for the filename if you want to treat it as a string (e.g. a VARCHAR type). Cast the column value to a string:

因此,您的选项(或至少其中一些)将是:验证应用程序代码中的输入字符串,如果它不是整数,则拒绝它(请参阅PHP中的ctype_digit函数)。如果要将其视为字符串(例如VARCHAR类型),请更改文件名的列类型。将列值转换为字符串:

. . . WHERE CAST(Id AS CHAR) = '256aei'

Source

#3

you can use this :

你可以用这个:

SET sql_mode = STRICT_TRANS_TABLES;

this sets you sql mode to strict checking, and then try firing the query you mentioned.

这会将您的sql模式设置为严格检查,然后尝试触发您提到的查询。

#4

lame + kills optimization but serves it purpose

跛脚+杀死优化但服务于它的目的

SELECT * FROM `ids` WHERE concat(id) = '112abcdefg';

that way you enforce casting to string http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

这样你就可以强制转换为字符串http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

#1