Get distinct element count from table in MySQL and local list

Question

I have a list of strings in memory locally. I have another list of strings in a database table. I want to get a count of distinct strings in the combination of both lists. I don't want to retrieve the full list from the database

By local memory, I mean I have an array variable in PHP. But this question is not language specific.

$var = [ "str1", "str2" ];

The following won't work, because I can't bind a parameter in the locations I need.

SELECT COUNT(DISTINCT str) FROM
 (SELECT str FROM strs UNION ALL SELECT ? UNION ALL SELECT ?)
  AS combinedList;

Is there a way to do this without a temporary table that still uses parameters?

Answer 1

The solution is to use variables.

This example is for two strings, but it works for any number.

SET @str1 = ?;
SET @str2 = ?;
SELECT COUNT(DISTINCT str) FROM
 (SELECT str FROM strs UNION ALL SELECT @str1 UNION ALL SELECT @str2)
  AS combinedList;

This gets the full benefit of parameters with no raw injection, and it uses no temp tables.