CODEWITHSUNDEEP
mysqlsql
Primoz Rome
Primoz Rome

Reputation: 11031

MySQL automatic string to integer casting in where clause?

I am not new to MySQL but a strange situation happened in my code today coincidently which got me surprised. Can someone explain why this gives me identical results?

SELECT * FROM `products` WHERE id = 12

and

SELECT * FROM `products` WHERE id = '12ABC'

In both cases I get the same result with the same record being selected. I would expect that second one would return me nothing?! My ID field is int(11) unsigned with auto_increment flag turned on.

Upvotes: 3

Views: 3224

Answers (2)

juergen d
juergen d

Reputation: 204766

MySQL has to make a conversion to make a compare betwen 2 different types. It tries to make the string to an int and get the digits from the string starting from the beginning.

It you had for instance

'ABC12'

the result of the string conversion to int would be 0

Upvotes: 1

potashin
potashin

Reputation: 44581

From MySQL docs:

When an operator is used with operands of different types, type conversion occurs to make the operands compatible

Documentation

So basically, '12ABC' is cast to 12.

Upvotes: 4

Related Questions