How can I use the value of a variable in an IN statement?-CodePudding

example:

SET @numbers:= '1,2,3,4,5,6';

SELECT * FROM table WHERE t.number in (@numbers);

UPDATE table SET name = 'test' WHERE t.number in (@numbers);

How can I use the value of a variable in an IN statement? As in the request above

CodePudding user response：

You can't use IN on a CSV string like that.

But you can use it with FIND_IN_SET

SET @numbers:= '1,2,3,4,5,6';

UPDATE your_table t
SET name = 'test' 
WHERE FIND_IN_SET(t.number, @numbers) != 0;

SELECT * 
FROM your_table t
WHERE FIND_IN_SET(t.number, @numbers) != 0;

In MySql 8 you could also unfold such CSV string as a JSON_TABLE.
And use that as a query for the IN.

SET @numbers:= '1,2,3,4,5,6';

UPDATE your_table t
SET name = 'test'
WHERE t.number IN (SELECT num FROM JSON_TABLE(CONCAT('[',@numbers,']'),'$[*]' COLUMNS(num INT PATH '$'))nums); 

SELECT *
FROM your_table t
WHERE t.number IN (SELECT num FROM JSON_TABLE(CONCAT('[',@numbers,']'),'$[*]' COLUMNS(num INT PATH '$'))nums);