MySQLでカンマ区切りの値から特定の数値を含むレコードをすべて選択する方法

MySQLで特定の数値を含むレコードを抽出するには？

カンマ区切りで保存された値の中に、特定の数値が含まれているレコードをすべて取得したい場合は、concat() 関数と LIKE 演算子を組み合わせて使用します。

基本の構文は以下のとおりです。

select *from yourTableName where concat(',', yourColumnName, ',') like '%,yourValue,%';

この方法のポイント

単純に LIKE '%4%' とすると、「14」や「40」など意図しない数値までマッチしてしまう可能性があります。そこで、列の値の前後を concat() でカンマで囲むことで、「4」という数値そのものだけを正確に検索できるようになります。

テーブルの作成

まずはサンプル用のテーブルを作成しましょう。

mysql> create table demo49
-> (
-> id varchar(20),
-> first_name varchar(20)
-> );
Query OK, 0 rows affected (1.45 sec)

レコードの挿入

insertコマンドを使って、テーブルにいくつかのレコードを挿入します。

mysql> insert into demo49 values('4,5,6','Adam');
Query OK, 1 row affected (0.20 sec)

mysql> insert into demo49 values('5,3,2','Mike');
Query OK, 1 row affected (0.19 sec)

mysql> insert into demo49 values('3,4,9','Bob');
Query OK, 1 row affected (0.14 sec)

レコードの表示

select文でテーブル内のレコードを確認します。

mysql> select *from demo49;

上記のクエリを実行すると、次のような結果が出力されます。

+-------+------------+
| id    | first_name |
+-------+------------+
| 4,5,6 | Adam       |
| 5,3,2 | Mike       |
| 3,4,9 | Bob        |
+-------+------------+
3 rows in set (0.00 sec)

特定の数値を含むレコードを選択するクエリ

ここでは、id列に「4」が含まれているすべてのレコードを取得するクエリを実行します。

mysql> select *from demo49 where concat(',', id, ',') like '%,4,%';

実行結果は以下のとおりです。「4」を含む2件のレコードのみが正確に抽出されていることがわかります。

+-------+------------+
| id    | first_name |
+-------+------------+
| 4,5,6 | Adam       |
| 3,4,9 | Bob        |
+-------+------------+
2 rows in set (0.00 sec)

まとめ

カンマ区切りの文字列から特定の数値を検索する場合は、concat() で値をカンマで挟み、LIKE '%,値,%' の形式で比較することで、部分一致による誤検出を防ぎながら正確にレコードを抽出できます。なお、データ量が多い場合や検索性能が重要な場合は、正規化して別テーブルに数値を個別に保存する設計も検討することをおすすめします。