在MySQL中，如何在WHERE IN子句中使用用户定义的变量?

问题描述：

我不明白为什么这行不通.有人可以解释我需要做什么吗?

I don't understand why this does not work. Can someone explain what I need to do?

SET @my_list = '2,6,8,10,12,13,14,18,19,21';

DELETE FROM my_table WHERE my_table.table_id IN (@my_list);

答

它将@my_list解释为单个ID，因此您要求它从my_table中删除，其中ID为字符串"2,6,8,10" ，12,13,14,18,19,21"

It's interpreting @my_list as a single id and so you're asking it to delete from my_table where your id is the string "2,6,8,10,12,13,14,18,19,21"

您可以尝试

SET @my_list = '2,6,8,10,12,13,14,18,19,21';
SET @exec = CONCAT('DELETE FROM my_table WHERE my_table.table_id IN (', @my_list ,')');
EXECUTE (@exec);