mysql: set an array to a variable in order to use in "IN" clause

Question

I want to set an array to a variable in order to use it in my queries in "IN" clause. The code below gives me an error back, so I am not sure if it is possible in mysql.

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

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

I get different two differt results. Namely, find_in_set and IN(@ids) give me identical result but it differs from the one I get from 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,

Beginner · Accepted Answer · 2016-12-07 09:55:33Z

1

use find_in_set instead of 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

this function is just same as in_array in php

edited Dec 7, 2016 at 9:55

answered Dec 7, 2016 at 9:25

Beginner

4,1183 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alina Over a year ago

No, it did not. The result is different from when i use IN(1,2,3..)

Beginner Over a year ago

you mean its different from promotion_id in(1,2,3,6) or just promotion_id in(@ids)

Alina Over a year ago

find_in_set and IN(@ids) give me the same results but it is different from IN(1,2,3)

Beginner Over a year ago

@let's continue here. chat.stackoverflow.com/rooms/129988/…

Bhumi Shah · Accepted Answer · 2016-12-07 09:17:33Z

0

You can use like this:

SET @ids = '1, 2, 3, 6';
select 
    sum(`promotion_id` IN ( @ids)  AND `confirmed_at` IS NOT NULL)
from `blabla`

answered Dec 7, 2016 at 9:17

Bhumi Shah

9,50210 gold badges69 silver badges110 bronze badges

Comments

Samir Lakhani · Accepted Answer · 2016-12-07 09:30:53Z

0

**You Have To Convert Array Into String First Each Element is Imploaded By , commma**


   $arr=array(1,2,3,6);
     $str_arr=implode(',',$arr);


    set @ids = '1,2,3,6';

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

answered Dec 7, 2016 at 9:30

Samir Lakhani

76311 silver badges19 bronze badges

Collectives™ on Stack Overflow

mysql: set an array to a variable in order to use in "IN" clause

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related