比较mysql中两个逗号分隔的值
问题描述：
我有一个变量，其中包含逗号分隔的值'1,2,3'
I have a variable which contains comma separated value '1,2,3'
我的桌子如下
id favourite_id
1 2,5,6
2 3,5,7
3 6,1,3
4 5,6,7
我想对照favourite_id列检查我的变量，以发现favourite_id中至少有一个常见的值.所以我想要如下所示的mysql查询输出
I want to check my variable against favourite_id column to find at least one value is common in favourite_id. so I want output of mysql query as below
id favourite_id
1 2,5,6
2 3,5,7
3 6,1,3
我知道这不是规范化的表结构，但是我无法更改数据库结构.我在Google上搜索了很多，但是找不到合适的解决方案.
I know that this is not normalized table structure but I am not able to change my database structure.I have googled a lot but could not found suitable solution.
答
使用mysql的REGEXP找到了两个解决方案
Found two solution with the use of REGEXP of mysql
(1)
`favourite_id` REGEXP '[[:<:]]1[[:>:]]|[[:<:]]2[[:>:]]|[[:<:]]3[[:>:]]' //faster then below
(2)
`favourite_id` REGEXP '(^|,)(1|2|3)(,|$)' //slower then above