Weird Select Result

Question

Hi All,

I have a table with an ID field of INT Type.

I am doing some data validation and noticed that

SELECT * from mytable where id=94

and

SELECT * from mytable where id='94'

works perfectly, but if I use

SELECT * from mytable where id='94dssdfdfgsdfg2'

it gave me the same result! How is this possible?

Answer 1

it would be possible if the internal implementation of MySql's string to int function dropped all characters from the string after the first non-numeric when parsing it.

Answer 2

What you've witnessed is called "implicit data conversion".

Implicit, the opposite of "explicit", means that the data type is automatically converted to the data type of the column being compared when possible. In this case, MYTABLE.id is an INTeger data type so MySQL will convert the value being compared to an INT if it is enclosed in single quotes (string based data type to SQL).

Because of the conversion, the data is getting truncated at the end of the last numeric character after starting from the leftmost position in the string.