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?

Answer 1

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?

Answer 2

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;