MySQL WHERE IN ()

Question

My request:

SELECT * FROM table WHERE id IN (1,2,3,4); 

I use it for user groups, and a user can be in multiple groups. but it seems that when a record has multiple identifiers like 1 and 3, mySQL does not return this string.

Is there any way to get this string?

Answer 1

Your request is being translated into

 SELECT * FROM table WHERE id='1' or id='2' or id='3' or id='4'; 

He will return only those results that correspond to him.

---

One way to solve the problem of avoiding complexity would be to convert the data type to SET . Then you can use FIND_IN_SET

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

Answer 2

You have the wrong database design, and you should take some time to read something about normalizing the database ( wikipedia / https://stackoverflow.com/a/166269/ ).

I assume your table looks something like this:

 TABLE ================================ | group_id | user_ids | name | -------------------------------- | 1 | 1,4,6 | group1 | -------------------------------- | 2 | 4,5,1 | group2 | 

therefore, in your user group table, each row represents one group and in the user_ids column you have a set of user IDs assigned to this group.

The normalized version of this table will 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 an assigned group, or all users in a group, or all user groups, or whatever you think. In addition, 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 easier to update the database, if you want to add a new assignment, just just insert a new row in group_user_assignment , when you want to delete the assignment, you just delete the row in group_user_assignment .

In your database project, in order to update assignments, you will need to get your job set from the database, process it and update it, and then write it back to the database.

Here's sqlFiddle to play with.