Search code examples
mysqlsql-in

Why mysql compare "IN" as a like

I have a table with following data

Table transactions

trasaction_id
886
456
654_asd
898_ASDF

If I use these sentence

SELECT trasaction_id from transactions where transaction_id IN (886,654)

I expect the result be 886,

But mysql is returning 886,654_ASDF

Why is 654_ASDF returned in that query?

Solution

  • This is happening because transaction_id is a string, but your comparison values are numbers. Hence, transaction_id is being converted to a number, using "silent conversion". That means that errors are not reported.

    The conversion is not really using like. It proceeds by converting the leading characters of the string to a number, if they look like a number. Subsequence characters are ignored.

    So, just use the correct types:

    SELECT trasaction_id 
FROM transactions 
WHERE transaction_id IN ('886', '654');