MySQL5.6～で日本語全文検索ができるぞ！！！

f:id:hit10231023:20180309104332j:plain

最近MariaDBばかりやっていて、MySQLの存在を忘れていたのですが、今まで英語しか利用できなかったのに、いつの間にか標準で日本語全文検索ができるようになっていたので、久しぶりに触って見ようと思います。これがまた、意外に簡単！だったので、その設定/実装方法をできるだけ簡単に紹介したいと思います

全文検索とは

あれですね。通常テーブルに設定するカラムの索引ではなく、行全体に対して、インデクシングをする設定といえばいいのかしら

IROTEC（アイロテック）ラバーダンベル 40KGセット（片手20kg×2個）筋トレダイエットダイエット器具トレーニングベンチプレス筋肉

出版社/メーカー: 株式会社スーパースポーツカンパニー
メディア: その他
購入: 2人クリック: 31回
この商品を含むブログを見る

ウィキペディアで見るとこんな感じで書かれております

複数の文書（ファイル）から特定の文字列を検索すること。「ファイル名検索」や「単一ファイル内の文字列検索」と異なり、「複数文書にまたがって、文書に含まれる全文を対象とした検索」という意味

データベースでいうところの、複数カラムにまたがったカラムの中の全文を対象にした検索って言い方になると思います。僕的には、「索引を設定できない大きなサイズのカラムも全文検索索引でさくさく検索できる」と言った意味合いで利用しております。（ざっくりすぎですみません・・）

過去のMySQL、いや、Mariaもそうなのですが、昔から全文検索自体の機能はついていたのですが、こと日本語の全文検索になると。Mroonga等、サードパーティーのエンジンを入れる必要がありました。入れるまでは良いのですが、その後の運用保守が結構めんどくさい・・いや、、Mroongaが使いづらいという話ではなくて、私自身が、複雑な構成をとっていたため、RDBMS本体のバージョンアップ等が大きな壁になっていたんですね。

今回は、日本語全文検索が標準で実装される！！これは大きいと思います。

索引文字列の抽出方法について

MySQLの抽出のデフォルトは、N-Gramになります、N-Gramは、検索対象を単語単位ではなく文字単位で分解し、後続の N-1 文字を含めた状態で出現頻度を求める方法です。インデクシングの速度は速いですが、サイズがでかくなることと検索のノイズ、そして検索速度が遅いというデメリットがあります。しかしながら、単語辞書が必要ないという利点があり、比較的導入しやすいと思います。

実装

前置きはさておき、実装を検討してみます。

今回は、以下のような構成で、全文検索専用のテーブルを作る設計にしてみます。

f:id:hit10231023:20161130134245j:plain

archcleテーブルは、全文検索対応していない通常のテーブル
archcleテーブルの更新に伴い、TRIGGER（トリガ）が発動し、fulltext_archcleテーブルに全文検索エンジン対応のテーブルにデータが書き込まれる

と概要はこんな感じです。バルクインサートなどの処理がないかぎりは、この運用もいけそうです。

テーブル定義

上記図のテーブル定義は以下の通りとなります

article

CREATE TABLE `article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) not null default '0',
  `title` varchar(128) NOT NULL DEFAULT '',
  `detail` text ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

fulltext_article

CREATE TABLE `fulltext_article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) not null default '0',
  `title` varchar(128) NOT NULL DEFAULT '',
  `detail` text ,
  PRIMARY KEY (`id`),
  FULLTEXT INDEX ft01_diaries (title,detail) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

上記のFULLTEXT INDEX がそれに当たります

トリガーの定義

メインのテーブルに更新が入った場合に、トリガが発動します。結果別のテーブルにコピーしているイメージです。

TRIGGER (INSERT)

DROP TRIGGER IF EXISTS article_ins ;

DELIMITER $$ 

CREATE TRIGGER article_ins AFTER INSERT ON article
FOR EACH ROW
BEGIN
  INSERT INTO
       fulltext_article (id,created_at,updated_at,user_id,title,detail)
   VALUES
     (NEW.id,NEW.created_at,NEW.updated_at,NEW.user_id,NEW.title,NEW.detail)
   ON DUPLICATE KEY UPDATE
     created_at = NEW.created_at,
     updated_at = NEW.updated_at,
     user_id = NEW.user_id ,
     title = NEW.title,
     detail = NEW.detail ;

END $$

DELIMITER ;

TRIGGER (UPDATE)

DROP TRIGGER IF EXISTS article_upd ;

DELIMITER $$ 

CREATE TRIGGER article_upd AFTER UPDATE ON article
FOR EACH ROW
BEGIN
  INSERT INTO
       fulltext_article (id,created_at,updated_at,user_id,title,detail)
   VALUES
     (NEW.id,NEW.created_at,NEW.updated_at,NEW.user_id,NEW.title,NEW.detail)
   ON DUPLICATE KEY UPDATE
     created_at = NEW.created_at,
     updated_at = NEW.updated_at,
     user_id = NEW.user_id ,
     title = NEW.title,
     detail = NEW.detail ;
     

END $$

DELIMITER ;

TRIGGER (DELETE)

DROP TRIGGER IF EXISTS article_del ;

DELIMITER $$ 

CREATE TRIGGER article_del AFTER DELETE ON article
FOR EACH ROW
BEGIN
 DELETE FROM fulltext_article WHERE id = OLD.id ;
END $$

DELIMITER ;

データ投入

早速データを投入してみましょう。このデータ投入で、トリガの動作確認も同時に行います。本当はもっといっぱい投入したいけど、今回はテストということで少なめにしております

INSERT INTO article (user_id,title,detail) VALUES
(12345,'三菱自が役員報酬枠を3倍増','三菱自動車は２９日、取締役に支給できる報酬総額の上限枠を３倍の３０億円とする方針を明らかにした。１２月１４日開催の臨時株主総会で正式決定する。三菱自は上限枠の拡大を「社外や海外も含めた優秀な人材の中から取締役を任命できるようにするため」としているが、筆頭株主になった日産自動車のカルロス・ゴーン社長が同日付で三菱自会長に就任予定で、高給取りで知られるゴーン氏の報酬対策という側面もありそうだ。'),
(12345,'任天堂 日米でテーマパーク','任天堂は２９日、大阪市のユニバーサル・スタジオ・ジャパン（ＵＳＪ）内のほか、米国の２カ所に「マリオ」などのキャラクターが登場するエリアをつくると発表した。それぞれ今後数年でオープンする計画だ。') ;

## データ確認

フルテキスト側のテーブルに値が入っているか確認します。正常であれば、二件入っているはずですがどうでしょうか？

mysql> SELECT * FROM fulltext_article \G
*************************** 1. row ***************************
        id: 1
created_at: 2016-11-30 04:07:47
updated_at: 2016-11-30 04:07:47
   user_id: 12345
     title: 三菱自が役員報酬枠を3倍増
    detail: 三菱自動車は２９日、取締役に支給できる報酬総額の上限枠を３倍の３０億円とする方針を明らかにした。１２月１４日開催の臨時株主総会で正式決定する。三菱自は上限枠の拡大を「社外や海外も含めた優秀な人材の中から取締役を任命できるようにするため」としているが、筆頭株主になった日産自動車のカルロス・ゴーン社長が同日付で三菱自会長に就任予定で、高給取りで知られるゴーン氏の報酬対策という側面もありそうだ。
*************************** 2. row ***************************
        id: 2
created_at: 2016-11-30 04:07:47
updated_at: 2016-11-30 04:07:47
   user_id: 12345
     title: 任天堂 日米でテーマパーク
    detail: 任天堂は２９日、大阪市のユニバーサル・スタジオ・ジャパン（ＵＳＪ）内のほか、米国の２カ所に「マリオ」などのキャラクターが登場するエリアをつくると発表した。それぞれ今後数年でオープンする計画だ。

日本語で、全文検索をやってみる

やっあぁぁああっとここまでたどり着きました。早速ですが全文検索をやってみたいと思います！今回はこんなSQLを発行してみます。

１つ目: DETAILに大阪市を含むデータを検索
2つめ：TITLEに役員を含むデータを検索

SQL

mysql> SELECT * FROM fulltext_article WHERE MATCH (title,detail) AGAINST ('大阪市');
+----+---------------------+---------------------+---------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | created_at          | updated_at          | user_id | title                                 | detail                                                                                                                                                                                                                                                                                           |
+----+---------------------+---------------------+---------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  2 | 2016-11-30 04:07:47 | 2016-11-30 04:07:47 |   12345 | 任天堂 日米でテーマパーク             | 任天堂は２９日、大阪市のユニバーサル・スタジオ・ジャパン（ＵＳＪ）内のほか、米国の２カ所に「マリオ」などのキャラクターが登場するエリアをつくると発表した。それぞれ今後数年でオープンする計画だ。                                                                                                 |
+----+---------------------+---------------------+---------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM fulltext_article WHERE MATCH (title,detail) AGAINST ('役員');                                                     
+----+---------------------+---------------------+---------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | created_at          | updated_at          | user_id | title                                 | detail                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+----+---------------------+---------------------+---------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 2016-11-30 04:07:47 | 2016-11-30 04:07:47 |   12345 | 三菱自が役員報酬枠を3倍増             | 三菱自動車は２９日、取締役に支給できる報酬総額の上限枠を３倍の３０億円とする方針を明らかにした。１２月１４日開催の臨時株主総会で正式決定する。三菱自は上限枠の拡大を「社外や海外も含めた優秀な人材の中から取締役を任命できるようにするため」としているが、筆頭株主になった日産自動車のカルロス・ゴーン社長が同日付で三菱自会長に就任予定で、高給取りで知られるゴーン氏の報酬対策という側面もありそうだ。                                                                                                                                                                                                     |
+----+---------------------+---------------------+---------+---------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

みごとに日本語で検索できました！！！！！

今回は、特に何もインストールせず、全文検索ができますよという例で説明しました。実際、ちょっと設定すれば、mcab等の形態素解析エンジンもMySQL5.6以上であれば簡単に設定できます

Database JUNKY

MySQL,MariaDBを中心としたブログです

知ってた！？MySQL5.6以上から日本語全文検索ができるようになったってこと・・

MySQL5.6～で日本語全文検索ができるぞ！！！

全文検索とは

索引文字列の抽出方法について

実装

テーブル定義

article

fulltext_article

トリガーの定義

TRIGGER (INSERT)

TRIGGER (UPDATE)

TRIGGER (DELETE)

データ投入

日本語で、全文検索をやってみる

SQL

MySQL5.6～ で日本語全文検索ができるぞ！！！

全文検索とは

索引文字列の抽出方法について

実装

テーブル定義

article

fulltext_article

トリガーの定義

TRIGGER (INSERT)

TRIGGER (UPDATE)

TRIGGER (DELETE)

データ投入

日本語で、全文検索をやってみる

SQL

MySQL5.6～で日本語全文検索ができるぞ！！！