MySQLでFULLTEXT検索方法について

概要

• mysqlの結合したテーブルからFULLTEXT検索方法について調べてみた。

実行環境

• MySql 5.8

背景

• MySQLバージョン5.7からInnoDBの全文検索機能搭載
• N-gram使える
• INDEX追加

データ準備

• テーブル作成

create_table

-- 部門
CREATE TABLE dep (
  depid BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  depname TEXT
) ENGINE=InnoDB CHARACTER SET utf8mb4;
-- 社員
CREATE TABLE emp (
  empid BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  depid BIGINT,
  empname TEXT
) ENGINE=InnoDB CHARACTER SET utf8mb4;

• データ登録

-- データ登録
INSERT INTO dep (depname) VALUES  ('市場部'),  ('市場リスク部'),  ('開発部'),  ('会場部');
INSERT INTO emp (depid,empname) VALUES  (1,'山田次郎'),  (1,'山田泰朗'),  (2,'高橋日本'),  
(3,'日本山田'),  (3,'中村次郎'), (4,'高橋次郎');
-- INDEX作成
ALTER TABLE dep ADD FULLTEXT INDEX ngram_idx (depname) WITH PARSER ngram;
ALTER TABLE emp ADD FULLTEXT INDEX ngram_idx (empname) WITH PARSER ngram;

• 正常検索SQLを実行すると以下の結果

select emp.empname,dep.depname from emp inner join dep on emp.depid = dep.depid where emp.empname like '%山田%'

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148710/7774d938-461c-a419-320a-aeb6240c1071.png)

* FULLTEXT検索SQLを実行すると以下の結果１

```sql
select emp.empname,dep.depname from emp inner join dep on emp.depid = dep.depid 
where MATCH (empname) AGAINST ('山田');

• FULLTEXT検索SQLを実行すると以下の結果２

select emp.empname,dep.depname from emp inner join dep on emp.depid = dep.depid 
where MATCH (empname) AGAINST ('山田') and
MATCH (depname) AGAINST ('市場');

その他

• 一つのテーブルの複数カラムをFULLTEXTとして検索したい場合以下のようにINDEXを作成する

ALTER TABLE emp ADD FULLTEXT INDEX ngram_idx (depid,empname) WITH PARSER ngram;

• その場合の検索方法は

select emp.empname,dep.depname from emp inner join dep on emp.depid = dep.depid 
where MATCH (depid,empname) AGAINST ('山田');

終了