MySQL select from INT column

Question

I was doing some system testing and expecting empty results from MySQL(5.7.21) but got surprised to get results.

My transactions table looks like this:

Column        Data type
----------------------------
id           | INT
fullnames    | VARCHAR(40)
---------------------------

And I have some records

--------------------------------
id           | fullnames
--------------------------------
20           | Mutinda Boniface
21           | Boniface M
22           | Some-other Guy
-------------------------------

My sample queries:

select * from transactions where id = "20"; -- gives me 1 record which is fine
select * from transactions where id = 20; -- gives me 1 record - FINE as well

Now it gets interesting when I try with these:

select * from transactions where id = "20xxx"; -- gives me 1 record - what is happening here?

What does MySQL do here??

Answer 1

MySQL plays fast and loose with type conversions. When implicitly converting a char to a number, it will take characters from the beginning of the string as long as they are digits, and ignore the rest. In your example, xxx aren't digits, so MySQL only takes the initial "20".

One way around this (which is horrible for performance, since you lose the usage on the index you may have on your column), is to explicitly cast the numeric side to a character:

SELECT * FROM transactions WHARE (CAST id AS CHAR) = 20; 

EDIT:
Referencing the discussion about performance from the comments - performing the cast to a number on the client-side is probably the best approach, as it will allow you to avoid sending queries to the database when you know no rows should be returned (i.e., when your input is not a valid number, such as "20x").

An alternative hack could be to cast the input to a number and back again to a string, and compare the lengths. If the lengths are the same it means the input string was fully converted into a number and no characters were omitted. This should be OK WRT performance, since this comparison is performed on an inputted string, not on a value from the column, and the column's index can still be used if the condition passes the short-circuit evaluation of the input:

SELECT *
FROM   transactions 
WHERE  LENGTH(:input) = LENGTH(CAST(:input AS SIGNED)) AND id = :input;