mysql user variable within an IN where statement

Question

I now gave up and sorted it by other means but because "curiosity killed the cat" I am trying to figure out how to use mysql query @variables within an IN where statement:

So, from this:

SELECT * FROM table1 WHERE table1.ID IN (794,758)

Try to include a variable and this returns a syntax error:

SET @variousids="(794,758)";
SELECT * FROM table1 WHERE table1.ID IN @variousids

This returns values only from the first:

SET @variousids="794,758";
SELECT * FROM table1 WHERE table1.ID IN (@variousids)

I've tried different syntax, values and have not found any specific doc about defining list of values.

user319198 · Accepted Answer · 2012-02-06 10:22:37Z

8

Use find_in_set . The below will work.

SET @variousids="794,758";
SELECT * FROM table1 WHERE find_in_set(table1.ID,@variousids)

answered Feb 6, 2012 at 10:22

user319198

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

Comments

Bajrang · Accepted Answer · 2012-02-06 10:23:01Z

1

Try :-

SET @variousids="794,758";
SELECT * FROM table1 WHERE table1.ID FIND_IN_SET (table1.ID,@variousids)

answered Feb 6, 2012 at 10:23

Bajrang

8,6795 gold badges29 silver badges40 bronze badges

2 Comments

luison Over a year ago

Thanks, was just about to write about that was the solution I found.

Kukeltje Over a year ago

This one gives me an error, while the one in the other answer works... The 'table1.ID` right after the where cannot be used in MySQL 5.7

aF. · Accepted Answer · 2012-02-06 10:13:15Z

1

Use dynamic SQL:

SET @variousids="794,758";
prepare stmt from 'SELECT * FROM table1 WHERE table1.ID IN ( ? )'
execute stmt using @variousids;

For more info, check THIS article.

answered Feb 6, 2012 at 10:13

aF.

67k45 gold badges141 silver badges200 bronze badges

Collectives™ on Stack Overflow

mysql user variable within an IN where statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related