在本实验中，你将学习如何在 PostgreSQL 中实现全文搜索。全文搜索提供了搜索自然语言文档中特定单词或短语的能力。你将从创建一个 articles 表并添加示例数据开始。然后，你将添加一个 tsvector 类型的 search_vector 列，这是一种用于存储处理后文本的专用数据类型。你将创建一个索引来加速搜索，然后使用 tsquery 执行查询。最后，你将设置一个触发器，以便在源文本修改时自动更新搜索数据，确保你的搜索索引保持最新。
在此步骤中，你将连接到 PostgreSQL 数据库，创建一个 articles 表，并插入一些示例数据。此表将作为实现和测试全文搜索的基础。
首先，打开终端并以 postgres 用户身份连接到 PostgreSQL 交互式 shell (psql)。本实验中后续的所有 SQL 命令都将在该 shell 中运行。
sudo -u postgres psql你现在将看到 PostgreSQL 提示符，它看起来像 postgres=#。
接下来，创建 articles 表。此表将存储每篇文章的 ID、标题和主要内容。
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT
);现在，向 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.');为了确认数据已正确插入，请运行一个 SELECT 查询来查看表中的所有记录。
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)为了执行全文搜索，你需要一个 tsvector 类型的列。tsvector 值是一个已排序的、不重复的词（词素）列表，这些词已被标准化以合并同一词的不同变体。在此步骤中，你将添加一个 tsvector 列并在其上创建索引以加速搜索查询。
首先，向 articles 表添加一个名为 search_vector 的新列，类型为 TSVECTOR。
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;接下来，通过将每篇文章的 title 和 content 转换为 tsvector 来填充此新列。to_tsvector 函数用于此转换。它通过移除常用词（停用词）和将词还原为其词根形式（词干提取）来处理文本。
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);在此命令中，'english' 指定了文本搜索配置，|| 是用于连接标题和内容的连接运算符。
为了显著加快全文搜索速度，请在 search_vector 列上创建一个 GIN（广义倒排索引）。GIN 索引对于索引复合值（如 tsvector）非常有效。
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);你可以通过使用 \d 命令检查表结构来验证列和索引是否已添加。
\d articles输出现在应包含 search_vector 列和 articles_search_idx GIN 索引。
Table "public.articles"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('articles_id_seq'::regclass)
title | character varying(255) | | not null |
content | text | | |
search_vector | tsvector | | |
Indexes:
"articles_pkey" PRIMARY KEY, btree (id)
"articles_search_idx" gin (search_vector)有了 tsvector 列和 GIN 索引后，你现在可以执行高效的全文搜索了。查询使用 tsquery 数据类型构建，它代表一个文本查询。@@ 运算符用于将 tsquery 与 tsvector 进行匹配。
首先，让我们搜索包含单词 search 的文章。to_tsquery 函数将搜索字符串转换为 tsquery。
SELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');此查询将返回关于“Full Text Search”的文章。
title | content
----------------------------------+-----------------------------------------------------------------------
Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
(1 row)你也可以在查询中使用运算符。& (AND) 运算符查找包含所有指定词的文档。让我们搜索同时包含 PostgreSQL 和 performance 的文章。
SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');这将返回关于性能调优的文章。
title
-------------------------------
PostgreSQL Performance Tuning
(1 row)| (OR) 运算符查找包含指定词中至少一个的文档。让我们搜索包含 tutorial 或 tuning 的文章。
SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');此查询将返回两篇文章。
title
-------------------------------
PostgreSQL Tutorial
PostgreSQL Performance Tuning
(2 rows)当前，search_vector 列是静态的。如果你更新文章的标题或内容，search_vector 不会自动反映更改。为了解决这个问题，你可以创建一个触发器，在对 articles 表执行任何 INSERT 或 UPDATE 操作之前自动更新 search_vector 列。
首先，定义一个将由触发器执行的函数。此函数更新新行或更新行的 search_vector。
CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content);
RETURN NEW;
END
$$ LANGUAGE plpgsql;函数中的 NEW 变量指的是正在插入或更新的行。
接下来，创建触发器本身。此触发器将在每行插入或更新之前调用 articles_tsvector_update 函数。
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_tsvector_update();现在，让我们测试一下触发器。首先，插入一篇关于数据库索引的新文章。
INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.');由于触发器的存在，这篇新文章的 search_vector 已被自动填充。你可以通过搜索单词 indexes 来验证这一点。
SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes');新文章应该会出现在结果中。
title
---------------------
Database Indexing
(1 row)这证实了你的触发器正在正常工作，确保你的全文搜索索引与你的数据保持同步。
在本实验中，你学习了在 PostgreSQL 中实现全文搜索的基础知识。你成功创建了一个表，填充了数据，并通过添加 tsvector 列将其配置为支持全文搜索。你学会了如何使用 to_tsvector 函数处理文本，以及如何使用 GIN 索引来优化搜索性能。你还练习了使用 tsquery 和不同的运算符执行搜索。最后，你实现了一个触发器来自动化保持搜索索引与数据同步的过程，这是维护可靠搜索系统的关键一步。
