Home >Backend Development >PHP Tutorial >Multiple values ​​in one field, separated by commas, retrieve all IDs containing a certain value in this field of the data table

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

不言
不言Original
2018-05-24 15:40:433495browse

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn