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?
推荐答案
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';
要直接回答有关为什么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屋!