MySQL Variables, GROUP_CONCAT, and using it later

Question

I am storing a hard list of

SELECT @items := GROUP_CONCAT(ID) FROM table_1 ... etc

@items is now a string of numbers: 55,77,99,2038,2844,etc

Later, I try to use it in a where clause as such:

SELECT * FROM table_2 WHERE table_1.ID IN (@items)

This does not work. It seems like it should. I know when I manually pull the data, put it in a variable, then output it it works:

list($x) = SELECT @items := GROUP_CONCAT(ID) FROM table_1 ... etc
$goodResults = SELECT * FROM table_2 WHERE table_1.ID IN ($x)

Any ideas? Thanks.

Answer 1

You may want to use the FIND_IN_SET() function:

SELECT * FROM table_1 WHERE FIND_IN_SET(id, @items) > 0;

Test case:

CREATE TABLE table_1 (id int, group_id int);
INSERT INTO table_1 VALUES (1, 1);
INSERT INTO table_1 VALUES (2, 1);
INSERT INTO table_1 VALUES (3, 1);
INSERT INTO table_1 VALUES (4, 1);
INSERT INTO table_1 VALUES (5, 1);

SELECT @items := GROUP_CONCAT(id) FROM table_1 GROUP BY group_id;

SELECT * FROM table_1 WHERE FIND_IN_SET(id, @items) > 0;
+------+----------+
| id   | group_id |
+------+----------+
|    1 |        1 |
|    2 |        1 |
|    3 |        1 |
|    4 |        1 |
|    5 |        1 |
+------+----------+
5 rows in set (0.02 sec)

Answer 2

From the MySQL manual:

"User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits."

Because you noted 'later' it is possible that the connection creating the variable has been destroyed and the variable is lost.

Could you temporarily hold the variable value in a table?

Answer 3

"@items is now a string of numbers". The IN clause expects a set.

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in