如何在 Postgre 中使用全文搜索

简介

全文检索是一个强大的技术，可以在大量文本中快速查找和匹配关键词。PostgreSQL是一个开源关系型数据库管理系统，拥有内置的函数来处理全文搜索查询，像是在 Postgres 内部的一个“搜索引擎”，可以应用于各种需要处理大量文本数据的场景。

在使用全文搜索功能时，开发者需要注意以下几点：

• 选择合适的分词器：PostgreSQL支持多种分词器，开发者需要根据实际应用场景选择合适的分词器。

• 优化搜索性能：对于大型数据库，全文搜索可能会涉及到大量的数据扫描。因此，开发者需要合理设计数据库结构、索引策略以及查询语句，以优化搜索性能。

• 处理多语言文本：对于包含多种语言的文本数据，开发者需要考虑如何处理不同语言的分词和搜索问题。

接下来，让我们来快速学习操作一下。

使用教程

注册登录MemFire Cloud平台，创建一个应用，点击进入应用详情，找到SQL编辑器工具；

创建数据表

首先创建一张数据表，并插入一些数据。

create table books (
  id serial primary key,
  title text,
  author text,
  description text
);

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

执行上述命令后，可以在表编辑器中查看结果：

使用方法

PostgreSQL中的全文搜索功能通过其内置的tsvector和tsquery数据类型，以及相关的全文搜索函数和操作符，为开发者提供了强大的文本搜索能力。

to_tsvector()函数

该函数将您的数据转换为 tsvector 类型，该数据类型允许高效地执行词干提取、词汇标准化等操作。例如：

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

这些词条集合在一起被称为一个“文档”，Postgres 可以使用它进行比较。

该函数将一个文本字符串转换为一个 tsquery 数据类型，该数据类型表示一个全文搜索查询。tsquery 类型允许你执行复杂的搜索查询，包括多个词汇的搜索、布尔操作符的使用（如 AND、OR、NOT），以及接近度搜索等。

to_tsquery() 的基本语法如下：

to_tsquery(text_config, querytext)

其中 text_config 是文本搜索配置的名称（比如 'english'），它定义了如何解析和规范化查询文。querytext 是你想要转换的查询字符串。

这个转换步骤非常重要，我们通常希望在关键词上进行“模糊匹配”。例如，如果用户搜索的是“eggs”，而某一列的值为“egg”，我们仍然希望返回匹配结果。通过使用 to_tsquery() 函数，可以实现这种模糊匹配，从而提高搜索的灵活性和准确性。

匹配符：@@

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

以下示例为例：

select *
from books
where title = 'Harry';

上文中的等号符号（=）在匹配时非常“严格”。在全文搜索的上下文中，我们可能希望找到所有“Harry Potter”系列书籍，因此我们可以重写上述示例：

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

执行上述命令，结果如下：

基本全文查询

搜索单个列

要找到所有描述中包含单词“big”的书籍：

select
  *
from
  books
where
  to_tsvector(description)
  @@ to_tsquery('big');

搜索多列

要找到所有描述或标题中包含单词“little”的书籍，采用空格分割的方式来合并多个列。

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

执行上述命令，结果如下：

匹配所有搜索词

要找到所有描述中同时包含“little”和“big”这两个词的书籍，我们可以使用符号& ：

select
  *
from
  books
where
  to_tsvector(description)
  @@ to_tsquery('little & big'); -- use & for AND in the search query

执行上述命令，结果如下：

匹配任意搜索词

要找到所有描述中包含“little”或“big”任意一个词的书籍，可以使用符号 | ：

select
  *
from
  books
where
  to_tsvector(description)
  @@ to_tsquery('little | big'); -- use | for OR in the search query

执行上述命令，结果如下：

请注意，搜索“big”这个词时，结果中会包括包含“bigger”（或“biggest”等）的记录。

创建索引

现在我们已经让全文搜索功能正常工作，让我们来创建一个索引。这将允许 Postgres 预先“构建”文档，这样在我们执行查询时就不需要再创建文档了。这将使我们的查询速度大大提升。

可搜索列

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

我们可以利用 Postgres 的一个特殊功能，称为生成列，以确保每当标题和描述列的值发生变化时，索引都会更新。

alter table
  books
add column
  fts tsvector generated always as (to_tsvector('english', description || ' ' || title)) stored;

create index books_fts on books using gin (fts); -- generate the index

select id, description , title, fts
from books;

执行上述命令，结果如下：

使用新列进行搜索

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

select
  *
from
  books
where
  fts @@ to_tsquery('little & big');

查询运算符

请访问 PostgreSQL：文本搜索函数和操作符来了解您可以使用的其他查询操作符，以便进行更高级的全文查询，例如：

近似符：<->

接近性符号对于搜索有一定“距离”的术语非常有用。例如，要找到短语“big dreams”，其中“big”的匹配紧随“dreams”的匹配之后：

select
  *
from
  books
where
  to_tsvector(description) @@ to_tsquery('big <-> dreams');

我们还可以使用 <-> 来查找彼此之间有一定距离的单词。例如，要找到“year”和“school”这两个词在彼此之间相隔不超过2个词的情况：

select
  *
from
  books
where
  to_tsvector(description) @@ to_tsquery('year <2> school');

执行上述命令，结果如下：

否定：!

否定符号可以用来查找不包含搜索词的短语。例如，要找到包含“big”这个词但不包含“little”的记录：

select
  *
from
  books
where
  to_tsvector(description) @@ to_tsquery('big & !little');

执行上述命令，结果如下：