【MySQL】FIND_IN_SETでカンマ区切りの文字列の中に何があるかを識別する

　しばしば複数の値がカンマ区切り文字列で格納されているデータベースを扱う必要がある時があります。これは例えば次のようなテーブルです。

　ロールが複数あるユーザーがいる場合、そのユーザーのロールはカンマ区切りで格納されています。このような場合、あるロールが含まれているユーザーを検索するには、FIND_IN_SET関数を使います。FIND_IN_SET関数は、カンマ区切りの文字列の中に指定した値が含まれているかどうかを調べる関数です。次のクエリは、ロールが「A」のユーザーを検索する例です。

SELECT * FROM users WHERE FIND_IN_SET('A', roles) > 0;

　このクエリは、rolesカラムの値に「A」が含まれているユーザーを検索します。FIND_IN_SET関数は、指定した値が含まれている場合はその値の位置を返します。含まれていない場合は0を返します。そのため、FIND_IN_SET(‘A’, roles) > 0という条件は、rolesカラムの値に「A」が含まれているユーザーを検索する条件となります。

　FIND_IN_SETの問題点としてインデックスが効かないことがあります。そのため大量のデータを扱う場合はカンマ区切りの文字列を使わずに別の方法でデータを格納することを検討する必要があります。できるならデータベース構造のリファクタリングをしたいところですが破壊的変更や大規模な変更ができない場合もあります。そういった時は計算カラムとインデックスの組合せを用いて小規模な変更で高速な検索を実現する方法もあります。これは次のようにできます。

# is_adminカラムを追加し、rolesカラムに「A」が含まれているかどうかを計算して格納する
ALTER TABLE users ADD COLUMN is_admin BOOLEAN AS (FIND_IN_SET('A', roles) > 0) STORED;
CREATE INDEX idx_is_admin ON users (is_admin);

　このようにすることで、is_adminカラムに「A」が含まれているかどうかのインデックスが効くようになります。これによりrolesカラムの値に「A」が含まれているユーザーを高速に検索することができます。

# AdminユーザーをFIND_IN_SETで検索する
# インデックスが効かない
EXPLAIN SELECT * FROM users WHERE FIND_IN_SET('A', roles) > 0;
# id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
# 1,SIMPLE,users,,ALL,,,,,3,100,Using where

# Adminユーザーをis_adminカラムで検索する
# インデックスが効く
EXPLAIN SELECT * FROM users WHERE is_admin = 1;
# id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
# 1,SIMPLE,users,,ref,idx_is_admin,idx_is_admin,2,const,2,100,

　このように、FIND_IN_SET関数を使ってカンマ区切りの文字列の中に何が含まれているかを調べることができます。ただしインデックスが効かない場合があるため、大量のデータを扱う場合はデータベース構造のリファクタリングや計算カラムとインデックスの組合せを使うなどして高速化した方が無難です。