Multiple values in a field, separated by commas, to remove all IDs of a value in the field of the data table

Lifting chestnuts:

ID   user_id   1    2,32,42    3,63,23    4,62,7

Out of user_id 2 id , result = "2" (contains a separate)

1. The way of like is obviously unworkable.

2. Simple and beautiful SQL statement

3. See downstairs there are big said change the field structure, it may not be allowed to do so at the moment. Because the whole project is going to work, if I move in this place, there are other places to call this place, because the field structure changes, there may be unpredictable problems.

Reply content:

Lifting chestnuts:

ID   user_id   1    2,32,42    3,63,23    4,62,7

Out of user_id 2 id , result = "2" (contains a separate)

1. The way of like is obviously unworkable.

2. Simple and beautiful SQL statement

3. See downstairs there are big said change the field structure, it may not be allowed to do so at the moment. Because the whole project is going to work, if I move in this place, there are other places to call this place, because the field structure changes, there may be unpredictable problems.

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 that, you can do what you like.

Actually, it's better to divide the middle table.

Table Product

ID      1    2   3

Table User

user_id   234632   6263

Intermediate table

product_id   user_id1               321               632               623               7

It is recommended that you make the table more structured! This is really not good to take. or change the way data is stored, such as

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

This can be done with a fuzzy query. However, it is recommended that you modify the table data as well

mysql5.7 JSON type

