mysql5.6 正規表現 カンマ区切りのデータから、条件が一致したレコードを取得する

Question

前提・実現したいこと

正規表現を使って、カンマ区切りのデータから、条件が一致したレコードを取得したいのですが上手くいきません。

レコードはこんな感じでデータベースに入っています。

no text
1 1
2 1,2,3
3 10,11
4 1,10,12
5 10,11,12
6 2
7 10
8 12

下記のsqlを使用しています。
SELECT * FROM master WHERE text REGEXP '(1)'　を実行した時

1 1
2 1,2,3
3 10,11
4 1,10,12
5 10,11,12
7 10
8 12

が取得されます。
1が含まれるレコード全てが取得されます。

この時取得したいレコードは、1が含まれるレコード

1 1
2 1,2,3
4 1,10,12

です。

また、 2が含まれるレコード

2 1,2,3
6 2

が取得できるようにしたいです。

どのように実現したらいいでしょうか？

試したこと

① SELECT * FROM master WHERE text REGEXP '(1,)'

2 1,2,3
4 1,10,12

が取得できますが、
1 1
が抜けてしまいます。

② SELECT * FROM master WHERE text REGEXP '(1)' AND text NOT LIKE '%10%'

1 1
2 1,2,3
が取得できますが、
4 1,10,12
が抜けてしまいます。

補足情報（FW/ツールのバージョンなど）

mysql5.6

Answer 1

正規表現ですから「先頭か,から始まり、その数字、,か末尾で終わる」
と書きます。

create table tbl(no int,text varchar(20));
insert into tbl values
(1,'1'),
(2,'1,2,3'),
(3,'10,11'),
(4,'1,10,12'),
(5,'10,11,12'),
(6,'2'),
(7,'10'),
(8,'12');

• 1,2,10順番に

select * from tbl where text regexp('(^|,)1(,|$)');
select * from tbl where text regexp('(^|,)2(,|$)');
select * from tbl where text regexp('(^|,)10(,|$)');

でも、カンマ区切りの文字列はfind_in_setで処理できます

select * from tbl where  find_in_set('1',text);
select * from tbl where  find_in_set('2',text);
select * from tbl where  find_in_set('10',text);

効率的なデータの持ち方

ちゃんと正規化するならこうですね

• 元データ

create table id_text(pid int primary key,id int,text int,unique(id,text));
insert into id_text values
(1,1,1),
(2,2,1),
(3,2,2),
(4,2,3),
(5,3,10),
(6,3,11),
(7,4,1),
(8,4,10),
(9,4,12),
(10,5,10),
(11,5,11),
(12,5,12),
(13,6,2),
(14,7,10),
(15,8,12);

• 全データ表示

select id,group_concat(text) as text from id_text
group by id;

• 抽出

text=1を含むデータの抽出

select id,group_concat(text) as text from id_text as t1
where exists(select 1 from id_text where text=1 and id=t1.id)
group by id;

Comments

[hagiwarasannnnn]

find_in_setで実現できました。
カンマ以外の方法も出来るんですね！
別抽出もご丁寧に教えていただきありがとうございます。