CODEWITHSUNDEEP
mysql
PercyQQ
PercyQQ

Reputation: 1

mysql in query with number/string return unexpected result

The field cookingMethod is varchar. When I pass the number parameter: in(3,4) why do I get this result? 

SELECT distinct(cookingMethod) FROM recipe where cookingMethod in(3, 4);

Result: [3][3,5][3,4][4][4,5]

enter image description here

and if I use ('3','4'), the result is only '3','4'?

SELECT distinct(cookingMethod) FROM recipe where cookingMethod in('3', '4')

Result: [3][4]

Upvotes: 0

Views: 46

Answers (1)

Chava Geldzahler
Chava Geldzahler

Reputation: 3730

Your second query is simple, and returns the expected result, because you are comparing strings with strings.

In your first query, however, you are using numbers for comparison with strings. MySQL implicitly tries to convert the value of the field into a number to perform the comparison (where other databases might just have thrown an error). However, since there field has both numbers and commas, MySQL treats it as a BLOB with a comma-separated list of numbers, for purposes of this query. The IN operator will search through a comma-separated list, and therefore it matches every field that has either a 3 or a 4 within it's comma-separated list of numbers.

Upvotes: 1

Related Questions