How can I use MySQL variables in subqueries?

Question

I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why.

Here's the query:

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

Here's the information underneath the variable and table.

TABLE:

mysql> mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

VARIABLE:

mysql> SELECT GROUP_CONCAT(QUOTE(name)) INTO @animal_names FROM animals WHERE name LIKE '%%';
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT @animal_names;
+-----------------------------------------------+
| @animal_names |
+-----------------------------------------------+
| 'dog','cat','penguin','lax','whale','ostrich' |
+-----------------------------------------------+
1 row in set (0.00 sec)

ISSUE:

mysql> SELECT * FROM animals WHERE name IN(SELECT @animal_names);
Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM animals WHERE name IN(SELECT @animal_names);
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | animals | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1249): Select 2 was reduced during optimization

MySQL refuses to tell me why the "SELECT @animal_ids" is not working! Is there some MySQL variable that needs to be adjusted for that subquery to work?

This is a test sample. I'm trying to do this on a larger scale with about 800 values in a list. I want the list in a variable so I don't have to do A SELECT query multiple times to DELETE, INSERT, SELECT the values I need across multiple databases and tables.

Obviously this can be easily done with a Python or Golang script, but I'm trying to do this in MySQL directly. While I appreciate it, please refrain from offering script-based solutions.

Answer 1

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows.

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

is translated to

SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\'');

MySQL variable cannot hold table resultset so this is not directly possible. You would have to create a subquery to extract separate values to rows by joining some counter table and parsing the string. Or you may use dynamic sql which allows to take a string and parse it - that way you would define new variable containing entire select with the IN list filled by your current variable, and then prepare a statement from that string representation (sort of SQL eval). But probably best would be to just use the query direcly as a subquery instead of filling the variable. Newer version should materialize such independent subquery automatically. For good performance on older versions, you might want to create a temporary table instead and use JOIN and not IN.

Just a note - GROUP_CONCAT() is not very safe as there is a limit (variable) to how long string it can return and some results may be lost (with a warning) when the limit is exceeded.

• Amine: You can set your own limit to avoid losing results: SET SESSION group_concat_max_len = 10000;

Answer 2

You could do:

SELECT *
FROM animals
WHERE CONCAT(',', @animal_names, ',') LIKE CONCAT('%,\'', name, '\',%');

It won't perform fast, but it'll work.

• Rick James: May fail if the lists are sorted differently.
• Ezequiel Tolnay: @RickJames It could only fail if the elements in the list could have commas next to apostrophes

Answer 3

Build a Stored Routine; in it use CONCAT to construct the SELECT with the @variable stitched in; prepare and execute.

Alternatively, if you are using an application language such as PHP, do the stitching in that language.

Answer 4

SELECT * FROM animals WHERE FIND_IN_SET(name,(SELECT @animal_names));

See the FIND_IN_SET documentation

• Alexk: This is what I was trying to do.