MySQLでカンマ区切りのデータの中から検索する関数(FIND_IN_SET, REPLACE)

前提条件

以下のようなテーブルがある。

users

id name tags
1 Sato 1,2,4,10,11
2 Suzuki 3,5
3 Takahashi 2,3,12
4 Tanaka 1,8,11,12

 
(SQLの構造的にはあまりよろしくないのだが)
usersテーブルのtagsに、それぞれのユーザーに関するタグ番号をカンマ区切りで保存している。

これらを検索し、タグ毎にユーザーを抽出してくることになった。
今回はその方法を

  • LIKE関数
  • FIND_IN_SET

の二つに関して比較していく。

 

検索方法

LIKE

SELECT * FROM users WHERE tags LIKE "%2%"

 
これは適切な方法ではない。

  • 2以外に12など、2を含む違う値も取れてしまう
    • これを防ぐためには %2, %,2,% %,2% などそれぞれの条件で書き並べる?
      • 冗長的であまり現実的ではない
  • 速度的に遅い

などの問題が予想されるからだ。

そのため、今回のケースでは FIND_IN_SET を使うのが望ましい。

 

FIND_IN_SET

SELECT * FROM users WHERE FIND_IN_SET(2, tags)

FIND_IN_SETを利用する際の注意事項は以下の通り

  • カンマ,で区切られた文字列にのみ利用できる関数
  • 最初の引数にカンマ,が含まれる場合、正しく動作しない。

つまりFIND_IN_SETを使うことで、tagsのカンマ区切りの文字列の中から適切に引数を検索することができるようになる。

 
ちなみに返り値は 1からN までの範囲の値になるため、

SELECT FIND_IN_SET(2, tags) as tag_position FROM users

としたとき、

  • id = 1 のとき、tag_position = 2
  • id = 3 のとき、tag_position = 1

となる。

tags内に引数がない場合、もしくはtagsが空の場合、返り値は 0。
引数のいずれかがNULLの場合、返り値は NULL。

 

FIND_IN_SET + REPLACE

先ほどの注意事項でも述べた通り、FIND_IN_SETはカンマ区切りの文字列にのみ利用ができる。
もしカンマではなく、半角スペースやバーティカルバーで区切られていた場合、一度カラムの値を置換する必要がある。

// バーティカルバーをカンマに置き換える  
// 出力結果)a,b,c  
SELECT REPLACE('a|b|c', '|', ',') ;

今回でいくと、

SELECT * FROM users WHERE FIND_IN_SET(2, REPLACE(tags, '|', ','))

をするとうまく2のtagを含んでいる行を取ってくることができるだろう。

 

参考