全文搜索

如何在PostgreSQL中使用全文搜索功能。

Postgres 内置了处理全文搜索查询的函数。这就像是 Postgres 内部的"搜索引擎"。

准备工作

在本指南中，我们将使用以下示例数据：

使用方法

本指南将介绍以下函数：

to_tsvector()#

将数据转换为可搜索的词元。to_tsvector() 表示"转换为文本搜索向量"。例如：

1
2
select to_tsvector('green eggs and ham');-- 返回结果：'egg':2 'green':1 'ham':4

这些词元统称为"文档"，Postgres 可以用于比较操作。

to_tsquery()#

将查询字符串转换为匹配用的词元。to_tsquery() 表示"转换为文本搜索查询"。

这个转换步骤很重要，因为我们通常需要对关键词进行"模糊匹配"。 例如，当用户搜索 eggs 而列中的值是 egg 时，我们可能仍希望返回匹配结果。

匹配操作符：@@#

@@ 符号是全文搜索的"匹配"操作符。它返回 to_tsvector 结果与 to_tsquery 结果之间的所有匹配项。

请看以下示例：

1
2
3
select *from bookswhere title = 'Harry';

上面的等号操作符(=)对匹配内容要求非常"严格"。在全文搜索场景中，我们可能想查找所有"哈利波特"书籍，因此可以重写上述示例：

1
2
3
select *from bookswhere to_tsvector(title) @@ to_tsquery('Harry');

基础全文查询

搜索单列

查找所有description列包含单词big的books记录：

1
2
3
4
5
6
7
select  *from  bookswhere  to_tsvector(description)  @@ to_tsquery('big');

多列搜索

目前没有直接使用 JavaScript 或 Dart 搜索多列的方法，但您可以通过在数据库中创建计算列来实现。

查找所有 description 或 title 包含单词 little 的 books：

1
2
3
4
5
6
7
select  *from  bookswhere  to_tsvector(description || ' ' || title) -- 合并列时务必包含空格分隔！  @@ to_tsquery('little');

匹配所有搜索词

要查找 description 字段同时包含 little 和 big 这两个单词的所有 books，我们可以使用 & 符号：

1
2
3
4
5
6
7
select  *from  bookswhere  to_tsvector(description)  @@ to_tsquery('little & big'); -- 在搜索查询中使用 & 表示 AND

匹配任意搜索词

要查找 description 字段包含 little 或 big 任意一个单词的所有 books，请使用 | 符号：

1
2
3
4
5
6
7
select  *from  bookswhere  to_tsvector(description)  @@ to_tsquery('little | big'); -- 在搜索查询中使用 | 表示 OR 逻辑

注意搜索 big 时会包含 bigger（或 biggest 等）的结果。

部分匹配搜索

部分匹配搜索在您需要查找数据中子字符串匹配时特别有用。

实现部分匹配搜索

您可以使用 :* 语法配合 to_tsquery() 函数。以下示例展示了如何搜索所有以"Lit"开头的书籍标题：

1
select title from books where to_tsvector(title) @@ to_tsquery('Lit:*');

通过RPC扩展功能#

为了使部分搜索功能能够通过API访问，您可以将搜索逻辑封装在存储过程中。

创建此函数后，您可以使用对应平台的SDK从应用程序中调用它。以下是一个示例：

1
2
3
4
5
6
7
create or replace function search_books_by_title_prefix(prefix text)returns setof books AS $$begin  return query  select * from books where to_tsvector('english', title) @@ to_tsquery(prefix || ':*');end;$$ language plpgsql;

此函数接收一个前缀参数，并返回所有标题包含以该前缀开头的单词的书籍。:*操作符用于在to_tsquery()函数中表示前缀匹配。

处理查询中的空格

当搜索词需要包含短语或多个单词时，您可以使用+作为空格占位符来连接单词：

1
select * from search_books_by_title_prefix('Little+Puppy');

创建索引

现在我们已经实现了全文搜索功能，接下来让我们创建一个索引。这将允许Postgres预先"构建"文档，使得在执行查询时无需临时创建。这将显著提升我们的查询速度。

可搜索列

让我们在books表中新建一个名为fts的列，用于存储title和description列的可搜索索引。

我们可以使用Postgres的一个特殊功能——生成列，来确保每当title和description列的值发生变化时，索引都会自动更新。

1
2
3
4
5
6
7
8
9
alter table  booksadd column  fts tsvector generated always as (to_tsvector('english', description || ' ' || title)) stored;create index books_fts on books using gin (fts); -- 生成索引select id, ftsfrom books;

使用新列进行搜索

现在我们已经创建并填充了索引，可以使用与之前相同的技术进行搜索：

1
2
3
4
5
6
select  *from  bookswhere  fts @@ to_tsquery('little & big');

查询操作符

请参阅 PostgreSQL: 文本搜索函数与操作符 了解可用于执行更高级全文查询的其他查询操作符，例如：

邻近操作符: <->#

邻近符号可用于搜索相隔特定"距离"的词语。例如，查找短语 big dreams，其中"big"的匹配项紧跟着"dreams"的匹配项：

1
2
3
4
5
6
select  *from  bookswhere  to_tsvector(description) @@ to_tsquery('big <-> dreams');

我们还可以使用 <-> 查找彼此间隔特定距离的词语。例如查找相隔2个词以内的 year 和 school：

1
2
3
4
5
6
select  *from  bookswhere  to_tsvector(description) @@ to_tsquery('year <2> school');

否定操作符：!#

否定符号可用于查找_不包含_特定搜索词的短语。例如，要查找包含单词big但不包含little的记录：

1
2
3
4
5
6
select  *from  bookswhere  to_tsvector(description) @@ to_tsquery('big & !little');

相关资源

• PostgreSQL: 文本搜索函数和操作符