Using the result of a custom MySQL function in a WHERE IN clause

Question

I'm trying to use the result of a custom MySQL function in the WHERE IN clause of a query:

SELECT * 
  FROM project p 
 WHERE p.category_id IN (getCategorys(1))

The result of the custom function is as follows:

SELECT getCategorys(1)

 1,2,3,4,5

Unfortunately, this does not give the same result as:

SELECT * FROM project p WHERE p.category_id IN (1,2,3,4,5)

The query using the custom function only returns results where category_id = 1

What am I missing here?

Answer 1

If you use the FIND_IN_SET() solution that @Uueerdo mentions, it'll always do a table-scan. It can't use an index.

The only way to make this work with index optimization is to fetch the string of category id's before you prepare the query. In other words, do two queries.

Something like this (but I haven't tested it):

SELECT getCategorys(1) INTO @tmp;

SET @sql = CONCAT('
  SELECT * 
  FROM project p 
  WHERE p.category_id IN (', @tmp, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This is technically a case of SQL injection, even though it's using content that you produce from your own function. Be sure you have complete control over what your getCategorys() function returns.

For example, can the function ever return an empty string? That would result in IN ( ) which is a syntax error.

Answer 2

The form the results from the custom function take is not obvious from the question; but IN takes lists or results, not strings. I am guessing the function returns a string.

It's not ideal from a performance perspective but FIND_IN_SET may be easier to use to produce the behavior you want.