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
);

也支持使用文本 ID，而不仅限于 INTEGERS。

这是一个标准提示框。你可以自定义其内容，甚至更改图标。

无内容表

你也可以创建不基于任何现有表的无内容表：

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

保持索引为最新

由于源内容存储在另一个表中，我们需要确保两个表保持同步，并避免在尝试执行查询时于关键路径上更新索引。

默认情况下，创建表时表是空的，即使源表中已有数据也是如此。你有多种方式可以填充索引。

通过查询更新

如果你使用无内容表，或者想从视图中提取数据，可以通过查询来更新。

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 操作时，两个表始终保持同步。

搜索索引

查询语法

以下是支持的查询语法：

<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