CODEWITHSUNDEEP
mysqlsql-in
Yeison Agudelo
Yeison Agudelo

Reputation: 25

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?

Upvotes: 2

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

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

Upvotes: 4

Related Questions