全文搜索（Full-Text Search）是 DuckDB 的一个扩展，允许对字符串进行搜索，类似于 SQLite 的 FTS5 扩展。
安装和加载
fts 扩展在首次使用时会从官方扩展仓库中自动加载。如果您想手动安装并加载它，请运行
INSTALL fts;
LOAD fts;
用法
该扩展为 DuckDB 添加了两个 PRAGMA 语句：一个用于创建索引，一个用于删除索引。此外，还添加了一个标量宏 stem，供扩展内部使用。
PRAGMA create_fts_index
create_fts_index(input_table, input_id, *input_values, stemmer = 'porter',
stopwords = 'english', ignore = '(\\.|[^a-z])+',
strip_accents = 1, lower = 1, overwrite = 0)
为指定表创建 FTS 索引的 PRAGMA。
| 名称 | 类型 | 描述 |
|---|---|---|
input_table |
VARCHAR |
指定表的限定名称，例如 'table_name' 或 'main.table_name' |
input_id |
VARCHAR |
文档标识符的列名，例如 'document_identifier' |
input_values... |
VARCHAR |
要索引的文本字段的列名（可变参数），例如 'text_field_1', 'text_field_2', …, 'text_field_N'，或者使用 '\*' 表示 input_table 中所有 VARCHAR 类型的列 |
stemmer |
VARCHAR |
要使用的词干提取器类型。可选值包括 'arabic', 'basque', 'catalan', 'danish', 'dutch', 'english', 'finnish', 'french', 'german', 'greek', 'hindi', 'hungarian', 'indonesian', 'irish', 'italian', 'lithuanian', 'nepali', 'norwegian', 'porter', 'portuguese', 'romanian', 'russian', 'serbian', 'spanish', 'swedish', 'tamil', 'turkish'，如果不使用词干提取，则为 'none'。默认为 'porter' |
stopwords |
VARCHAR |
包含所需停用词（单列 VARCHAR）的表的限定名称，如果不使用停用词则为 'none'。默认值为 'english'，对应预定义的 571 个英语停用词列表 |
ignore |
VARCHAR |
要忽略的模式的正则表达式。默认为 '(\\.|[^a-z])+'，忽略所有转义字符和非字母的小写字符 |
strip_accents |
BOOLEAN |
是否移除重音符号（例如将 á 转换为 a）。默认为 1 |
lower |
BOOLEAN |
是否将所有文本转换为小写。默认为 1 |
overwrite |
BOOLEAN |
是否覆盖表上现有的索引。默认为 0 |
此 PRAGMA 在新创建的模式（schema）下构建索引。模式将以输入表命名：如果对表 'main.table_name' 创建索引，则模式将命名为 'fts_main_table_name'。
PRAGMA drop_fts_index
drop_fts_index(input_table)
删除指定表的 FTS 索引。
| 名称 | 类型 | 描述 |
|---|---|---|
input_table |
VARCHAR |
输入表的限定名称，例如 'table_name' 或 'main.table_name' |
match_bm25 函数
match_bm25(input_id, query_string, fields := NULL, k := 1.2, b := 0.75, conjunctive := 0)
当索引构建完成后，会创建此检索宏，可用于搜索索引。
| 名称 | 类型 | 描述 |
|---|---|---|
input_id |
VARCHAR |
文档标识符的列名，例如 'document_identifier' |
query_string |
VARCHAR |
要在索引中搜索的字符串 |
fields |
VARCHAR |
要搜索的以逗号分隔的字段列表，例如 'text_field_2, text_field_N'。默认为 NULL，表示搜索所有已索引的字段 |
k |
DOUBLE |
Okapi BM25 检索模型中的参数 k1。默认为 1.2 |
b |
DOUBLE |
Okapi BM25 检索模型中的参数 b。默认为 0.75 |
conjunctive |
BOOLEAN |
是否进行联合查询，即文档中必须包含查询字符串中的所有词条才能被检索到 |
stem 函数
stem(input_string, stemmer)
将单词简化为词干。供扩展内部使用。
| 名称 | 类型 | 描述 |
|---|---|---|
input_string |
VARCHAR |
要提取词干的列或常量。 |
stemmer |
VARCHAR |
要使用的词干提取器类型。可选值包括 'arabic', 'basque', 'catalan', 'danish', 'dutch', 'english', 'finnish', 'french', 'german', 'greek', 'hindi', 'hungarian', 'indonesian', 'irish', 'italian', 'lithuanian', 'nepali', 'norwegian', 'porter', 'portuguese', 'romanian', 'russian', 'serbian', 'spanish', 'swedish', 'tamil', 'turkish'，如果不使用词干提取，则为 'none'。 |
使用示例
创建一个表并填充文本数据
CREATE TABLE documents (
document_identifier VARCHAR,
text_content VARCHAR,
author VARCHAR,
doc_version INTEGER
);
INSERT INTO documents
VALUES ('doc1',
'The mallard is a dabbling duck that breeds throughout the temperate.',
'Hannes Mühleisen',
3),
('doc2',
'The cat is a domestic species of small carnivorous mammal.',
'Laurens Kuiper',
2
);
构建索引，并使 text_content 和 author 列均可搜索。
PRAGMA create_fts_index(
'documents', 'document_identifier', 'text_content', 'author'
);
在 author 字段索引中搜索由 Muhleisen 撰写的文档。这将检索到 doc1
SELECT document_identifier, text_content, score
FROM (
SELECT *, fts_main_documents.match_bm25(
document_identifier,
'Muhleisen',
fields := 'author'
) AS score
FROM documents
) sq
WHERE score IS NOT NULL
AND doc_version > 2
ORDER BY score DESC;
| document_identifier | text_content | score |
|---|---|---|
| doc1 | The mallard is a dabbling duck that breeds throughout the temperate. | 0.0 |
搜索关于 small cats 的文档。这将检索到 doc2
SELECT document_identifier, text_content, score
FROM (
SELECT *, fts_main_documents.match_bm25(
document_identifier,
'small cats'
) AS score
FROM documents
) sq
WHERE score IS NOT NULL
ORDER BY score DESC;
| document_identifier | text_content | score |
|---|---|---|
| doc2 | The cat is a domestic species of small carnivorous mammal. | 0.0 |
警告：当输入表发生更改时，FTS 索引不会自动更新。解决此限制的方法是重新创建索引以进行刷新。