FIND_IN_SETでカンマ区切りの文字列から簡単検索
以下のような testtable テーブルについて、
カンマ区切りになっている文字列 strings を検索したい、と思うケースがあります。
あくまでカンマで区切ったひとつひとつをデータとして扱い、例えば「a」ではヒットさせたくないけど「abc」でヒットさせたい、という感じですね。
| id | strings |
| 1 | 1,2,abc,def |
| 2 | 1,3,abc,ghi, |
| 3 | ,,, |
検索が発生する時点で既にこの設計は破綻しているのですが、
現実問題として「じゃあテーブル分けましょう！」と簡単にいかないことも多いので
そういうときは、MySQLであれば以下の関数を用いて検索できます。
FIND_IN_SET(検索する値, 検索対象の文字列・カラム);
主な挙動はこんな感じ。
-- SELECT * FROM `testtable` 以下 WHERE FIND_IN_SET('1' , `strings`); -- id:1, id:2 がヒット WHERE FIND_IN_SET(1 , `strings`); -- id:1, id:2 がヒット（数字は数値でもOK！） WHERE FIND_IN_SET(2 , `strings`); -- id:1 のみヒット WHERE FIND_IN_SET('abc', `strings`); -- id:1, id:2 がヒット WHERE FIND_IN_SET('a' , `strings`); -- ヒットしない WHERE FIND_IN_SET('def', `strings`); -- id:1のみヒット WHERE FIND_IN_SET('' , `strings`); -- id:2, id:3 がヒット WHERE FIND_IN_SET(',' , `strings`); -- ヒットしない
うわー便利。
AND検索・OR検索はいつも通り。
WHERE FIND_IN_SET('abc', `strings`) AND FIND_IN_SET('def', `strings`); -- id:1 のみヒット WHERE FIND_IN_SET('def', `strings`) OR FIND_IN_SET('ghi', `strings`); -- id:1, id:2 がヒット
MySQL独自関数はちょっと…という場合は前後にカンマを付けてLIKE検索。
と言いつつこの書き方もMySQL独自なので、文字列連結方法は各環境に応じてもにょもにょしましょう。
アプリ側でINSERT時に前後にカンマ付けるようにしておいても良いですけどね。
-- 「CONCAT(',', `strings`, ',')」でstringsカラムの前後にカンマを付けた文字列になる -- 「LIKE '%,abc,%'」で「,abc,」の部分一致検索 SELECT * FROM `testtable` WHERE CONCAT(',', `strings`, ',') LIKE '%,abc,%';
雑だけど挙動はだいたい同じです。
-- SELECT * FROM `testtable` 以下 WHERE CONCAT(',', `strings`, ',') LIKE '%,1,%' ; -- id:1, id:2 がヒット WHERE CONCAT(',', `strings`, ',') LIKE '%,2,%' ; -- id:1 のみヒット WHERE CONCAT(',', `strings`, ',') LIKE '%,abc,%'; -- id:1, id:2 がヒット WHERE CONCAT(',', `strings`, ',') LIKE '%,a,%' ; -- ヒットしない WHERE CONCAT(',', `strings`, ',') LIKE '%,def,%'; -- id:1のみヒット WHERE CONCAT(',', `strings`, ',') LIKE '%,,%' ; -- id:2, id:3 がヒット WHERE CONCAT(',', `strings`, ',') LIKE '%,,,%' ; -- id:3のみヒット（ここが違う）
ただしいずれにしろインデックスは効きません。効くわけない。
レコード数や実行頻度と相談して、ご利用は計画的に。
テーブルを分けることができないならフリーワード検索を応用した方がまだ良いかと思います。
（こちらはFULLTEXTインデックスを設定してればちゃんと効く）
