【MySQL】カンマ区切りデータを扱う

Tweet

Category MySQL 2012/06/29

問題

テキスト型のカラムにカンマ区切りのデータが入っているらしいんだけど、like検索するしかないかな？

答え　１

MySQLならでは。FIND_IN_SET関数がある。

FIND_IN_SET(str, strlist)

N 個の部分文字列で構成されるリスト strlist に、文字列 str が含まれている場合は、1 から N までのいずれかの値を返す。文字列のリストは、それぞれの間を ‘,’ 文字で区切られた各部分文字列で構成される文字列である。最初の引数が定数文字列で、2 番目の引数が SET 型のカラムの場合、FIND_IN_SET() 関数はビット演算を使用するよう最適化される。 str が strlist に含まれていない場合や、strlist が空の文字列の場合は、0 を返す。どちらの引数も NULL の場合は、NULL を返す。 最初の引数にカンマ ‘,’ が含まれていると、この関数は正しく動作しない。

mysql> select find_in_set('b', 'a,b,c,d');
+-----------------------------+
| find_in_set('b', 'a,b,c,d') |
+-----------------------------+
|                           2 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('ab', 'abb,abbb,abc,ab,abcc');
+-------------------------------------------+
| find_in_set('ab', 'abb,abbb,abc,ab,abcc') |
+-------------------------------------------+
|                                         4 |
+-------------------------------------------+
1 row in set (0.00 sec)

答え　２

MySQL独自関数を避けて、like検索で対応する。

SELECT * FROM T WHERE col1 LIKE ‘%,b,%’; と書くと、先頭と末尾のデータに対してうまくいかなくなるので、先頭と末尾にも区切り文字（カンマ）を付けて、以下のようにすると、検索はできる。

mysql> SELECT * FROM T WHERE CONCAT(',', col1, ',') LIKE '%,b,%';

検索のときに前後にカンマを付けるのではなく、最初からカンマを付けて格納しておいてもよい。

答え1にしても答え2にしても、パフォーマンスがとてもよいわけではないので、速度が重要なら設計を考えるべきかも。データが少ないテーブルに対してや、たまに使う程度ならよいかもしれません。