How can I use MySQL variables in subqueries?
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.