在mysql选择查询中匹配分隔符（|）分隔值字段

Question

In my mysql table one of the field has values like (1|2|3) stored in it.

  id   Skills
 ----  --------
   1   1|2|3|5
   2     2|4|5
   3    4|6|3
   4    5|2|3|1

I want to search and list the id's based on the skills matched. If i want to list the id's of the skill which contains 3, i have to list the ids 1,3,4. Like if i want to list the id's of the skill which contains 1,5 (either 1 nor 5 or both)(like mulitple values 1,4,3) then i want to list 1,2,4(i want to list 2 also). Any help could be greatly appreciated.

Thanks in advance.

Answer 1

Use FIND_IN_SET

select id
from your_table
where find_in_set('3', replace(skills, '|',',') > 0

select id
from your_table
where find_in_set('3', replace(skills, '|',',')) > 0
or find_in_set('5', replace(skills, '|',',')) > 0

But you should actually change your DB structure. Always store single values in a column to avoid such problems!