MySQL WHERE IN()

我的疑问是:

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.

Is there any way to get that row too?

393960 次浏览
小开

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

SELECT * FROM table WHERE FIND_IN_SET('1', id);
小开

You have wrong database design and you should take a time to read something about database normalization (wikipedia / 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_ids column you have set of user ids assigned to that group.

Normalized version of 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:

/* 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.

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 sqlFiddle to play with.

小开

you must have record in table or array record in database.

example:

SELECT * FROM tabel_record
WHERE table_record.fieldName IN (SELECT fieldName FROM table_reference);

提交答案