Posted in MySQL

[MySQL]1カラムに登録されたカンマ区切りデータを検索・抽出する方法（FIND_IN_SET）

   Published Date: 2015年12月10日

MySQLのひとつのカラムにカンマ区切りでデータが入っている時にAND検索やOR検索をする方法です。
本来はテーブルを複数に分けるべきでしょうが、簡単なタグシステムを作ったり、複数のカテゴリーで検索をかけたりする時に便利なので、覚えておいて損はないと思います。

MySQLにはカンマ区切り検索をする独自の関数「FIND_IN_SET()」が用意されているのでこの関数を使用します。

MySQLのマニュアルを見ると下記のように難しい言葉が羅列されていますが、使ってみるとなるほど！と感動する関数でした。

FIND_IN_SET(str,strlist)

文字列 str が N 部分文字列で構成される文字列リスト strlist 内にある場合は、1 から N までの範囲内の値を返します。文字列リストは、「,」 文字で区切られた部分文字列で構成された文字列です。最初の引数が定数文字列で、2 番目が SET 型のカラムの場合、FIND_IN_SET() 関数はビット演算を使用するために最適化されます。str が strlist 内にない場合、または strlist が空の文字列の場合は、0 を返します。引数のいずれかが NULL である場合は、NULL を返します。最初の引数にカンマ (「,」) 文字が含まれる場合は、この関数が正しく動作しません。

MySQL 5.6 リファレンスマニュアル

全データ

postテーブルに以下のようにデータが登録されています。
本ブログ最後にテスト用のデータ（SQL文）を掲載しておきますので、テストしたい方はそちらのデータをご利用ください。

nameカラムにテスト名などが入っており、tagカラムに教科（国語・数学など）がカンマ区切りで入力されています。今回はこのtagカラムのカンマ区切りデータに対し検索・抽出を行います。

MySQL FIND_IN_SET　全データ

１つのキーワードで抽出

カンマ区切りデータの中に「国語」という文字が入っているレコードを抽出します。
FIND_IN_SETの書式は以下のようになっています。

書式
FIND_IN_SET(‘検索する文字列’,フィールド名) 
SELECT * FROM `post` WHERE FIND_IN_SET('国語',tag)

上記のようなSQL文を実行すると、tagカラムの中に「国語」という文字が含まれるレコードが抽出されます。
MySQL FIND_IN_SET

複数のキーワードでOR検索

tagカラムの中に「国語」または「算数」が含まれるデータを抽出します。

SELECT * FROM `post` WHERE FIND_IN_SET('国語',tag) OR FIND_IN_SET('算数',tag)

「国語」または「算数」を含むレコードがちゃんと抽出されていますね。
MySQL FIND_IN_SET OR検索

複数のキーワードでAND検索

tagカラムの中に「国語」と「算数」が含まれるレコードを抽出します。

SELECT * FROM `post` WHERE FIND_IN_SET('国語',tag) AND FIND_IN_SET('算数',tag)

こちらも希望通りのレコードが抽出されました。
MySQL FIND_IN_SET AND検索

ひとつのカラム（フィールド）に複数のキーワードを登録しておいて検索したい、という場面は多いのですが、この関数を知っておくと短時間で機能を実装することができます。レコード数が多いと検索が遅くなるなどのマイナス面もありますが、規模の小さいシステムにでは力を発揮してくれます。

MySQLデータ

テスト用にご利用ください。

--
-- テーブルの構造 `post`
--

CREATE TABLE `post` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `tag` varchar(500) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- テーブルのデータのダンプ `post`
--

INSERT INTO `post` (`id`, `name`, `tag`) VALUES
(1, '朝のテスト', '国語,算数'),
(2, '昼のテスト', '国語,算数,理科,英語'),
(3, '研究テーマ', '算数'),
(4, '夏季テスト', '算数,英語'),
(5, '冬季テスト', '英語,理科');


Author: webmaster