10

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?

the Hampster
  • 876
  • 1
  • 14
  • 21

1 Answers1

29

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.

newfurniturey
  • 37,556
  • 9
  • 94
  • 102
  • Technically, `!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 – the Hampster Jan 13 '14 at 21:22
  • @theHampster Please see my updated answer that should hopefully clarify your issue =] – newfurniturey Jan 13 '14 at 21:33
  • Thanks. I'm chalking this up to poor language constructions. IN cannot use strings, but accepts things that "look" like strings. – the Hampster Jan 15 '14 at 21:44