全文搜索

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

Postgres 内置了处理 Full Text Search 查询的函数。这就像 Postgres 中的一个“搜索引擎”。

准备#

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

id	title	author	description
1	The Poky Little Puppy	Janette Sebring Lowrey	Puppy is slower than other, bigger animals.
2	The Tale of Peter Rabbit	Beatrix Potter	Rabbit eats some vegetables.
3	Tootle	Gertrude Crampton	Little toy train has big dreams.
4	Green Eggs and Ham	Dr. Seuss	Sam has changing food preferences and eats unusually colored food.
5	Harry Potter and the Goblet of Fire	J.K. Rowling	Fourth year of school starts, big drama ensues.

用法#

本指南中我们将介绍的函数是

`to_tsvector()`#

将您的数据转换为可搜索的词元。to_tsvector() 代表“转换为文本搜索向量”。例如

1
select to_tsvector('green eggs and ham');
2
-- Returns 'egg':2 'green':1 'ham':4

这些词元统称为“文档”，Postgres 可以将其用于比较。

`to_tsquery()`#

将查询字符串转换为匹配的词元。to_tsquery() 代表“转换为文本搜索查询”。

此转换步骤非常重要，因为我们希望对关键字进行“模糊匹配”。例如，如果用户搜索 eggs，而列中的值为 egg，我们可能仍然希望返回匹配项。

Postgres 提供了几个函数来创建 tsquery 对象

to_tsquery() - 需要手动指定运算符 (&, |, !)
plainto_tsquery() - 将纯文本转换为 AND 查询：plainto_tsquery('english', 'fat rats') → 'fat' & 'rat'
phraseto_tsquery() - 创建短语查询：phraseto_tsquery('english', 'fat rats') → 'fat' <-> 'rat'
websearch_to_tsquery() - 支持带有引号、"or" 和否定词的 Web 搜索语法

匹配：`@@`#

@@ 符号是全文搜索的“匹配”符号。它返回 to_tsvector 结果与 to_tsquery 结果之间的任何匹配项。

以下是一个示例

1
select *
2
from books
3
where title = 'Harry';

上面的等号 (=) 在匹配内容上非常“严格”。在全文搜索上下文中，我们可能希望找到所有“Harry Potter”书籍，因此我们可以重写上面的示例

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

基本全文查询#

搜索单个列#

要查找所有 books，其中 description 包含单词 big

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

搜索多个列#

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

要查找所有 books，其中 description 或 title 包含单词 little

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description || ' ' || title) -- concat columns, but be sure to include a space to separate them!
7
  @@ to_tsquery('little');

匹配所有搜索词#

要查找所有 books，其中 description 同时包含单词 little 和 big，我们可以使用 & 符号

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description)
7
  @@ to_tsquery('little & big'); -- use & for AND in the search query

匹配任何搜索词#

要查找所有 books，其中 description 包含单词 little 或 big 中的任何一个，请使用 | 符号

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description)
7
  @@ to_tsquery('little | big'); -- use | for OR in the search query

请注意，搜索 big 会包含带有单词 bigger（或 biggest 等）的结果。

部分搜索#

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

实现部分搜索#

您可以使用 :* 语法与 to_tsquery() 一起使用。这是一个搜索以“Lit”开头的任何书名的示例

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

通过 RPC 扩展功能#

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

创建此函数后，可以使用您平台的 SDK 从应用程序调用它。这是一个例子

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

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

处理查询中的空格#

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

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

使用 `websearch_to_tsquery()` 进行 Web 搜索语法#

websearch_to_tsquery() 函数提供了一种直观的搜索语法，类似于流行的 Web 搜索引擎，使其非常适合面向用户的搜索界面。

基本用法#

1
select *
2
from books
3
where to_tsvector(description) @@ websearch_to_tsquery('english', 'green eggs');

带引号的短语#

使用引号搜索精确短语

1
select * from books
2
where to_tsvector(description || ' ' || title) @@ websearch_to_tsquery('english', '"Green Eggs"');
3
-- Matches documents containing "Green" immediately followed by "Eggs"

OR 搜索#

使用“or”（不区分大小写）搜索多个术语

1
select * from books
2
where to_tsvector(description) @@ websearch_to_tsquery('english', 'puppy or rabbit');
3
-- Matches documents containing either "puppy" OR "rabbit"

否定#

使用破折号 (-) 排除术语

1
select * from books
2
where to_tsvector(description) @@ websearch_to_tsquery('english', 'animal -rabbit');
3
-- Matches documents containing "animal" but NOT "rabbit"

复杂查询#

组合多个运算符进行复杂的搜索

1
select * from books
2
where to_tsvector(description || ' ' || title) @@
3
  websearch_to_tsquery('english', '"Harry Potter" or "Dr. Seuss" -vegetables');
4
-- Matches books by "Harry Potter" or "Dr. Seuss" but excludes those mentioning vegetables

创建索引#

现在您已经可以使用全文搜索，创建一个 index。这允许 Postgres “预先”构建文档，因此无需在执行查询时创建它们。这将使我们的查询更快。

可搜索的列#

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

我们可以使用 Postgres 的一项特殊功能，称为生成列，以确保每当 title 和 description 列中的值发生更改时，索引都会更新。

1
alter table
2
  books
3
add column
4
  fts tsvector generated always as (to_tsvector('english', description || ' ' || title)) stored;
5
6
create index books_fts on books using gin (fts); -- generate the index
7
8
select id, fts
9
from books;

使用新列进行搜索#

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

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

查询运算符#

访问 Postgres: Text Search Functions and Operators 了解有关您可以使用的高级 full text queries 运算符，例如

邻近度：`<->`#

邻近度符号对于搜索彼此相距一定“距离”的术语非常有用。例如，要查找短语 big dreams，其中“big”的匹配项紧随“dreams”的匹配项之后

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

我们还可以使用 <-> 查找彼此相距一定距离的单词。例如，查找彼此相距 2 个单词的 year 和 school

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

否定：`!`#

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

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

对搜索结果进行排名#

Postgres 提供了排名函数，可以按相关性对搜索结果进行排序，帮助您首先呈现最相关的匹配项。由于排名函数需要在服务器端计算，因此请使用 RPC 函数和生成列。

创建带有排名的搜索函数#

首先，创建一个处理搜索和排名的 Postgres 函数

1
create or replace function search_books(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(to_tsvector('english', books.description), to_tsquery(search_query)) as rank
10
  from books
11
  where to_tsvector('english', books.description) @@ to_tsquery(search_query)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

现在您可以从客户端调用此函数

1
const { data, error } = await supabase.rpc('search_books', { search_query: 'big' })

加权排名#

Postgres 允许您使用权重标签为文档的不同部分分配不同的重要性级别。当您希望某些字段（如标题）中的匹配项排名高于其他字段（如描述）中的匹配项时，这非常有用。

了解权重标签#

Postgres 使用四个权重标签：A、B、C 和 D，其中

A = 最高重要性（权重 1.0）
B = 高重要性（权重 0.4）
C = 中等重要性（权重 0.2）
D = 低重要性（权重 0.1）

创建加权搜索列#

首先，创建一个加权的 tsvector 列，该列赋予标题比描述更高的优先级

1
-- Add a weighted fts column
2
alter table books
3
add column fts_weighted tsvector
4
generated always as (
5
  setweight(to_tsvector('english', title), 'A') ||
6
  setweight(to_tsvector('english', description), 'B')
7
) stored;
8
9
-- Create index for the weighted column
10
create index books_fts_weighted on books using gin (fts_weighted);

现在创建一个使用此加权列的搜索函数

1
create or replace function search_books_weighted(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(books.fts_weighted, to_tsquery(search_query)) as rank
10
  from books
11
  where books.fts_weighted @@ to_tsquery(search_query)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

自定义权重数组#

您还可以通过将权重数组提供给 ts_rank() 来指定自定义权重

1
create or replace function search_books_custom_weights(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(
10
      '{0.0, 0.2, 0.5, 1.0}'::real[], -- Custom weights {D, C, B, A}
11
      books.fts_weighted,
12
      to_tsquery(search_query)
13
    ) as rank
14
  from books
15
  where books.fts_weighted @@ to_tsquery(search_query)
16
  order by rank desc;
17
end;
18
$$ language plpgsql;

此示例使用自定义权重，其中

A 标记的术语（标题）具有最大权重 (1.0)
B 标记的术语（描述）具有中等权重 (0.5)
C 标记的术语具有低权重 (0.2)
D 标记的术语被忽略 (0.0)

使用加权搜索#

1
// Search with standard weighted ranking
2
const { data, error } = await supabase.rpc('search_books_weighted', { search_query: 'Harry' })
3
4
// Search with custom weights
5
const { data: customData, error: customError } = await supabase.rpc('search_books_custom_weights', {
6
  search_query: 'Harry',
7
})

实际示例与结果#

假设你搜索 "Harry"。使用加权列

“Harry Potter and the Goblet of Fire” (标题匹配) 获得权重 A = 1.0
在描述中提到 "Harry" 的书籍 获得权重 B = 0.4

这确保了标题中包含 "Harry" 的书籍排名明显高于仅在描述中提到 "Harry" 的书籍，从而为用户提供更相关的搜索结果。

使用索引进行排名 #

在使用之前创建的 fts 列时，排名会变得更有效。创建一个使用索引列的函数

1
create or replace function search_books_fts(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(books.fts, to_tsquery(search_query)) as rank
10
  from books
11
  where books.fts @@ to_tsquery(search_query)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

1
const { data, error } = await supabase.rpc('search_books_fts', { search_query: 'little & big' })

使用排名进行网络搜索语法 #

你还可以创建一个将 websearch_to_tsquery() 与排名结合起来的函数，以提供用户友好的搜索

1
create or replace function websearch_books(search_text text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(books.fts, websearch_to_tsquery('english', search_text)) as rank
10
  from books
11
  where books.fts @@ websearch_to_tsquery('english', search_text)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

1
// Support natural search syntax
2
const { data, error } = await supabase.rpc('websearch_books', {
3
  search_text: '"little puppy" or train -vegetables',
4
})

资源#

Postgres: 文本搜索函数和运算符