php MySQL Select 语句，WHERE 'IN' 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1586087/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Select Statement, WHERE 'IN' Clause
提问by Dodinas
I currently have the following row in my table:
我目前在我的表中有以下行：
course_data:
user_id days <-- This is a varchar column.
405 1,3,5
and I am trying to implement the following SELECT statement:
我正在尝试实现以下 SELECT 语句：
SELECT usrID, usrFirst, usrLast, usrEmail
FROM tblUsers
WHERE usrID NOT IN
(
SELECT users.usrID
FROM
`course_data` courses,
`tblUsers` users
WHERE
days IN ('$day')
)
GROUP BY usrID
ORDER BY usrID
Basically, I want that row (with user 405) to be omitted if the $day variable includes a '1, 3, or 5'.
基本上，如果 $day 变量包含“1、3 或 5”，我希望省略该行（用户为 405）。
For example, if $day = "1", it should return an empty query (because the number "1" is in the column "1,3,5").
例如，如果$day = "1"，它应该返回一个空查询（因为数字“1”在“1,3,5”列中）。
However, I have not found this to be the case. Even though $day = "1", it still returns that row.
但是，我还没有发现这种情况。即使$day = "1"，它仍然返回该行。
The only way that it won't return the row is if $day= "1,3,5."Yet, I thought that the IN() clause would take any part of my variable and apply it to that column.
它不会返回行的唯一方法是，如果$day= "1,3,5."然而，我认为 IN() 子句将采用我的变量的任何部分并将其应用于该列。
Any insights on what I'm doing wrong here? Thanks.
关于我在这里做错了什么的任何见解？谢谢。
回答by theomega
Remove the Quotes in the IN Statement. The Syntax is:
删除 IN 语句中的引号。语法是：
... WHERE column IN (1,2,3)
and not as you used it
而不是你使用的那样
... WHERE column IN ('1,2,3')
Also see the documentationon IN, there are more examples.
另请参阅有关 IN的文档，还有更多示例。
回答by James McNellis
You should use the likekeywordto do a partial match of the char field:
您应该使用的like关键字做煤焦领域的一个部分匹配：
where days like '%1%'
Edit:VolkerK and Lukas Lalinsky both suggest MySQL's find_in_set, which is probably better than likefor what you want to do. However, the following recommendation about normalizing your database still applies.
编辑：VolkerK 和 Lukas Lalinsky 都建议使用 MySQL 的find_in_set，这可能比like您想要做的更好。但是，以下有关规范化数据库的建议仍然适用。
However,you should not store multiple values in a single database field. Instead, consider using two tables:
但是，您不应在单个数据库字段中存储多个值。相反，请考虑使用两个表：
course_data:
user_id
course_days:
user_id
day_number
Then, you would have the following data:
然后，您将拥有以下数据：
course_data:
user_id
405
course_days
user_id day_number
405 1
405 3
405 5
You can then correctly query this schema. For example:
然后，您可以正确查询此架构。例如：
select cd.user_id
from course_data as cd
where cd.user_id not in
(
select course_days.user_id
from course_days
where course_days.user_id = cd.user_id
and course_days.day_number = 1
)
(or, that should be close; I'm not exactlysure what you're trying to accomplish or what the rest of your schema looks like, so this is a best guess).
（或者，这应该是接近，我不准确确定你想要什么来完成或者什么休息您的架构看起来像，所以这是一个最好的猜测）。
回答by Luká? Lalinsky
I think the query you want is:
我认为你想要的查询是：
SELECT usrID, usrFirst, usrLast, usrEmail
FROM tblUsers
WHERE usrID NOT IN (
SELECT user_id
FROM course_data
WHERE find_in_set(?, days) > 0
)
ORDER BY usrID
But you should seriously consider normalizing the database, so that each day has it's own row.
但是您应该认真考虑规范化数据库，以便每天都有自己的行。
回答by VolkerK
If I understand your question correctly you want the contents of the varchar field to be treated as a comma-separated list and test whether this list does not contain a certain value. For that you need the find_in_set(str, strlist)function.
But keep in mind that MySQL can't use an index in that case and your query will always need a full table scan. It mightbe better not to store structured data (and run comparisons on the single elements) in a single column but to use another table and a JOIN as has been suggested in other responses.
如果我正确理解您的问题，您希望将 varchar 字段的内容视为逗号分隔的列表，并测试此列表是否不包含某个值。为此，您需要find_in_set(str, strlist)函数。
但请记住，在这种情况下 MySQL 不能使用索引，并且您的查询将始终需要全表扫描。这可能是最好不要（对单一要素和运行比较），结构化数据存储在一个单一的列，但使用另一个表和已在其他回应有人提出一个JOIN。
回答by Yuliy
days does not hold a list of strings. It holds a single string. Is the string "1,3,5" inside one of the strings in {'1'}? Clearly the answer is no. Either refactor days into a different table, or be prepared to do more string manipulation
days 不包含字符串列表。它包含一个字符串。字符串“1,3,5”是否在 {'1'} 中的字符串之一内？显然答案是否定的。要么将 days 重构为不同的表，要么准备进行更多的字符串操作
