MySQL automatic string to integer casting in where clause?

Question

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.

Answer 1

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.

Answer 2

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