MYSQL variable IN clause [duplicate]

Question

In filtering out some spam, I have two MYSQL statements in one file,

SET @valid_users := '"admin", "jrock", "kmicka", "First Last"';  //etc

Followed by a SELECT like this

SELECT /*stuff*/ /*WHERE*/ /*filters*/ AND User.user_name NOT IN (@valid_users)

When I do this, it acts as if @valid_users is an empty string. (Returns all results). But if I change the clause to NOT IN ('admin', 'jrock', etc) then it works as it should.

Why would a variable in the NOT IN filter not work?

Answer 1

You'll want to take a look at MySQL's find_in_set() function:

SELECT
    *
FROM
    your_table
WHERE
    NOT FIND_IN_SET(User.user_name, @valid_users);

For this to work, the comma-separated list shouldn't contain quotes (unless your usernames actually contain quotes) and should not be padded with spaces:

SET @valid_users := 'admin,jrock,kmicka,First Last';

SqlFiddle Example

To directly answer your question regarding "why would a variable in the NOT IN filter work", it's because @valid_users is being treated as a string and when you pass it to IN(), it's being treated as a single string (i.e. not a set/list). With FIND_IN_SET(), it treats the string in @valid_users as a comma-separated set/list and uses it accordingly.