Use a comma-separated string in an `IN ()` in MySQL

If a user-defined variable @x is a string of comma separated numbers, e.g. '1,2,4', is there a way to use it in an IN () function?

Specifically:

SET @x :开发者_JAVA技巧= '1,2,4';
SELECT * FROM t WHERE c IN (@x);

does not select rows in t where c equals 1 or 2 or 4.

You can't do this directly using a variable. Based on what you have now, the server is trying to execute select * form t where c in ('1,2,3'); ... which clearly isn't what you want given that you're checking to see if c contains the single string given.

You must generate dynamic SQL based off of your list in order to get this to work.

Maybe there is no point of answering this question now, but recently I faced same problem and this is how I resolved it.

It worked for me, hope this is what you were looking for.

select * from table_name where CONCAT(',',('1,2,4'),',') LIKE CONCAT('%,',column_name,',%');

Example: It will look like this

select * from t where ',1,2,4,' LIKE '%,2,%';

Try this:

SET @x := '1,2,4';
SET @sql = CONCAT('SELECT * FROM t WHERE c IN (', @x, ');');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;