mysql in query with number/string return unexpected result

Question

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]

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]

not yet, SELECT distinct(cookingMethod) FROM cms.recipe where cookingMethod = 3 result : [3,1,5] [3,5] [3] — PercyQQ
– PercyQQ, Commented Nov 3, 2017 at 6:33

Chava Geldzahler · Accepted Answer · 2017-11-03 04:02:02Z

1

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.

edited Nov 3, 2017 at 4:02

answered Nov 3, 2017 at 3:56

Chava Geldzahler

3,7501 gold badge20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql in query with number/string return unexpected result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related