PostgreSQL 全文搜索

介绍

在这个实验中，你将学习如何使用 TSVECTOR 在 PostgreSQL 中实现全文搜索。你将从创建一个 articles 表并添加示例数据开始。然后，你将添加一个 search_vector 列，并在其上创建一个 TSVECTOR 索引。最后，你将设置一个触发器，以便在 title 或 content 列被修改时自动更新 search_vector 列。这将使你能够执行高效的全文搜索。

Skills Graph

设置 Articles 表

在这个步骤中，你将创建 articles 表并插入一些示例数据。这个表将被用于演示 PostgreSQL 中的全文搜索功能。

首先，使用终端中的以下命令，以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

现在，使用以下 SQL 命令创建 articles 表：

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

这个命令创建了一个名为 articles 的表，包含三个列：id、title 和 content。id 列是一个自增的主键。

接下来，将一些示例数据插入到 articles 表中：

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

这个命令将三行数据插入到 articles 表中，每行数据都有不同的标题和内容。

你可以通过运行以下查询来验证数据是否已正确插入：

SELECT * FROM articles;

你应该能看到你刚刚插入的三行数据。

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

添加 search_vector 列并创建 GIN 索引

在这个步骤中，你将向 articles 表添加 search_vector 列，并在其上创建一个 GIN 索引。search_vector 列将存储 title 和 content 列的 TSVECTOR 表示形式，而 GIN 索引将加速全文搜索。

首先，将 search_vector 列添加到 articles 表：

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

接下来，使用 title 和 content 列的 TSVECTOR 表示形式更新 search_vector 列。你可以使用 to_tsvector 函数将文本转换为 TSVECTOR。

UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);

在上面的命令中，'english' 指定用于文本处理的语言配置。 || 运算符将 title 和 content 列用空格连接起来。

现在，在 search_vector 列上创建一个 GIN 索引：

CREATE INDEX articles_search_vector_idx ON articles USING GIN (search_vector);

GIN (Generalized Inverted Index，广义倒排索引) 是一种非常适合于索引 TSVECTOR 列的索引类型。这个索引将允许 PostgreSQL 快速找到与给定搜索查询匹配的文档。

你可以通过运行以下命令来验证索引是否已创建：

\d articles

你应该在输出中看到 articles_search_vector_idx 索引。

                                     Table "public.articles"
      Column      |          Type          | Collation | Nullable |                 Default
------------------+------------------------+-----------+----------+-----------------------------------------
 id               | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title            | character varying(255) |           |          |
 content          | text                   |           |          |
 search_vector    | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_vector_idx" gin (search_vector)

使用 TSQUERY 执行全文搜索

现在你已经设置好了 search_vector 列和 GIN 索引，你可以使用 TSQUERY 运算符执行全文搜索了。

PostgreSQL 提供了 to_tsquery 函数，用于将文本字符串转换为 TSQUERY 对象。然后，你可以使用 @@ 运算符将 TSQUERY 与 search_vector 列进行匹配。

例如，要搜索同时包含“PostgreSQL”和“tutorial”的文章，你可以使用以下查询：

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & tutorial');

to_tsquery 函数中的 & 运算符指定 search_vector 中必须同时存在这两个术语（term）。

你也可以使用 | 运算符来搜索包含“PostgreSQL”或“tutorial”的文章：

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL | tutorial');

要搜索包含短语“full text search”的文章，你可以使用以下查询：

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', '''full text search''');

将短语括在单引号中告诉 to_tsquery 将其视为单个短语。

尝试使用不同的搜索查询，看看 TSQUERY 运算符是如何工作的。

按相关性对结果进行排序

PostgreSQL 提供了 ts_rank 函数，用于按相关性对搜索结果进行排序。 ts_rank 函数将 search_vector 和 TSQUERY 作为输入，并返回一个排序值（rank value）。

要按相关性对搜索结果进行排序，你可以使用以下查询：

SELECT title, ts_rank(search_vector, to_tsquery('english', 'PostgreSQL')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL')
ORDER BY rank DESC;

这个查询搜索包含“PostgreSQL”的文章，使用 ts_rank 函数计算每篇文章的排序值，然后按排序值降序排列结果。

输出中的 rank 列显示了每篇文章的相关性得分。具有较高相关性得分的文章会首先列出。

使用触发器保持 search_vector 列的更新

为了确保 search_vector 列始终是最新的，你可以创建一个触发器，以便在 title 或 content 列被修改时自动更新该列。

首先，创建一个触发器函数，用于更新 search_vector 列：

CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这个函数接受 title 和 content 列的新值，并使用连接值的 TSVECTOR 表示形式更新 search_vector 列。

接下来，创建一个触发器，每当在 articles 表中插入或更新行时，该触发器都会调用 update_search_vector 函数：

CREATE TRIGGER articles_update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

每当 title 或 content 列被修改时，此触发器将自动更新 search_vector 列。

要测试触发器，请更新现有文章的内容：

UPDATE articles SET content = 'This is an updated comprehensive tutorial on PostgreSQL with advanced features.' WHERE id = 1;

现在，搜索包含单词“features”的文章：

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'features');

你应该在搜索结果中看到更新后的文章。

总结

在这个实验中，你已经学习了如何使用 TSVECTOR 在 PostgreSQL 中实现全文搜索。你创建了一个 articles 表，添加了一个 search_vector 列，在 search_vector 列上创建了一个 GIN 索引，并设置了一个触发器，以便在 title 或 content 列被修改时自动更新 search_vector 列。你还学习了如何使用 TSQUERY 运算符执行全文搜索，以及如何按相关性对搜索结果进行排序。