how-to-do-full-text-search-with-sqlite

SQLite で全文検索を行う方法

SQLite では、ロード可能な拡張機能を使って、新しい機能を追加できます。標準で用意されているものには、fts5、json1などがあります。

アプリケーションを構築する際、テーブルのデータを基にした検索機能を追加するのは一般的で、すでに LIKE を使ったあいまい検索のクエリを用意している場合もあるでしょう。SQLite では、MATCH キーワードを使うだけで、データセット全体を簡単に検索できると聞けば、きっと興味を持つはずです。👀

最初の検索インデックスを作成する

SQLite の全文検索では、インデックスを VIRTUAL テーブルに保存する必要があります。これにより、実行するクエリに合わせてインデックスを最適化して保存できます。

インデックス用の仮想テーブルを作成するには、fts5 を指定する USING ディレクティブを含めます。

CREATE VIRTUAL TABLE posts_fts USING fts5 (
    title,
    description,
    content,
    content=posts,
    content_rowid=id
);

INTEGER だけでなく、テキスト ID もサポートされています。

これは標準のコールアウトです。内容をカスタマイズしたり、アイコンを変更したりできます。

コンテンツレステーブル

既存のテーブルを基にしないコンテンツレステーブルを作成することもできます。

CREATE VIRTUAL TABLE example_fts USING fts5 (
    name,
    description,
    content=''
);

インデックスを最新の状態に保つ

元データを別のテーブルに保存する場合は、2 つのテーブルを同期させ、クエリを実行するときに負荷の高い処理が発生しないよう、インデックスの更新方法を考慮する必要があります。

テーブルを作成した直後は、元のテーブルにデータが入っていても、インデックスは空です。インデックスにデータを登録する方法はいくつかあります。

クエリで更新する

コンテンツレステーブルを使う場合や、ビューからデータを取得する場合は、クエリで更新できます。

INSERT INTO posts_fts (id, title, description, content)
SELECT id, title, description, content FROM posts;

再構築コマンド

再構築コマンドを使うと、指定したコンテンツテーブルに基づいてインデックスが更新されます。

INSERT INTO posts_fts(posts_fts) VALUES('rebuild');

トリガー

SQLite のトリガーを使って、レコードを自動的に最新の状態に保てます。

CREATE TRIGGER posts_insert AFTER INSERT ON posts BEGIN
  INSERT INTO posts_fts(id, title, description, content)
  VALUES (new.id, new.title, new.description, new.content);
END;

CREATE TRIGGER posts_delete AFTER DELETE ON posts BEGIN
  INSERT INTO posts_fts(posts_fts, id, title, description, content)
  VALUES ('delete', old.id, old.title, old.description, old.content);
END;

CREATE TRIGGER posts_update AFTER UPDATE ON posts BEGIN
  INSERT INTO posts_fts(posts_fts, id, title, description, content)
  VALUES ('delete', old.id, old.title, old.description, old.content);
  INSERT INTO posts_fts(id, title, description, content)
  VALUES (new.id, new.title, new.description, new.content);
END;

これにより、元のテーブルに対する CRUD 操作が行われても、常に 2 つのテーブルが同期されます。

インデックスを検索する

クエリ構文

サポートされているクエリ構文は次のとおりです。

<phrase>    := string [*]
<phrase>    := <phrase> + <phrase>
<neargroup> := NEAR ( <phrase> <phrase> ... [, N] )
<query>     := [ [-] <colspec> :] [^] <phrase>
<query>     := [ [-] <colspec> :] <neargroup>
<query>     := [ [-] <colspec> :] ( <query> )
<query>     := <query> AND <query>
<query>     := <query> OR <query>
<query>     := <query> NOT <query>
<colspec>   := colname
<colspec>   := { colname1 colname2 ... }

インデックスに対して実際にクエリを実行するには、MATCH キーワードを使い、rank で並べ替えます。

SELECT posts.* FROM posts_fts
INNER JOIN posts ON posts.id = posts_fts.rowid
WHERE posts_fts MATCH :query
ORDER BY rank;

デモ

参考資料

• https://www.sqlite.org/fts5.html
• https://docs.datasette.io/en/latest/full_text_search.html