CODEWITHSUNDEEP
mysqluser-variables
jisaacstone
jisaacstone

Reputation: 4284

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 part. 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?

Upvotes: 1

Views: 187

Answers (2)

Kaii
Kaii

Reputation: 20550

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

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89711

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

Upvotes: 2

Related Questions