Zenn
🌵

MySQL で カンマ区切り文字列 で IN 句 検索したい時は FIND_IN_SET が使えることを知っておこう

2021/09/22に公開
MySQL
Rails
Rails
tech

カンマ区切り文字列 で IN 句 検索したい

IN句での検索といえば

-- これは動く
SELECT * FROM foo WHERE bar IN (1,2,3)

こんな感じですが、なんらかの変数の値や
他のカラムに保存されているカンマ区切りの情報（そんな設計が悪いとかは今は置いとく）で
検索したい！となっても

-- これは動かない
SELECT * FROM foo WHERE bar IN ('1,2,3')

こうなるので、できませんね。
そんな時は FIND_IN_SET が使えます。

つまりこうです。これは動く！
※パフォーマンスまでは調べていないので、プロダクションコードで扱うのであれば各自調べてください

-- これは動く!
SELECT * FROM foo WHERE FIND_IN_SET(bar, '1,2,3')

応用： Ruby on Rails の　serialize を使って保存されたカラムで IN 検索する

Ruby on Rails の　serialize はカスタムオブジェクト指定することで　JSON　や Hash オブジェクトとして情報を保存してくれますが、カスタムオブジェクトを指定しなかった場合 DB のカラムには yaml で保存されます。
なんでそんなもん使ってんのって感じですが、私もそう思います。
様々な事情でそういうものに出会うこともありますね。

例えばこんな Model があったとして

class Foo < ActiveRecord::Base
  serialize :emails
end

DB にはこんな感じで保存されています。オーマイガー！

mysql> select id,emails from foos;
1|---
- hoge@example.com
- fuga@example.com
- piyo@example.com

で、この emails を使って別のテーブル、例えば

mysql> select id,email from emails;
1|hoge@example.com
2|fuga@example.com
3|piyo@example.com

を IN 句 検索したい時にめっちゃ困ります。ですが、 FIND_IN_SET が助けてくれます。

こんな感じですね。
まあこんなことやらねーよって感じはありますが……。
何かの調査とかで必要になることもあると思います。

SELECT
  id
, (
   SELECT COUNT(*)
   FROM emails
   WHERE
     FIND_IN_SET(
       email
       REPLACE(SUBSTRING(REPLACE(REPLACE(foos.emails, '\n', ''),'---',''),3),'- ',',')
     )
   ) as `exists_email_count`
FROM foos

Rails の serialize 参考：

今回はここまでです。じゃ！

Discussion