Geek asks and answers

MYSQL IN variable clause

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

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

Following SELECT, like this

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

When I do this, it acts as if it @valid_usersis an empty string. (Returns all results). But if I change the sentence to NOT IN ('admin', 'jrock', etc), it will work as it should.

Why NOT INdoes variable in not work?

+3
source share
1 answer

You want to take a look at the MySQL function find_in_set():

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

For this to work, the comma separated list should not contain quotation marks (unless your usernames actually contain quotation marks) and should not be padded with spaces:

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

SqlFiddle example

" NOT IN", , @valid_users , IN(), (.. /). find_in_set() @valid_users / .

+9

More articles:


All Articles