使用mysql变量来保存逗号分隔值以用于where子句

I have to run a query like this (query 1) -

我必须运行这样的查询（查询1） -

select something from sometable where someId in (1,2,3)

I would like to keep a variable for the IDs part, like this (query 2) -

我想为ID部分保留一个变量，就像这样（查询2） -

set @myIds = "1,2,3";
select something from sometable where someId in (@myIds);

But this does not give the expected result (gives an empty result set), and no query error as well.

但是这不会给出预期的结果（给出一个空的结果集），也没有查询错误。

I checked that if I wrap the comma separated IDs inside quotes, the query results an empty result set (query 3) -

我检查了如果我将逗号分隔的ID包装在引号内，则查询结果为空结果集（查询3） -

select something from sometable where someId in ("1,2,3");

I guess when I am using variable @myIds like I showed above (query 2), it is evaluating to the above query (query 3).

我想当我使用上面显示的变量@myIds（查询2）时，它正在评估上面的查询（查询3）。

2 个解决方案

#1

You need to have a dynamic sql on this,

你需要有一个动态的SQL，

SET @myIds = '1,2,3';
SET @sql = CONCAT('select something from sometable where someId in (',@myIds,')');

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

SQLFiddle Demo
SQLFiddle演示

#2

The proper (and also more complicated) way to do that would be a temp table:

正确的（也是更复杂的）方法是临时表：

DROP TEMPORARY TABLE IF EXISTS `some_tmp_table`
CREATE TEMPORARY TABLE `some_tmp_table` (
    `id` INT(10) UNSIGNED NOT NULL
) ENGINE=MEMORY #memory engine is optional...

Insert your ID's the temp table

插入您的ID是临时表

INSERT INTO some_tmp_table VALUES (1),(2),(3)...

and then use a JOIN instead of IN().

然后使用JOIN而不是IN（）。

SELECT something 
FROM sometable s
JOIN some_tmp_table ts ON ts.id = s.someId

The other way is to use dynamic SQL as the other answer suggests. It might be simpler for you to generate the dynamic SQL in your app, but you can do it in MySQL too.

另一种方法是使用动态SQL，如另一个答案所示。在您的应用程序中生成动态SQL可能更简单，但您也可以在MySQL中执行此操作。

#1