MySqlでFULLTEXT INDEXを利用する場合の形式

FULLTEXT INDEX　とは

FULLTEXT INDEXを作成したとき、対象のカラムの文字列を部分分割しする

この分割した文字に対してインデックスが作られます。文字列全体に対して、INDEXを作成しているわけではなく

英語は単語ごとつまりスペース区切りです。日本語や中国語はそういった区切りがないため、ngram paser で n 文字に分割する。

形式　以下三種類の形式がある

・IN NATURAL LANGUAGE MODE
自然言語の検索 (IN NATURAL LANGUAGE MOD)
検索対象と検索文字列との類似性を計算し、高い順に順番に返す

・IN BOOLEAN MODE
ブール検索 (IN BOOLEAN MODE)
特殊な演算子など (+, -, *) で、厳密な条件を付けて複数の単語をAND検索やOR検索できる
完全に一致するものしか返されない

・WITH QUERY EXPANSION

クエリ拡張検索 (WITH QUERY EXPANSION)
このモードでは指定した単語を元に検索し、返されたレコード内で最も関連が高い単語を再度検索し直す

以下のテーブルを作成

CREATE TABLE test_companies (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      name TEXT,
      FULLTEXT (name) WITH PARSER ngram
) DEFAULT CHARSET = utf8 ENGINE=InnoDB;

INSERT INTO test_companies (name) VALUES
    ('トヨタ'),
    ('トヨタ電気'),
    ('ヤマダ商事'),
    ('ヤマダ法律事務所'),
    ('スバル'),
    ('ヤマダ電気'),
    ('ヤマモト電気'), 
    ('ヤマナカ電気');

検索例

IN NATURAL LANGUAGE MODE

SELECT * FROM test_companies 
WHERE MATCH (name) AGAINST ('ヤマダ' IN NATURAL LANGUAGE MODE);

----+--------------------------+
| id | name                     |
+----+--------------------------+
|  2 | ヤマダ商事               |
|  3 | ヤマダ法律事務所         |
|  5 | ヤマダ電気               |
|  6 | ヤマモト電気             |
|  7 | ヤマナカ電気             |
+----+--------------------------+
5 rows in set (0.01 sec)

ヤマナカ電気が帰ってくるが
類似性の低いので結果は下位
ヤマダに関連性のあるものが返される。

IN BOOLEAN MODE

SELECT * FROM test_companies 
WHERE MATCH (name) 
AGAINST ('ヤマダ電' IN BOOLEAN MODE);

----+-----------------+
| id | name            |
+----+-----------------+
|  5 | ヤマダ電気      |
+----+-----------------+
1 row in set (0.01 sec

完全一致のためヤマダ電機のみが結果となる

WITH QUERY EXPANSION

SELECT * FROM test_companies 
WHERE MATCH (name) 
AGAINST ('ヤマダ' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);

----+--------------------------+
| id | name                     |
+----+--------------------------+
|  3 | ヤマダ法律事務所         |
|  6 | ヤマモト電気             |
|  7 | ヤマナカ電気             |
|  2 | ヤマダ商事               |
|  5 | ヤマダ電気               |
|  8 | トヨタ電気               |
+----+--------------------------+
6 rows in set (0.01 sec)

IN NATURAL LANGUAGE MODEでの結果から関連の高い単語を再検索しているため

IN NATURAL LANGUAGE MODEと結果が変わる

用途

企業等を検索する場合
WITH QUERY EXPANSIONで近しいものを調べる場合に利用するのが良いかも？

追加したFULLTEXT INDEX を確認する方法

SELECT * from INFORMATION_SCHEMA.INNODB_INDEXES limit 1000;

show index in test_companies;

Index_typeを確認できる

etc

既存テーブルに追加する場合　に発生する警告

ALTER TABLE test_companies ADD FULLTEXT INDEX ngram_idx (name) WITH PARSER ngram;

SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+

ALTER TABLE を使用して、FTS_DOC_ID カラムが存在しないテーブルに全文インデックスを追加するとき
FTS_DOC_ID 　がないので
Warning　で作成自動でおこなっている

CREATE TABLE の実行時に全文インデックスを作成する場合に、FTS_DOC_ID カラムを定義しないと、InnoDB によって警告なしで、非表示の FTS_DOC_ID カラムが追加されます。
上記と同じものが追加される