I need a query like this.
Set @var1:="30,31";
Select * from mytable where id in (@var)
I've tried on more tables, but MySQL returns null value. Why?
-
Because @var1 is a varchar variable and not a list of values.forpas– forpas2019-06-17 13:34:15 +00:00Commented Jun 17, 2019 at 13:34
-
1Not that it matters but you are testing @VAR which you haven't set up.P.Salmon– P.Salmon2019-06-17 13:36:34 +00:00Commented Jun 17, 2019 at 13:36
-
Have you checked for obvious typos?Nico Haase– Nico Haase2019-06-17 13:50:19 +00:00Commented Jun 17, 2019 at 13:50
Add a comment
|
2 Answers
You can do with find_in_set() check Here
SET @var1 = "30,31";
SELECT * FROM mytable WHERE find_in_set(myTable.myColumn, @var1);
MySQL return NULL value because search string and not a list of values
Comments
You need dynamic SQL to do this. Prepared statements are one general way to go, except that binding an unknown number of values to a WHERE IN clause is difficult. Assuming you always would be binding only two values, we can try:
SET @sql = 'SELECT * FROM mytable WHERE id IN (?, ?)';
PREPARE stmt2 FROM @sql;
SET @var1 = 30;
SET @var2 = 31;
EXECUTE stmt2 USING @var1, @var2;
