mysql:将数组设置为变量以便在 "IN"子句中使用

Question

我想将数组设置为变量，以便在“IN”子句的查询中使用它。下面的代码给了我一个错误，所以我不确定它是否可以在 mysql 中实现。

set @ids = (1,2,3,6);

select 
    sum(promotion_id IN @ids AND confirmed_at IS NOT NULL)
from blabla

我得到了两个不同的结果。也就是说，find_in_set 和 IN(@ids) 给出的结果相同，但与我从 IN(1,2,3) 得到的结果不同:

set @voda_prom ='1483, 2396, 2395, 1887';

    SUM(FIND_IN_SET(promotion_id,@voda_prom) and confirmed_at IS NOT NULL)*1.0/ SUM(FIND_IN_SET(promotion_id,@voda_prom)) as p,
    SUM(promotion_id IN(1483,2396,2395,1887) and confirmed_at IS NOT NULL)*1.0/ SUM(promotion_id IN(1483,2396,2395,1887)) as p_check,
    SUM(promotion_id IN(@voda_prom) and confirmed_at IS NOT NULL)*1.0/ SUM(promotion_id IN(@voda_prom)) as p_1,

Answer 1

使用find_in_set而不是in

set @ids = '1,2,3,6';
select 
    SUM(CASE WHEN FIND_IN_SET(promotion_id,@ids) AND confirmed_at IS NOT NULL THEN 1 ELSE 0 END)
from blabla

该函数与php中的in_array相同