Querying a string from int column?

Question

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.

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.

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?

Answer 1

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

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

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

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:

WHERE BINARY id = '112abcdefg'

Answer 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

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.

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:

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

Source

Answer 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.