User variable in "WHERE IN" statement

Question

I'm having difficulty using a WHERE IN() and a user variable together.

When I use a WHERE IN() statement without a variable, everything works well:

-- Returns three rows
SELECT id FROM users WHERE company_id IN (1,2,3);

However, when I assign 1,2,3 to a variable, I suddenly only get one row:

SET @companyIds = '1,2,3';
-- Returns one row (the row that matches "1")
SELECT id FROM users WHERE company_id IN (@companyIds);

I suspect that this is because @companyIds is a string. If I directly insert the string into WHERE IN(), I also only get one row:

-- Returns one row
SELECT id FROM users WHERE company_id IN ('1,2,3');

However, If I try to make @companyIds not be a string, then I get a syntax error:

-- Syntax error
SET @companyIds = 1,2,3;

Is there a way to have a user variable be a "true" array that MySQL can look through?

Thanks! :)

• Rick James: If you created the string, you could create the query.

Answer 1

Using FIND_IN_SET() gives you the correct number of matching rows, but it has a disadvantage: it can't be optimized with an index. It will always search the table by doing a table-scan. So it will get slower and slower in linear proportion to the number of rows as your table grows.

Whereas a proper IN() predicate does use an index, if one is present on the company_id column. The performance will be much improved in most cases.

But as you experienced, you can't use a single variable for a list of values. A variable takes the place of a single string value, even if the variable seems to contain partial SQL syntax (like a comma-separated list of integers).

To use a variable in the way you want, you have to make the variable part of the SQL query before the query is parsed. This means you have to prepare the SQL query as a string, and use PREPARE and EXECUTE to run it.

SET @companyIds = '1,2,3';
SET @sql = CONCAT('SELECT id FROM users WHERE company_id IN (', @companyIds, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Now the variable is part of the IN() predicate, and it can use an index to optimize the search.

• neoncube: Ooooo, very cool, thank you! :D

Answer 2

Ah, I just found the answer, which is to use FIND_IN_SET():

SET @companyIds = '1,2,3';
SELECT id FROM users WHERE FIND_IN_SET(company_id, @companyIds);

• Rohit Gupta: Fyi, you are able to tick it in 24hrs