今回MySQL5.7.22を用いて検証しています。
初期設定
データベース作成
mysql> CREATE DATABASE fts;
Query OK, 1 row affected (0.00 sec)
mysql> use fts;
Database changed
テーブル作成
テーブル作成時に全文検索をするカラムの型をFULLTEXTにし、パーサーにngramを指定します。
mysql> CREATE TABLE documents (id SERIAL PRIMARY KEY, content VARCHAR(255), FULLTEXT(content) WITH PARSER ngram) CHARACTER SET utf8;
Query OK, 0 rows affected (0.10 sec)
レコード挿入
レコードの挿入は通常の文字列同様に入れられます。
mysql> INSERT INTO documents (content) VALUES ('すもももももももものうち'), ('おおきなももがどんぶらこ、どんぶらことながれてきました');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
検索
検索ではMATCH AGAINST関数を使用します。
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('どんぶらこ');
+----+-----------------------------------------------------------------------------------+
| id | content |
+----+-----------------------------------------------------------------------------------+
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました |
+----+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
また、search_modifierの部分にこれら４つが指定でき、このMODEによって検索結果がかなり異なってきます。
- IN NATURAL LANGUAGE MODE
- IN BOOLEAN MODE
- WITH QUERY EXPANSION
- IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
IN NATURAL LANGUAGE MODE
何も指定しないとデフォルトでこれが指定されます。 このモードでは類似性を計算して高いものから順番に返されます。
計算結果を表示するにはこの様に指定します（これはSELECTで表示しているだけなので類似性の高い順ではありません）
mysql> SELECT *, MATCH (content) AGAINST ('どんぶらこ' IN NATURAL LANGUAGE MODE) as score FROM documents;
+----+-----------------------------------------------------------------------------------+--------------------+
| id | content | score |
+----+-----------------------------------------------------------------------------------+--------------------+
| 1 | すもももももももものうち | 0 |
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました | 0.7249524593353271 |
+----+-----------------------------------------------------------------------------------+--------------------+
2 rows in set (0.00 sec)
scoreの部分には0以上の浮動小数点が返ってきます。また、高い値ほど類似度が高くなります。
検索するとこんな結果が出ました。
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('どんぶらこ' IN NATURAL LANGUAGE MODE);
+----+-----------------------------------------------------------------------------------+
| id | content |
+----+-----------------------------------------------------------------------------------+
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました |
+----+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
IN BOOLEAN MODE
このモードは厳密に複数の条件をつけて検索を行うことができます。
この例の様にNATURAL LANGUAGE MODEでは似ているものに関しては検索に引っかかりますが、
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('すもも' IN NATURAL LANGUAGE MODE);
+----+-----------------------------------------------------------------------------------+
| id | content |
+----+-----------------------------------------------------------------------------------+
| 1 | すもももももももものうち |
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました |
+----+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
IN BOOLEAN MODEでは完全に一致するものしか検索に引っかかりません。
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('すもも' IN BOOLEAN MODE);
+----+--------------------------------------+
| id | content |
+----+--------------------------------------+
| 1 | すもももももももものうち |
+----+--------------------------------------+
1 row in set (0.00 sec)
また、複雑な条件も指定できます。この例では「もも」は含むが「すもも」は含まないレコードを検索します。
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('+もも -すもも' IN BOOLEAN MODE);
+----+-----------------------------------------------------------------------------------+
| id | content |
+----+-----------------------------------------------------------------------------------+
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました |
+----+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
その他にも色々な条件が指定できるので知りたい方はこちらを参照ください。
WITH QUERY EXPANSION
このモードでは指定した単語を元に検索し、そこから得たレコード内の関連が高そうな単語を再度検索し直します。 これによってより曖昧な検索をすることができます。
mysql> SELECT * FROM documents WHERE MATCH (content) AGAINST ('どんぶらこ' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
+----+-----------------------------------------------------------------------------------+
| id | content |
+----+-----------------------------------------------------------------------------------+
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました |
| 1 | すもももももももものうち |
+----+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
IN NATURAL LANGUAGE MODEとscoreを比較してみます。
# IN NATURAL LANGUAGE MODE
mysql> SELECT *, MATCH (content) AGAINST ('どんぶらこ' IN NATURAL LANGUAGE MODE) AS score FROM documents;
+----+-----------------------------------------------------------------------------------+--------------------+
| id | content | score |
+----+-----------------------------------------------------------------------------------+--------------------+
| 1 | すもももももももものうち | 0 |
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました | 0.7249524593353271 |
+----+-----------------------------------------------------------------------------------+--------------------+
2 rows in set (0.00 sec)
# IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
mysql> SELECT *, MATCH (content) AGAINST ('どんぶらこ' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION) AS score FROM documents;
+----+-----------------------------------------------------------------------------------+----------------------------+
| id | content | score |
+----+-----------------------------------------------------------------------------------+----------------------------+
| 1 | すもももももももものうち | 0.000000013201498560988512 |
| 2 | おおきなももがどんぶらこ、どんぶらことながれてきました | 2.265476942062378 |
+----+-----------------------------------------------------------------------------------+----------------------------+
2 rows in set (0.01 sec)
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
これは「WITH QUERY EXPANSION」と同じです。
インデックスの内容
最後に21.29.22 INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE テーブルを参考に作られたインデックスを確認してみます。
mysql> SET GLOBAL innodb_ft_aux_table = 'fts/documents';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 、ど | 3 | 3 | 1 | 3 | 36 |
| うち | 2 | 2 | 1 | 2 | 30 |
| おお | 3 | 3 | 1 | 3 | 0 |
| おき | 3 | 3 | 1 | 3 | 3 |
| がど | 3 | 3 | 1 | 3 | 18 |
| がれ | 3 | 3 | 1 | 3 | 60 |
| きな | 3 | 3 | 1 | 3 | 6 |
| きま | 3 | 3 | 1 | 3 | 69 |
| こ、 | 3 | 3 | 1 | 3 | 33 |
| こと | 3 | 3 | 1 | 3 | 51 |
| した | 3 | 3 | 1 | 3 | 75 |
| すも | 2 | 2 | 1 | 2 | 0 |
| てき | 3 | 3 | 1 | 3 | 66 |
| とな | 3 | 3 | 1 | 3 | 54 |
| どん | 3 | 3 | 1 | 3 | 21 |
| どん | 3 | 3 | 1 | 3 | 18 |
| なが | 3 | 3 | 1 | 3 | 57 |
| なも | 3 | 3 | 1 | 3 | 9 |
| のう | 2 | 2 | 1 | 2 | 27 |
| ぶら | 3 | 3 | 1 | 3 | 27 |
| ぶら | 3 | 3 | 1 | 3 | 18 |
| まし | 3 | 3 | 1 | 3 | 72 |
| もが | 3 | 3 | 1 | 3 | 15 |
| もの | 2 | 2 | 1 | 2 | 24 |
| もも | 2 | 3 | 2 | 2 | 3 |
| もも | 2 | 3 | 2 | 2 | 3 |
| もも | 2 | 3 | 2 | 2 | 3 |
| もも | 2 | 3 | 2 | 2 | 3 |
| もも | 2 | 3 | 2 | 2 | 3 |
| もも | 2 | 3 | 2 | 2 | 3 |
| もも | 2 | 3 | 2 | 2 | 3 |
| もも | 2 | 3 | 2 | 3 | 12 |
| らこ | 3 | 3 | 1 | 3 | 30 |
| らこ | 3 | 3 | 1 | 3 | 18 |
| れて | 3 | 3 | 1 | 3 | 63 |
| んぶ | 3 | 3 | 1 | 3 | 24 |
| んぶ | 3 | 3 | 1 | 3 | 18 |
+--------+--------------+-------------+-----------+--------+----------+
37 rows in set (0.00 sec)