mysql - 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'