IN()の代わりにFIND_IN_SETを使ってMySQLの1つのフィールド内にあるカンマ区切り値を検索する方法

IN()の代わりにFIND_IN_SETを使用する

MySQLで1つのフィールドに格納されたカンマ区切りの値を検索したい場合、IN()ではなくFIND_IN_SET関数を使用します。FIND_IN_SETは、カンマ区切りの文字列リストの中から指定した値を検索し、見つかった位置（1始まり）を返す関数です。値が存在しない場合は0を返します。

サンプルテーブルの作成

まず、以下のコマンドでテーブルを作成しましょう。

mysql> create table DemoTable
   -> (
   -> ListOfValues text
   -> );
Query OK, 0 rows affected (0.56 sec)

レコードの挿入

次に、INSERTコマンドを使ってレコードを挿入します。ここでは、パイプ記号（|）で区切られた値を格納している点に注目してください。

mysql> insert into DemoTable values('10|20|30|40|50|60|100');
Query OK, 1 row affected (0.18 sec)

テーブルの内容を確認する

SELECT文ですべてのレコードを表示してみます。

mysql> select *from DemoTable;

実行結果は以下の通りです。

+-----------------------+
| ListOfValues          |
+-----------------------+
| 10|20|30|40|50|60|100 |
+-----------------------+
1 row in set (0.00 sec)

FIND_IN_SETで区切り値を検索する

以下が、1つのフィールド内の区切り値を検索するクエリです。FIND_IN_SETはカンマ区切りの文字列のみを扱えるため、REPLACE関数でパイプ記号（|）をあらかじめカンマ（,）に変換してから検索を行っています。

mysql> select FIND_IN_SET(100, replace(ListOfValues,'|',',')) from DemoTable;

実行結果：

+-------------------------------------------------+
| FIND_IN_SET(100, replace(ListOfValues,'|',',')) |
+-------------------------------------------------+
|                                               7 |
+-------------------------------------------------+
1 row in set (0.04 sec)

実行結果の解説

結果として返された「7」は、検索した値「100」がリストの7番目に存在することを示しています。もし該当する値がリスト内に存在しなければ、0が返されます。

この手法は、正規化されていないカンマ区切り（またはその他の区切り文字）形式のデータを手軽に検索したい場合に便利です。ただし、区切り文字をREPLACEで変換する処理が加わるため、大量のデータを扱う場合や頻繁に検索を実行する場合は、インデックスが利用できずパフォーマンスに影響が出る可能性がある点には注意しましょう。可能であれば、中間テーブルへの正規化を検討するのが望ましい設計です。