Multiple values in MySQL variable

Question

The following works as expected when there is a single value stored in a variable.

SET @a := "20100630";
SELECT * FROM wordbase WHERE verified = @a;

But it does not work when there are multiple values stored in a variable.

SET @a := "'20100630', '20100701' ";
SELECT * FROM wordbase WHERE verified in (@a);

Do I need to use prepared statements for this?

SET @a := "'20100630', '20100701' " should be SET @a := "20100630, 20100701". You'd added quotes for integer values, which caused it to return no rows — Akash, Jul 21 '13 at 08:57

score 24 · Answer 1 · edited May 23 '17 at 12:00

There's good solution described here: https://stackoverflow.com/a/11957706/1523961

So can use something like this:

SET @a := '20100630,20100701';
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);

Also, if you're selecting the ids for @a from another table, you can come up with following:

SET @a := (SELECT GROUP_CONCAT(id) FROM someTable where yourBooleanExpressionHere);
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);

score 6 · Answer 2 · answered Jul 21 '13 at 08:05

6

Using GROUP_CONCAT and GROUP BY one could pull all values ( i.e. an id ) into a variable like so:

SET @var := (SELECT GROUP_CONCAT(id) FROM `table` WHERE `verified` = @verified GROUP BY verified);

answered Jul 21 '13 at 08:05

Beshoy Girgis

465
5
11

3

depends on the amount of values. GROUP_CONCAT has a maximum length. – buggedcom Apr 29 '15 at 12:08

score 6 · Accepted Answer · answered Jul 01 '10 at 09:35

You cannot (as far as I am aware) store multiple values in a MySQL user defined variable. What you have done is create a string which contains:

'20100630', '20100701'

That is not two separate values, but a single string value, just as this is a single string value:

SET @a := "It's a single string, and that's the problem";

You need to use two separate variables, or prepare a statement, like this:

SET @a := "20100630";
SET @b := "20100701";

SET @sql = CONCAT(
    'SELECT * FROM wordbase WHERE verified IN (',
    @a,
    ',',
    @b,
    ')'
);


SELECT @sql;
+--------------------------------------------------------------+
| @sql                                                         |
+--------------------------------------------------------------+
| SELECT * FROM wordbase WHERE verified IN (20100630,20100701) |
+--------------------------------------------------------------+

PREPARE stmt FROM @sql;
EXECUTE stmt;

But that's kinda messy. Why do you need to use variables?

score 3 · Answer 4 · answered Jul 01 '10 at 09:56

3

Something like this should work. Is it ok to use prepared statements to create temporary tables like this?

SET @a := "'20100630', '20100701'";
SET @sql = CONCAT('create temporary table pn1 SELECT * FROM wordbase WHERE verified IN (', @a, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;

select * from pn1;

answered Jul 01 '10 at 09:56

shantanuo

30,102
75
225
364

I know this is an old post but this worked perfectly especially when you have multiple values. – sho Nov 01 '11 at 06:55

score 1 · Answer 5 · edited Nov 06 '17 at 16:32

1

SELECT GROUP_CONCAT(field_table1 SEPARATOR ',') FROM table1 into @var;

then

SELECT * FROM table2 WHERE field_table2 in(@var);

works fine for me

edited Nov 06 '17 at 16:32

Nissa

4,598
8
29
37

answered Nov 06 '17 at 16:16

Marcello Telles

19
4

I fail to see how this can work. First query creates a massive string which is the concatenation of `field_table1` after converting it to a string. Second query checks whether `field_table2` is equal to that massive string. – xhienne Feb 14 '18 at 17:30

justnajm · Answer 6 · 2020-07-03T23:34:08.093

0

FIND_IN_SET(column to find in , string csv) is a very handy method in case you have the string list of CSV:

SET @a := "'20100630', '20100701' ";
SELECT * FROM wordbase WHERE FIND_IN_SET(verified, @a);

if your variable is also coming from query then use this to set @a

SET @a := (SELECT GROUP_CONCAT(`id`) FROM `table`);

edited Jul 03 '20 at 23:34

answered Jul 03 '20 at 16:02

justnajm

4,045
6
33
55

score 0 · Answer 7 · answered Jul 07 '21 at 22:47

If you need to use your variable for a select or delete you can use select in the select:

delete from  MPCurrentPayEntitlementAccrual where CurrentPayEntitlementID in (  select CurrentPayEntitlementID from MPCurrentPayEntitlement where PayRunID=myPayRunId by PayRunID desc);

That worked perfectly for me

Multiple values in MySQL variable

7 Answers7

Linked