Why mysql compare "IN" as a like

Question

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?

`'654_asd'` is coerced to an int. `SELECT 654 = '654_asd' == 1` — Alex K., Jan 24 '17 at 14:02

score 4 · Accepted Answer · answered Jan 24 '17 at 14:02

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');

Why mysql compare "IN" as a like

1 Answers1