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

grinderX19 · Answer 1 · 2022-06-12T12:06:57.227

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

So you 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 the following:

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

score 8 · 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 6 · Answer 3 · 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 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

31,689
78
245
403

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

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

1

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,422
6
36
56

score 0 · Answer 6 · 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

score -1 · Answer 7 · 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,636
8
29
37

answered Nov 06 '17 at 16:16

Marcello Telles

15
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
1

This might look like it worked if you only ever retrieve *one* value from table1. Then @var will be equal to that one value, and the second select will, indeed, search and find the rows where field_table2 has that value. But if the values are two, say "A" and "B", the search will be for "A,B"; so, table2 rows with "A" will not be selected, because "A" is not "A,B", and neither will rows with "B". The query will return without errors, but without data. – LSerni Oct 19 '22 at 09:45
1

Hey, `GROUP_CONCAT(field_table1 SEPARATOR ',')` builds a string like: `1,,2,,3`, I think that `GROUP_CONCAT(field_table1)` is enough – funder7 Jan 12 '23 at 17:12

Multiple values in MySQL variable

7 Answers7

Linked