SQLite 全文索引 - FTS5 高效搜索

介绍

在这个实验中，你将学习如何使用 SQLite 的 FTS5 扩展来进行高效的全文搜索。你将创建 FTS5 表，插入数据，执行搜索，并使用 MATCH 子句优化查询。这个实验提供了一个关于 SQLite 中全文索引的实践性介绍。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") sqlite/SQLiteGroup -.-> sqlite/end_db("Close Database Connection") subgraph Lab Skills sqlite/make_table -.-> lab-552551{{"SQLite 全文索引"}} sqlite/add_rows -.-> lab-552551{{"SQLite 全文索引"}} sqlite/get_all -.-> lab-552551{{"SQLite 全文索引"}} sqlite/query_where -.-> lab-552551{{"SQLite 全文索引"}} sqlite/verify_table -.-> lab-552551{{"SQLite 全文索引"}} sqlite/end_db -.-> lab-552551{{"SQLite 全文索引"}} end

创建 SQLite 数据库并启用 FTS5

在这个步骤中，你将创建一个 SQLite 数据库并启用 FTS5 扩展。FTS5 是一个提供全文搜索功能的模块。

首先，在 LabEx 虚拟机中打开你的终端。你的默认路径是 /home/labex/project。

创建 SQLite 数据库：

创建一个名为 books.db 的数据库文件，并使用 sqlite3 命令连接到它：
```
sqlite3 books.db
```
这个命令会启动 SQLite shell，你可以在这里执行 SQL 命令。
启用 FTS5 扩展：

在使用 FTS5 之前，你需要启用它。执行以下 SQL 命令：
```
CREATE VIRTUAL TABLE book_search USING fts5();
```
如果你看到类似 Error: no such module: fts5 的错误，这意味着 FTS5 扩展不可用。在大多数现代 SQLite 版本中，FTS5 默认包含。如果你遇到这个错误，请确保你的 SQLite 版本是最新的。如果命令运行没有错误，则 FTS5 扩展已启用。

这个命令使用 fts5 模块创建一个名为 book_search 的虚拟表（virtual table）。虚拟表是 SQLite 的一个特性，允许你使用自定义功能扩展数据库。
定义表模式（schema）：

现在，让我们为 book_search 表定义列。我们将包括 title（标题）、author（作者）和 content（内容）列。执行以下 SQL 命令：
```
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);
```
这个命令创建具有指定列的 book_search 表。title、author 和 content 列将由 FTS5 索引，允许你对它们执行全文搜索。
配置分词器（tokenizer）（可选）：

你可以通过指定一个分词器来自定义 FTS5 如何对文本进行分词。unicode61 分词器提供了良好的 Unicode 支持。要使用它并删除变音符号（diacritics，重音符），请使用以下命令重新创建表：
```
DROP TABLE IF EXISTS book_search;
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");
```
这个命令首先删除现有的 book_search 表（如果存在），然后使用 unicode61 分词器重新创建它。remove_diacritics 1 选项告诉 FTS5 在索引期间从文本中删除变音符号。

将数据插入到 FTS5 表中

在这个步骤中，你将把数据插入到 book_search 表中。这些数据将在后面的步骤中用于全文搜索。

插入图书数据：

执行以下 SQL 命令，将示例图书数据插入到 book_search 表中：

INSERT INTO book_search (title, author, content) VALUES (
    'The Lord of the Rings',
    'J.R.R. Tolkien',
    'A fantasy epic about hobbits, elves, and the battle against Sauron.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'Pride and Prejudice',
    'Jane Austen',
    'A classic novel about love, class, and society in 19th-century England.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'The Hitchhiker''s Guide to the Galaxy',
    'Douglas Adams',
    'A comedic science fiction series following the misadventures of Arthur Dent.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'To Kill a Mockingbird',
    'Harper Lee',
    'A powerful story about racial injustice in the American South.'
);

INSERT INTO book_search (title, author, content) VALUES (
    '1984',
    'George Orwell',
    'A dystopian novel about totalitarianism and surveillance.'
);

这些命令将五行数据插入到 book_search 表中，每一行代表一本书。为每本书提供了 title（标题）、author（作者）和 content（内容）的值。请注意 'The Hitchhiker''s Guide to the Galaxy' 中转义的单引号。字符串中的单引号必须通过将它们加倍来转义。

验证数据插入：

要确认数据已正确添加，请运行以下命令：

SELECT * FROM book_search;

这个命令检索 book_search 表中的所有行和列。你应该看到你刚刚插入的数据。

预期输出：

The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

执行基本全文搜索

在这个步骤中，你将使用 MATCH 操作符执行基本全文搜索。

搜索单个词条（term）：

要查找包含单词“fantasy”的书籍，请执行以下 SQL 命令：
```
SELECT * FROM book_search WHERE book_search MATCH 'fantasy';
```
这个命令在 book_search 表的所有列中搜索词条“fantasy”。MATCH 操作符用于执行全文搜索。

预期输出：
```
The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
```
搜索多个词条：

你可以通过用空格分隔多个词条来搜索它们。要查找同时包含“love”和“society”的书籍，请执行以下 SQL 命令：
```
SELECT * FROM book_search WHERE book_search MATCH 'love society';
```
这个命令搜索在任何索引列中同时包含“love”和“society”的行。

预期输出：
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```
在特定列中搜索：

要在特定列中搜索，请在 MATCH 操作符之前指定列名。例如，要在 author（作者）列中查找包含“Orwell”的书籍，请执行以下 SQL 命令：
```
SELECT * FROM book_search WHERE author MATCH 'Orwell';
```
这个命令仅在 author 列中搜索词条“Orwell”。

预期输出：
```
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.
```

使用高级 MATCH 功能

在这个步骤中，你将探索 MATCH 操作符的高级功能，包括前缀搜索和布尔运算符。

前缀搜索（Prefix Search）：

使用 * 通配符执行前缀搜索。要查找包含以“soci”开头的单词的书籍，请执行以下 SQL 命令：
```
SELECT * FROM book_search WHERE book_search MATCH 'soci*';
```
这个命令搜索以“soci”开头的词条，例如“society”。

预期输出：
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```

布尔运算符（Boolean Operators）：

FTS5 支持布尔运算符，如 AND、OR 和 NOT，以创建更复杂的搜索查询。

AND： 查找包含所有词条的文档。当词条用空格分隔时，默认行为是 AND。
```
SELECT * FROM book_search WHERE book_search MATCH 'love AND society';
```
这等同于 SELECT * FROM book_search WHERE book_search MATCH 'love society';

预期输出：
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```

OR： 查找包含任一词条的文档。

SELECT * FROM book_search WHERE book_search MATCH 'love OR injustice';

这个命令查找包含“love”或“injustice”（或两者都包含）的书籍。

预期输出：

Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.

NOT： 排除包含特定词条的文档。

SELECT * FROM book_search WHERE book_search MATCH 'NOT fantasy';

这个命令查找不包含单词“fantasy”的书籍。

预期输出：

Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

总结

在这个实验中，你已经学习了如何使用 SQLite 的 FTS5 扩展进行全文搜索。你创建了一个 FTS5 表，插入了数据，使用 MATCH 操作符执行了基本搜索，并探索了高级功能，如前缀搜索和布尔运算符。这些技能为你构建强大的搜索功能到你的 SQLite 应用程序中提供了基础。