Odd behavior of user-defined variables in MySQL

I've noticed something odd about user-defined variables:

Lets say I have this table:

 num_table
+---------+
| numbers |
|    3    |
|    6    |
|    9    |
+---------+

I can create a comma-separated list and store it in a user-defined variable like so:

SELECT @var := GROUP_CONCAT `numbers` from num_table;

Which will assign the value 3,6,9 to @var.

And here is the odd pa开发者_高级运维rt. Running

SELECT 3 IN (@var)

returns 1, but running

SELECT 6 IN (@var)

returns 0.

It seems to me it should either work or not work. Any idea why it works only with the first number in the list?

You cannot use IN () with a variable and have that variable be treated as a list - only actual lists (perhaps of variables) can be used - i.e. IN (1, 2, 3) or IN (@var1, @var2, @var3)

Neither should work if @var contains '3, 6, 9' - so I suspect @var contains '3', though - can you verify its contents?

Martin might be on to something with the casting - I'd bet '3' IN (@var) returns nothing

You cannot use IN () with a string variable - but you can use FIND_IN_SET() instead, which serves exactly this purpose:

SELECT FIND_IN_SET(6, @var)

returns 2 - second position

SELECT FIND_IN_SET(7, @var)

returns NULL - no match