SQLite FTS3 和 FTS4 扩展(三十二)-CSDN博客

本文链接：https://blog.csdn.net/lyfwwb/article/details/137979243

返回：SQLite—系列文章目录

概述

FTS3 和 FTS4 是 SQLite 虚拟表模块，允许用户执行对一组文档进行全文搜索。最常见（和最有效）描述全文搜索的方式是“Google、Yahoo 和 Bing 所做的文件放在万维网上“。用户输入术语或系列的项，可能由二元运算符连接或组合成短语，全文查询系统会找到最能考虑到用户拥有的运算符和分组，匹配这些术语指定。本文介绍 FTS3 和 FTS4 的部署和使用。

FTS1 和 FTS2 是 SQLite 的过时全文搜索模块。有已知的应避免这些较旧模块及其使用的问题。原始 FTS3 代码的一部分被贡献给了 SQLite 项目作者：谷歌的斯科特·赫斯（Scott Hess）。现在是作为SQLite的一部分进行开发和维护。

1. FTS3 和 FTS4 简介

FTS3 和 FTS4 扩展模块允许用户创建具有内置全文索引（以下简称“FTS表”）。全文索引允许用户有效地查询数据库中包含一个或多个单词（以下称为“标记”），即使表包含许多大型文档。

例如，如果 517430 文档中的每个文档 “Enron E-Mail 数据集” 同时插入到 FTS 表和普通 SQLite 表中使用以下 SQL 脚本创建：

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table */

然后，可以执行以下两个查询中的任何一个来查找数据库中包含单词“Linux”的文档（351）。使用一个台式 PC 硬件配置，FTS3 表上的查询返回大约 0.03 秒，而查询普通表则为 22.5 秒。

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */

当然，上面的两个查询并不完全等同。例如 LIKE 查询匹配包含“linuxophobe”等术语的行或“EnterpriseLinux”（碰巧的是，Enron E-Mail Dataset 没有实际上包含任何此类术语），而 FTS3 表上的 MATCH 查询仅选择包含“Linux”作为离散令牌的行。双搜索不区分大小写。FTS3 表在磁盘，而普通表只有 1453 MB。使用相同的用于执行上述 SELECT 查询的硬件配置，FTS3 表格只需不到 31 分钟即可填充，而普通表格则需要 25 分钟桌子。

1.1. FTS3 和 FTS4 之间的区别

FTS3 和 FTS4 几乎相同。他们共享大部分代码，并且它们的接口是相同的。区别在于：

FTS4 包含查询性能优化，这些优化可能显著改进包含以下术语的全文查询的性能很常见（存在于很大比例的表行中）。
FTS4 支持一些可能与 matchinfo（）函数一起使用的附加选项。
因为它在磁盘上存储了两个新的影子表中的额外信息，以支持性能优化和额外的 matchinfo（）选项，FTS4 表可能会消耗更多磁盘空间比使用 FTS3 创建的等效表。通常是开销是 1-2% 或更少，但如果文档存储在 FTS 表非常小。开销可以通过指定指令“matchinfo=fts3”作为 FTS4 表的一部分声明，但这是以牺牲一些额外支持的 matchinfo（）选项。
FTS4 提供钩子（压缩和解压缩选项），允许将数据存储在压缩的表单，减少磁盘使用量和 IO。

FTS4 是对 FTS3 的增强。 FTS3 自 SQLite 版本 3.5.0 （2007-09-04）起可用 SQLite 版本 3.7.4 （2010-12-07）添加了 FTS4 的增强功能。

您应该在应用中使用哪个模块，FTS3 或 FTS4？FTS4 是有时比 FTS3 快得多，甚至快几个数量级取决于查询，尽管在常见情况下，两者的性能模块是类似的。FTS4 还提供增强的 matchinfo（）输出，这些输出可用于对 MATCH 操作的结果进行排名。在另一方面，在没有 matchinfo=fts3 指令的情况下，FTS4 需要一点磁盘空间比 FTS3 多，但在大多数情况下只有 2 的百分比。

对于较新的应用，建议使用 FTS4;虽然如果与旧的兼容性 SQLite 的版本很重要，那么 FTS3 通常也可以使用。

1.2. 创建和销毁 FTS 表

与其他虚拟表类型一样，新的 FTS 表是使用 CREATE VIRTUAL TABLE 语句创建的。模块名称，如下所示 USING 关键字是“fts3”或“fts4”。虚拟表模块参数可以留空，在这种情况下，具有单个用户定义的 FTS 表创建名为“content”的列。或者，模块参数可以传递逗号分隔的列名称列表。

如果为 FTS 表显式提供列名作为 CREATE VIRTUAL TABLE 语句，则数据类型名称可以是可选的为每列指定。这是纯句法糖， FTS 或 SQLite 核心不将提供的类型名用于任何目的。这同样适用于与 FTS 列名称 - 它们被解析，但未被系统使用或记录以任何方式。

-- Create an FTS table named "data" with one column - "content":
CREATE VIRTUAL TABLE data USING fts3();

-- Create an FTS table named "pages" with three columns:
CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);

-- Create an FTS table named "mail" with two columns. Datatypes
-- and column constraints are specified along with each column. These
-- are completely ignored by FTS and SQLite. 
CREATE VIRTUAL TABLE mail USING fts3(
  subject VARCHAR(256) NOT NULL,
  body TEXT CHECK(length(body)<10240)
);

除了列列表外，模块参数还传递给 CREATE 用于创建 FTS 表的 VIRTUAL TABLE 语句可用于指定分词器。这是通过指定格式的字符串来完成的 “tokenize=<tokenizer name> <tokenizer args>”代替列 name，其中 <tokenizer name> 是要使用的分词器的名称，并且 <tokenizer args> 是空格分隔限定符的可选列表传递给 Tokenizer 实现。分词器规范可以是放置在列列表中的任何位置，但最多一个分词器声明是允许用于每个 CREATE VIRTUAL TABLE 语句。请参阅下面的内容使用（并在必要时实现）分词器的详细说明。

-- Create an FTS table named "papers" with two columns that uses
-- the tokenizer "porter".
CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);

-- Create an FTS table with a single column - "content" - that uses
-- the "simple" tokenizer.
CREATE VIRTUAL TABLE data USING fts4(tokenize=simple);

-- Create an FTS table with two columns that uses the "icu" tokenizer.
-- The qualifier "en_AU" is passed to the tokenizer implementation
CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

可以使用普通的 DROP TABLE 语句从数据库中删除 FTS 表。例如：

-- Create, then immediately drop, an FTS4 table.
CREATE VIRTUAL TABLE data USING fts4();
DROP TABLE data;

1.3. 填充 FTS 表

FTS 表使用 INSERT、UPDATE 和 DELETE 语句填充，其填充方式与普通 SQLite 表相同。

以及用户命名的列（如果没有，则为“内容”列 module 参数被指定为 CREATE VIRTUAL TABLE 语句的一部分），每个 FTS 表都有一个“rowid”列。FTS 的 rowid 表的行为方式与普通 SQLite 的 rowid 列相同表，但存储在 FTS 表的 rowid 列中的值除外如果使用 VACUUM 命令重建数据库，则保持不变。对于 FTS 表，“docid”允许与通常的“rowid”一起作为别名， “oid”和“_oid_”标识符。尝试插入或更新带有表中已存在的 docid 值是一个错误，就像它一样使用普通的 SQLite 表。

“docid”和普通的SQLite之间还有一个微妙的区别 rowid 列的别名。通常，如果 INSERT 或 UPDATE 语句将离散值分配给 rowid 列 SQLite 的两个或多个别名写入 INSERT 或 UPDATE 中指定的最右边的此类值语句添加到数据库中。但是，为两者分配非 NULL 值插入或更新 FTS 表被视为错误。有关示例，请参见下文。

-- Create an FTS table
CREATE VIRTUAL TABLE pages USING fts4(title, body);

-- Insert a row with a specific docid value.
INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a software...');

-- Insert a row and allow FTS to assign a docid value using the same algorithm as
-- SQLite uses for ordinary tables. In this case the new docid will be 54,
-- one greater than the largest docid currently present in the table.
INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...');

-- Change the title of the row just inserted.
UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54;

-- Delete the entire table contents.
DELETE FROM pages;

-- The following is an error. It is not possible to assign non-NULL values to both
-- the rowid and docid columns of an FTS table.
INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');

为了支持全文查询，FTS 维护了一个反向索引，用于映射从数据集中出现的每个唯一术语或单词到位置它出现在表格内容中。对于好奇的人，一个用于存储的数据结构的完整说明数据库文件中的此索引如下所示。一个特点这种数据结构是，在任何时候数据库都可能包含不一个索引 B 树，但几个不同的 B 树是增量的在插入、更新和删除行时合并。这种技术改进了写入 FTS 表时的性能，但会导致一些开销使用索引的全文查询。评估特殊的“优化”命令，的 SQL 语句表单 “INSERT INTO <fts-table>（<fts-table>） VALUES（'optimize'）”，导致 FTS 将所有现有的索引 b 树合并为一个大的包含整个索引的 b 树。这可能是一项昂贵的操作，但可能会加快将来的查询速度。

例如，要优化名为 FTS 表的全文索引 “文档”：

-- Optimize the internal structure of FTS table "docs".
INSERT INTO docs(docs) VALUES('optimize');

对于某些人来说，上述陈述在语法上可能不正确。指描述简单 FTS 查询的部分，以获取说明。

还有另一种已弃用的方法来调用 optimize 使用 SELECT 语句进行操作。新代码应使用语句类似于上面的 INSERT 来优化 FTS 结构。

1.4. 简单的 FTS 查询

至于所有其他 SQLite 表，无论是虚拟表还是其他表，都会检索数据使用 SELECT 语句从 FTS 表中。

FTS 表可以使用两个 SELECT 语句进行有效查询不同的形式：

按 rowid 查询。如果 SELECT 语句的 WHERE 子句包含“rowid = ？”形式的子句，其中？是一个 SQL 表达式， FTS 能够使用等效项直接检索请求的行 SQLite INTEGER PRIMARY KEY 索引。
全文查询。如果 SELECT 语句的 WHERE 子句包含 “<column> MATCH？”形式的子句，FTS 能够使用内置全文索引，用于将搜索限制在这些文档上与指定为右侧操作数的全文查询字符串匹配的 MATCH 子句。

如果这两种查询策略都不能使用，则所有对 FTS 表的查询是通过对整个桌子。如果表包含大量数据，则这可能是不切实际的方法（本页的第一个示例表明，线性使用现代 PC 扫描 1.5 GB 数据大约需要 30 秒）。

-- The examples in this block assume the following FTS table:
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

SELECT * FROM mail WHERE rowid = 15;                -- Fast. Rowid lookup.
SELECT * FROM mail WHERE body MATCH 'sqlite';       -- Fast. Full-text query.
SELECT * FROM mail WHERE mail MATCH 'search';       -- Fast. Full-text query.
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;   -- Fast. Rowid lookup.
SELECT * FROM mail WHERE subject = 'database';      -- Slow. Linear scan.
SELECT * FROM mail WHERE subject MATCH 'database';  -- Fast. Full-text query.

在上面的所有全文查询中，MATCH 的右操作数运算符是由单个术语组成的字符串。在本例中，MATCH 对于包含一个或多个文档的所有文档，表达式的计算结果为 true 指定单词的实例（“sqlite”、“search”或“database”，取决于在你看的例子上）。将单个术语指定为右侧 MATCH 运算符的操作数生成最简单和最常见的类型的全文查询是可能的。但是，可以进行更复杂的查询，包括短语搜索、词缀搜索和文档搜索包含每个术语在定义的邻近范围内发生的术语组合其他。下面介绍了查询全文索引的各种方法。

通常，全文查询不区分大小写。但是，这取决于 FTS 表使用的特定分词器被查询。有关详细信息，请参阅有关分词器的部分。

上面的段落指出，具有简单项的 MATCH 运算符右操作数对于包含指定期限。在这种情况下，“文件”可以指存储在 FTS 表的一行的单列中的数据，或存储到内容中一行中的所有列，具体取决于用作 MATCH 运算符的左手操作数。如果标识符指定为 MATCH 运算符的左操作数是 FTS 表列名，则搜索词必须包含的文档是值存储在指定的列中。但是，如果标识符是名称，则 MATCH 运算符的计算结果为 true 对于任何列包含搜索的 FTS 表的每一行术语。以下示例演示了这一点：

-- Example schema
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

-- Example table population
INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');

-- Example queries
SELECT * FROM mail WHERE subject MATCH 'software';    -- Selects rows 1 and 2
SELECT * FROM mail WHERE body    MATCH 'feedback';    -- Selects row 2
SELECT * FROM mail WHERE mail    MATCH 'software';    -- Selects rows 1, 2 and 3
SELECT * FROM mail WHERE mail    MATCH 'slow';        -- Selects rows 1 and 3

乍一看，上面示例中的最后两个全文查询似乎语法不正确，因为有一个表名（“mail”）用作 SQL 表达式。这是可以接受的原因是每个 FTS 表实际上有一个同名的 HIDDEN 列作为表本身（在本例中为“邮件”）。存储在 this 中的值列对应用程序没有意义，但可以用作 MATCH 运算符的左手操作数。这个特殊的专栏也可能是作为参数传递给 FTS 辅助函数。

以下示例说明了上述内容。表达式“文档”， “docs.docs”和“main.docs.docs”都是指“docs”列。但是，表达式“main.docs”不引用任何列。它可以用来引用表，但在以下上下文中不允许使用表名它在下面使用。

-- Example schema
CREATE VIRTUAL TABLE docs USING fts4(content);

-- Example queries
SELECT * FROM docs WHERE docs MATCH 'sqlite';              -- OK.
SELECT * FROM docs WHERE docs.docs MATCH 'sqlite';         -- OK.
SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite';    -- OK.
SELECT * FROM docs WHERE main.docs MATCH 'sqlite';         -- Error.

1.5. 小结

从用户的角度来看，FTS表类似于普通的SQLite 表在许多方面。数据可以添加到、修改和删除从 FTS 表中使用 INSERT、UPDATE 和 DELETE 命令，就像它可能与普通桌子一起使用。同样，可以使用 SELECT 命令查询数据。以下列表总结了 FTS 之间的差异和普通表：

与所有虚拟表类型一样，无法创建索引或附加到 FTS 表的触发器。也无法使用 ALTER TABLE 命令向 FTS 表添加额外的列（尽管可以使用 ALTER TABLE 重命名 FTS 表）。
作为“CREATE VIRTUAL TABLE”语句的一部分指定的数据类型用于创建 FTS 表将被完全忽略。而不是将类型关联应用于插入值的常规规则，全部插入到 FTS 表列中的值（特殊 rowid 除外） column）在存储之前转换为 TEXT 类型。
FTS 表允许使用特殊别名“docid”来指代所有虚拟表都支持的 rowid 列。
FTS MATCH 运算符支持基于内置全文索引。
FTS 辅助函数 snippet（）、offsets（）和 matchinfo（）是可用于支持全文查询。
每个 FTS 表都有一个隐藏列，其中包含与表本身同名。每行中包含的值 hidden column 是一个 blob，它仅用作 MATCH 运算符的左操作数，或用作 MATCH 运算符的最左边的参数 FTS 辅助功能。

2. 编译和启用 FTS3 和 FTS4

尽管 FTS3 和 FTS4 包含在 SQLite 核心源代码中，但它们不是默认启用。要在启用 FTS 功能的情况下构建 SQLite，请定义编译时SQLITE_ENABLE_FTS3预处理器宏。新应用还应该定义 SQLITE_ENABLE_FTS3_PARENTHESIS 宏以启用增强的查询语法（见下文）。通常，这是通过添加以下两个开关到编译器命令行：

-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS

请注意，启用 FTS3 也会使 FTS4 可用。没有单独的 SQLITE_ENABLE_FTS4编译时选项。SQLite 的构建支持 FTS3 和 FTS4 都不支持，或者两者都不支持。

如果使用基于合并 autoconf 的构建系统，请将 CPPFLAGS 运行“configure”脚本时的环境变量很容易设置这些宏的方式。例如，以下命令：

CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure <configure options>

其中<配置选项>是通常传递给的选项配置脚本（如果有）。

由于 FTS3 和 FTS4 是虚拟表，因此 SQLITE_ENABLE_FTS3编译时选项与 SQLITE_OMIT_VIRTUALTABLE 选项不兼容。

如果 SQLite 的构建不包含 FTS 模块，那么任何准备 SQL 语句，用于创建 FTS3 或 FTS4 表，或者删除或访问现有的 FTS 表无论如何都会失败。返回的错误消息将与此类似更改为“没有这样的模块：ftsN”（其中 N 是 3 或 4）。

如果 ICU 库的 C 版本可用，则 FTS 也可以使用 SQLITE_ENABLE_ICU 预处理器宏定义。使用此宏进行编译可使 FTS 分词器能够使用 ICU 库将文档拆分为术语（words）使用指定语言和区域设置的约定。

-DSQLITE_ENABLE_ICU

3. 全文索引查询

FTS 表最有用的是查询使用内置的全文索引执行。全文查询是通过指定表单的子句来执行 “<column> MATCH <full-text query expression>” 作为 WHERE 的一部分从 FTS 表中读取数据的 SELECT 语句的子句。简单的 FTS 查询，返回所有文档包含上面描述的给定术语。在那次讨论中，右手假定 MATCH 运算符的操作数是由单项。本节介绍支持的更复杂的查询类型通过 FTS 表，以及如何通过指定更多复杂查询表达式作为 MATCH 运算符的右操作数。

FTS 表支持三种基本查询类型：

令牌或令牌前缀查询。可以查询 FTS 表中包含指定术语（上述简单情况），或包含具有指定前缀的术语的所有文档。正如我们所拥有的因此，特定术语的查询表达式只是术语本身。用于搜索词前缀的查询表达式是前缀本身附加了一个“*”字符。例如：

-- Virtual table declaration
CREATE VIRTUAL TABLE docs USING fts3(title, body);

-- Query for all documents containing the term "linux":
SELECT * FROM docs WHERE docs MATCH 'linux';

-- Query for all documents containing a term with the prefix "lin". This will match
-- all documents that contain "linux", but also those that contain terms "linear",
--"linker", "linguistic" and so on.
SELECT * FROM docs WHERE docs MATCH 'lin*';

通常，令牌或令牌前缀查询与 FTS 表匹配指定为 MATCH 运算符左侧的列。或者，如果指定了与 FTS 表本身同名的特殊列，针对所有列。这可以通过指定列名来覆盖后跟“：” 基本术语查询前的字符。可能有空间在“：”和要查询的术语之间，但不在列名之间和 “：” 字符。例如：

-- Query the database for documents for which the term "linux" appears in
-- the docu

SQLite FTS3 和 FTS4 扩展(三十二)