MySQL WHERE IN ()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9736284/
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 WHERE IN ()
提问by netcase
My query is:
我的查询是：
SELECT * FROM table WHERE id IN (1,2,3,4);I use it for usergroups and a user can be in more than one group. but it seems that when a record has multiple id like 1 and 3, mySQL does not return that row.
我将它用于用户组，并且一个用户可以在多个组中。但似乎当一条记录有多个 id 像 1 和 3 时，mySQL 不会返回该行。
Is there any way to get that row too?
有什么办法也可以得到那一行吗？
回答by Starx
Your query translates to
您的查询转换为
SELECT * FROM table WHERE id='1' or id='2' or id='3' or id='4';
It will only return the results that match it.
它只会返回与其匹配的结果。
One way of solving it avoiding the complexity would be, chaning the datatype to SET.
Then you could use, FIND_IN_SET
解决它避免复杂性的一种方法是，将数据类型更改为SET. 然后你可以使用，FIND_IN_SET
SELECT * FROM table WHERE FIND_IN_SET('1', id);
回答by Buksy
You have wrong database design and you should take a time to read something about database normalization (wikipedia/ stackoverflow).
您有错误的数据库设计，您应该花时间阅读有关数据库规范化的内容（维基百科/ stackoverflow）。
I assume your table looks somewhat like this
我想你的桌子看起来有点像这样
TABLE
================================
| group_id | user_ids | name |
--------------------------------
| 1 | 1,4,6 | group1 |
--------------------------------
| 2 | 4,5,1 | group2 |
so in your table of user groups, each row represents one group and in user_idscolumn you have set of user ids assigned to that group.
所以在你的用户组表中，每一行代表一个组，在user_ids列中你有一组分配给该组的用户 ID。
Normalized versionof this table would look like this
此表的规范化版本如下所示
GROUP
=====================
| id | name |
---------------------
| 1 | group1 |
---------------------
| 2 | group2 |
GROUP_USER_ASSIGNMENT
======================
| group_id | user_id |
----------------------
| 1 | 1 |
----------------------
| 1 | 4 |
----------------------
| 1 | 6 |
----------------------
| 2 | 4 |
----------------------
| ...
Then you can easily select all users with assigned group, or all users in group, or all groups of user, or whatever you can think of. Also, your sql query will work:
然后您可以轻松地选择所有分配组的用户，或组中的所有用户，或所有用户组，或您能想到的任何内容。此外，您的 sql 查询将起作用：
/* Your query to select assignments */
SELECT * FROM `group_user_assignment` WHERE user_id IN (1,2,3,4);
/* Select only some users */
SELECT * FROM `group_user_assignment` t1
JOIN `group` t2 ON t2.id = t1.group_id
WHERE user_id IN (1,4);
/* Select all groups of user */
SELECT * FROM `group_user_assignment` t1
JOIN `group` t2 ON t2.id = t1.group_id
WHERE t1.`user_id` = 1;
/* Select all users of group */
SELECT * FROM `group_user_assignment` t1
JOIN `group` t2 ON t2.id = t1.group_id
WHERE t1.`group_id` = 1;
/* Count number of groups user is in */
SELECT COUNT(*) AS `groups_count` FROM `group_user_assignment` WHERE `user_id` = 1;
/* Count number of users in group */
SELECT COUNT(*) AS `users_count` FROM `group_user_assignment` WHERE `group_id` = 1;
This way it will be also easier to update database, when you would like to add new assignment, you just simply insert new row in group_user_assignment, when you want to remove assignment you just delete row in group_user_assignment.
这样更新数据库也会更容易，当你想添加新的分配时，你只需简单地在 中插入新行group_user_assignment，当你想删除分配时，你只需删除 中的行group_user_assignment。
In your database design, to update assignments, you would have to get your assignment set from database, process it and update and then write back to database.
在您的数据库设计中，要更新作业，您必须从数据库中获取作业集，对其进行处理并更新，然后写回数据库。
Here is sqlFiddleto play with.
这是sqlFiddle可以玩。
