Multiple values ​​in one field, separated by commas, retrieve all IDs containing a certain value in this field of the data table

Example:

id   user_id   
1    2,32,4
2    3,63,2
3    4,62,7

Extract the id that contains 2 in user_id, the result =》1,2 (containing a separate 2)

1. like method obviously does not work

2. Simple and beautiful The sql statement

3. I saw someone big downstairs saying that the field structure should be changed, which may not be allowed at the moment. Because the entire project is open, if I make a move in this place and this place is called in other places, unpredictable problems may occur due to changes in the field structure.

Reply content:

Example:

id   user_id   
1    2,32,4
2    3,63,2
3    4,62,7

Extract the id that contains 2 in user_id, the result =》1,2 (containing a separate 2)

1. like method obviously works It doesn’t make sense

2. Concise and beautiful sql statement

3. I saw someone downstairs big said about changing the field structure, which may not be allowed at the moment. Because the entire project is open, if I make a move in this place and this place is called in other places, unpredictable problems may occur due to changes in the field structure.

SELECT id FROM table WHERE find_in_set('2',user_id);

id user_id
1 ,2,32,4,
2 ,3,63,2,
3 ,4,62,7,

If you change it to this, it will be easy to do like.

In fact, it is better to divide it into intermediate tables

Table product

id      
1    
2   
3

Table user

user_id   
2
3
4
6
32   
62
63

Intermediate table

product_id   user_id

1               2
1               32
1               4
2               2
2               63
2               3
3               4
3               62
3               7

I still recommend you to change the table structure! This is really not easy to do. Or change the data storage method like

id   user_id   
1    [2],[32],[4]
2    [3],[63],[2]
3    [4],[62],[7]

and use fuzzy query. However, it is still recommended that you modify the table data

mysql5.7 has json type