使用mysql变量保存逗号分隔的值，以用于where in子句 [英] using mysql variable to hold comma separated value to be used for where in clause

问题描述

我必须运行这样的查询(查询1)-

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

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

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

I would like to keep a variable for the IDs part, like this (query 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.

我检查了是否将逗号分隔的ID括在引号中，查询结果为空结果集(查询3)-

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

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

我猜想当我使用上面显示的变量@myIds(查询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).

推荐答案

您需要对此使用动态SQL，

You need to have a dynamic sql on this,

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演示
• SQLFiddle Demo

这篇关于使用mysql变量保存逗号分隔的值，以用于where in子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！