在以逗号分隔的字符串mysql中查找值 [英] Find value in comma delimited string mysql

问题描述

我的数据库中有一个表，如下所示:

I have a table in my DB that looks like so:

member_id    name    mgroup_others    member_group_id
    1        Name1      2,3,10              1
    2        Name2      4,5,10              1
    3        Name3      6,7,10              1
    4        Name4       8,9                1

我需要创建一个SELECT语句，该语句选择在'mgroup_others'逗号分隔列表中指定整数的所有成员.因此，例如，我编写了一条SQL语句，该语句将选择'mgroup_others'包含'10'的所有成员，并且它将返回成员1、2和3但不返回成员4，因为10不在该字段中.任何帮助将不胜感激！谢谢.我也是用PHP编写的.

I need to create a SELECT statement that selects all members WHERE a specified integer is in the 'mgroup_others' comma demented list. So for example, I would write a SQL statement that would select all members where 'mgroup_others' contains '10' and it would return member 1, 2 and 3 but not 4 since 10 is not in that field. Any help would be greatly appreciated! Thanks. I am writing this in PHP as well.

推荐答案

由于mgroup_others是varchar列，如果仅使用LIKE运算符而不是FIND_IN_SET()之类的

Since mgroup_others is a varchar column what if you simply use LIKE operator other than FIND_IN_SET() like

select * from table1
where mgroup_others like '%10'

您也可以像下面一样使用FIND_IN_SET()

You can as well use FIND_IN_SET() like below

select * from table1
where find_in_set(10,mgroup_others)

在此处查看演示小提琴 http://sqlfiddle.com/#!2/034711/2

See a demo fiddle here http://sqlfiddle.com/#!2/034711/2

这篇关于在以逗号分隔的字符串mysql中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！