本文へスキップ
ウェブエンジニア問題集
第9章

FTS5でSQLiteに全文検索を実装する

約10分
この章の目次開く

第7章で見た通り、LIKE '%キーワード%' にインデックスは効きません。記事本文からキーワードを探すような検索を素直に書くと、テーブル全体を毎回走査することになります。

SQLiteには FTS5 という全文検索の仕組みが内蔵されています。検索用のインデックスを別途持つことで、部分一致の検索を高速に実行でき、さらに関連度順の並べ替えやキーワードのハイライトまで扱えます。

学習者学習者

全文検索って、ElasticsearchみたいなものをSQLiteでやるということですか？

先生先生

規模はぜんぜん違うけど、考え方は同じだよ。数万〜数十万件の記事を検索するくらいなら、FTS5で十分に実用になる。

FTS5テーブルを作る

FTS5は仮想テーブルとして作ります。

構文： CREATE VIRTUAL TABLE テーブル名 USING fts5(列名, ...[, オプション])

CREATE VIRTUAL TABLE articles_fts USING fts5(title, body);
sql

通常のテーブルと違い、型を指定しません。FTS5の列はすべてテキストとして扱われるためです。

INSERT INTO articles_fts (title, body) VALUES
  ('SQLiteの基礎', 'SQLiteはサーバー不要のデータベースです'),
  ('WALモード入門', '読み取りと書き込みを同時に処理できます');
sql

MATCH で検索する

検索には MATCH 演算子を使います。左辺にはテーブル名そのものを書きます。

SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite';
sql

table(...) という短い書き方もできます。

SELECT * FROM articles_fts('SQLite');
sql
MATCH の左辺は列名ではなくテーブル名です。列を指定したいときは、検索文字列の側に 列名 : キーワード と書きます。 
-- title列だけを検索
SELECT * FROM articles_fts WHERE articles_fts MATCH 'title : SQLite';
sql

検索文字列では、次のような指定ができます。

書き方意味
SQLite WAL両方を含む（暗黙のAND）
SQLite OR WALどちらかを含む
SQLite NOT WAL前者を含み後者を含まない
"WAL mode"フレーズとして連続で一致
SQL*前方一致（SQLite などにヒット）
NEAR(SQLite WAL, 10)10トークン以内に両方が現れる

関連度順に並べる

FTS5には rank という特別な列があり、ORDER BY rank と書くだけで関連度順になります。

SELECT title, rank
FROM articles_fts
WHERE articles_fts MATCH 'SQLite'
ORDER BY rank
LIMIT 20;
sql

内部では BM25 というスコアリングが使われています。BM25のスコアは「小さいほど関連度が高い」負の値で表されるため、ORDER BY rank（昇順）がそのまま「関連度の高い順」になります。

列ごとの重み付けもできます。

構文： bm25(テーブル名[, 列1の重み, 列2の重み, ...])

戻り値： BM25スコア（負の実数。小さいほど関連度が高い）

-- タイトルの一致を本文の10倍重視する
SELECT title, bm25(articles_fts, 10.0, 1.0) AS score
FROM articles_fts
WHERE articles_fts MATCH 'SQLite'
ORDER BY score
LIMIT 20;
sql
本を読むイメージ
タイトルの一致を重く扱うと、検索結果の納得感が大きく変わります

ハイライトと抜粋

検索結果の表示に使う関数が2つ用意されています。

構文： highlight(テーブル名, 列番号, 開始文字列, 終了文字列)

戻り値： 一致部分を開始・終了文字列で囲んだテキスト

SELECT highlight(articles_fts, 0, '<mark>', '</mark>')
FROM articles_fts
WHERE articles_fts MATCH 'SQLite';
sql

構文： snippet(テーブル名, 列番号, 開始文字列, 終了文字列, 省略記号, 最大トークン数)

戻り値： 一致箇所の周辺だけを切り出したテキスト

SELECT snippet(articles_fts, 1, '<mark>', '</mark>', '…', 20)
FROM articles_fts
WHERE articles_fts MATCH 'SQLite';
sql

検索結果一覧で「本文の該当部分だけを抜粋して表示する」という、よくあるUIがこれ1つで作れます。

日本語をどう扱うか

ここがFTS5でもっとも重要な論点です。

FTS5は既定で unicode61 というトークナイザを使い、空白や記号でテキストを分割します。英語なら単語ごとに分かれますが、日本語は単語の間に空白がないため、文がまるごと1つのトークンになってしまいます。

-- 既定のトークナイザでは、これがヒットしない
INSERT INTO articles_fts (title, body) VALUES ('入門', 'データベースの基礎');
SELECT * FROM articles_fts WHERE articles_fts MATCH 'データベース';
sql
学習者学習者

日本語だと使えない、ということですか……？

使えます。trigram トークナイザを指定します。SQLite 3.34.0 以降で利用できます。

CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  tokenize = 'trigram'
);
sql

trigramは、テキストを3文字ずつの重なり合った断片に分割してインデックスを作ります。「データベース」なら「データ」「ータベ」「タベー」「ベース」…という具合です。単語の切れ目を知らなくても部分一致で検索できるため、日本語との相性が良い方式です。

トークナイザ分割方法日本語備考
unicode61（既定）空白・記号で分割不向き英語向け
asciiASCIIの記号で分割不向きunicode61 の簡易版
porter英語の語幹処理を追加不向きrunning と run を同一視
trigram3文字ずつ向いている2文字以下の検索はできない
日本語を検索するなら tokenize = 'trigram' を指定してください。ただし2文字以下のキーワードは検索できません。

trigramの制約と特徴を整理しておきます。

  • 検索キーワードは3文字以上が必要（「猫」「東京」ではヒットしない）
  • インデックスのサイズが大きくなる
  • 部分一致に強い。LIKE '%…%' や GLOB でもインデックスが使われるようになる
  • 語の意味を理解しないため、無関係な文字列が偶然一致することがある

元のテーブルと同期させる

実際のアプリでは、記事データは通常のテーブルにあり、FTS5テーブルは検索用のインデックスとして持つのが一般的です。

FTS5には、本体テーブルを参照する external content という仕組みがあります。テキストを二重に保存せずに済みます。

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;
 
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  content = 'articles',
  content_rowid = 'id',
  tokenize = 'trigram'
);
sql

この構成では、本体テーブルの変更がFTS5側に自動反映されません。トリガーで同期させます。

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;
 
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
END;
 
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;
sql

同期がずれてしまった場合は、作り直しの命令があります。

INSERT INTO articles_fts(articles_fts) VALUES('rebuild');
sql

検索するときは、FTS5テーブルと本体テーブルを結合します。

SELECT a.id, a.title, a.created_at,
       snippet(articles_fts, 1, '<mark>', '</mark>', '…', 20) AS excerpt
FROM articles_fts f
JOIN articles a ON a.id = f.rowid
WHERE articles_fts MATCH ?
ORDER BY rank
LIMIT 20;
sql
ひらめきのイメージ
本体テーブル＋FTS5＋トリガーの3点セットが、SQLiteでの全文検索の基本構成です

よくあるハマりどころ

MATCH の左辺に列名を書く

-- エラーになる
SELECT * FROM articles_fts WHERE body MATCH 'SQLite';
 
-- テーブル名を書く
SELECT * FROM articles_fts WHERE articles_fts MATCH 'body : SQLite';
sql

ORDER BY rank DESC にしてしまう

BM25スコアは負の値で、小さいほど関連度が高いという設計です。DESC にすると関連度の低い順になります。ORDER BY rank のまま（昇順）が正解です。

trigramで2文字のキーワードを検索している

trigramは3文字単位でインデックスを作るため、2文字以下の検索語はヒットしません。UI側で「3文字以上入力してください」と案内するか、短い検索語のときだけ LIKE にフォールバックする実装が必要です。

本体テーブルを直接更新してFTS5がずれる

マイグレーションやバッチでトリガーを迂回して更新すると、検索結果と実データがずれます。ずれに気づいたら 'rebuild' で作り直してください。

大量データを1件ずつINSERTする

FTS5への挿入もインデックス更新を伴います。初回のデータ投入では、必ずトランザクションでまとめてください。

ちゃんと使うためのポイント

  • FTS5は CREATE VIRTUAL TABLE ... USING fts5(...) で作る。型は指定しない
  • 検索は テーブル名 MATCH '検索語'。列指定は検索語側に 列名 : 語 と書く
  • ORDER BY rank（昇順）で関連度順。bm25() で列ごとの重み付けができる
  • highlight() / snippet() で結果表示を作れる。列番号は0始まり
  • 日本語には tokenize = 'trigram'。ただし3文字以上のキーワードが必要
  • 本体テーブルと分ける場合は external content + トリガーで同期する
  • ずれたら 'rebuild' で作り直せる

次の章では、キーワードの一致ではなく「意味の近さ」で探すベクトル検索を扱います。sqlite-vec 拡張を使って、ローカルで完結するRAGやAIエージェントのメモリを構築する方法を見ていきます。

参考リンク

SQLクイズに挑戦する検索とインデックスの知識を、4択クイズでアウトプットして定着させよう