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
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)
like method obviously does not work
Simple and beautiful The sql statement
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.
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)
like method obviously works It doesn’t make sense
Concise and beautiful sql statement
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