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?

Extract the number before you do your query

John Conde
– John Conde

2013-05-29 18:09:49 +00:00
Commented May 29, 2013 at 18:09 — John Conde
– John Conde, Commented May 29, 2013 at 18:09
Fun reading: sql-info.de/mysql/gotchas.html

Denis de Bernardy
– Denis de Bernardy

2013-05-29 18:11:39 +00:00
Commented May 29, 2013 at 18:11 — Denis de Bernardy
– Denis de Bernardy, Commented May 29, 2013 at 18:11

Ed Gibbs · Accepted Answer · 2013-05-29 18:14:00Z

5

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'

answered May 29, 2013 at 18:14

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chue x Over a year ago

I think the proper usage is cast(id as char(12)).

Ed Gibbs Over a year ago

Thanks for the catch @chuex - a case of "typing while distracted" :)

chue x Over a year ago

+1 you just beat me to this one. I kept trying to cast as varchar, but it kept complaining. (I'm not a MySQL guy).

AAlferez · Accepted Answer · 2013-05-29 18:31:45Z

4

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

edited May 29, 2013 at 18:31

answered May 29, 2013 at 18:11

AAlferez

1,5022 gold badges23 silver badges50 bronze badges

3 Comments

pati Over a year ago

You probably misunderstood my question.

AAlferez Over a year ago

So you pretend to compare an int and a string and get results? SQL is parsing that string when it should just crash saying its wrong.

pati Over a year ago

Exactly the opposite, I want to get no results, but it gives me results.

Ochi · Accepted Answer · 2013-05-29 18:24:27Z

2

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

answered May 29, 2013 at 18:24

Ochi

1,48812 silver badges18 bronze badges

Comments

jospratik · Accepted Answer · 2013-05-29 18:13:14Z

1

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.

answered May 29, 2013 at 18:13

jospratik

1,6942 gold badges12 silver badges19 bronze badges

1 Comment

pati Over a year ago

I'll try this, but I prefer a query-level solution, don't want to mess up the whole application.

Collectives™ on Stack Overflow

Querying a string from int column?

4 Answers 4

3 Comments

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related