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?
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';
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.
!FIND_IN_SET(), but it still didn't work. Same results as before. It could be the quotes affecting things here-- there is no user named \"admin\". I got it to work by copy/paste my list to remove the variable, but it's just not as clean as with the variable, and it still doesn't explain why my original code did not work. Thanks