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.

souslov · Accepted Answer · 2015-11-05 11:45:23Z

4

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.

answered Nov 5, 2015 at 11:45

souslov

44.7k11 gold badges92 silver badges113 bronze badges

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

3 Comments

Primoz Rome Over a year ago

Dear lord :-). Can this be disabled or should I explicitly say do not convert somehow?

souslov Over a year ago

@PrimozRome: It seems that it is not possible to suppress this behaviour.

Sam Shiles Over a year ago

This is horrendous behavior. Is it still the case that this can't be turned off and made more strict?

juergen d · Accepted Answer · 2015-11-05 11:45:28Z

1

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

answered Nov 5, 2015 at 11:45

juergen d

205k40 gold badges303 silver badges377 bronze badges

Collectives™ on Stack Overflow

MySQL automatic string to integer casting in where clause?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related