How to create a user defined variable that can be used in a set expression?

Question

I would like to do this

set @a =(1,2,3);
select * from mytable where somefield in @a;

But mysql does not like it.

How can I do this?

Answer 1

One way to go about this

SET @a = '1,2,3';
SELECT * 
  FROM mytable 
 WHERE FIND_IN_SET(somefield, @a) > 0;

Note: This will effectively cause a full scan.

---

IMHO you better off without user variables containing strings

SELECT * 
  FROM mytable t JOIN
(
  SELECT 1 somefield UNION ALL
  SELECT 2 UNION ALL
  SELECT 3
) q
    ON t.somefield = q.somefield;

---

One more option is to leverage dynamic SQL

SET @a = '1,2,3';
SET @sql = CONCAT('SELECT * FROM mytable WHERE somefield IN(', @a, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo for all queries

Answer 2

You can do this using temporary tables, as in this question: How can I simulate an array variable in MySQL?

Or you can use find_in_set() as in this question: how to set an array as a mysql user variable

Answer 3

Try this

SET @a = '1,2,3';
SET @sql = CONCAT('SELECT * FROM test WHERE `user` IN(', @a, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;

See Fiddle Demo

Answer 4

It could be done but with proper syntax and prepare statement, likethis:

set @a =1,2,3;
select * from mytable where somefield in (@a);


set @crt=concat("select * from mytable where somefield in ",concat('(',@a,')'),";"); 

prepare smnt from @crt;
EXECUTE smnt;
DEALLOCATE PREPARE smnt;