MySQL の FULLTEXT インデックスを使ってブログの全文検索を作った : 犬ターネット

ブログの全文検索を作った↓

実装は MySQL の FULLTEXT インデックスを利用。現時点では全エントリー合わせても1400件弱だし LIKE 検索でもよさそうだけど勉強がてら作ってみた。以下作るまでの手順。

このサイトのブログテーブルは以下のような構成になっている。

CREATE TABLE entry (
  id int(11) PRIMARY KEY,
  title text,
  contents text,
  created datetime,
  ...

通常は同じテーブルに FULLTEXT インデックスカラムを追加するようだけど、検索用のテーブルは別にしたほうが将来的に管理が楽そうだから新たに検索用テーブルを作った↓

create table entry_search (
  id INT(11) PRIMARY KEY,
  title text,
  contents text,
  FULLTEXT (title,contents) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

その後、元のテーブルから検索用テーブルへデータインポートする簡単なプログラムを作って実行。ざっくり以下のような感じ。これでデータ作成完了。

次に検索。検索は以下のようなSQLを実行する必要がある。

SELECT id,title
FROM entry_search
WHERE MATCH(title, contents) AGAINST ('ラーメン' IN BOOLEAN MODE)

検索モードは IN NATURAL LANGUAGE MODE と IN BOOLEAN MODE の2種類があるけれど、BOOLEAN MODE のほうが検索結果が正確になる。

BOOLEAN MODE では検索演算子が使えるから、例えば ラーメン値段 で検索したら +ラーメン +値段 のように AND 演算子を追加して検索を行いたい。しかし、PDO::bindValue を使うと意図した結果にならなかった。仕方がないので PDO::quote を使ってクオートした文字列をSQL文に直接を埋め込むという問題ありな手法にしている…。この辺りみんなどのように実装しているんだろう…。

とりあえずしばらく様子見。

検索時の単語の最小長を設定するには /etc/my.cnf に以下を記述後、mysqld を再起動する（参考）。

innodb_ft_min_token_size = 1