MYSQL变量IN子句 [英] MYSQL variable IN clause

问题描述

在过滤掉一些垃圾邮件时，我在一个文件中有两个MYSQL语句，

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

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

后跟这样的SELECT

Followed by a SELECT like this

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

执行此操作时，它的作用就好像@valid_users是一个空字符串. (返回所有结果).但是，如果我将子句更改为NOT IN ('admin', 'jrock', etc)，那么它将按预期工作.

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.

为什么NOT IN过滤器中的变量不起作用?

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

推荐答案

您将要看看MySQL的

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示例

要直接回答有关为什么NOT IN过滤器中的变量为什么起作用"的问题，这是因为@valid_users被视为字符串，当您将其传递给IN()时，它被视为单个字符串.字符串(即不是集合/列表).对于FIND_IN_SET()，它将@valid_users中的字符串视为逗号分隔的集合/列表，并相应地使用它.

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.

这篇关于MYSQL变量IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！