PostgreSQL全文搜索功能详解与使用指南

{"position":0,"title":"介绍","layout":"doc-fullscreen","text":"## 介绍nn在本实验中，你将学习如何在 PostgreSQL 中实现全文搜索。全文搜索提供了搜索自然语言文档中特定单词或短语的能力。你将从创建一个 `articles` 表并添加示例数据开始。然后，你将添加一个 `tsvector` 类型的 `search_vector` 列，这是一种用于存储处理后文本的专用数据类型。你将创建一个索引来加速搜索，然后使用 `tsquery` 执行查询。最后，你将设置一个触发器，以便在源文本修改时自动更新搜索数据，确保你的搜索索引保持最新。n","need_verify":false,"has_solution":false},{"position":1,"title":"创建表并插入示例数据","layout":"doc-workbench-split","text":"## 创建表并插入示例数据nn在此步骤中，你将连接到 PostgreSQL 数据库，创建一个 `articles` 表，并插入一些示例数据。此表将作为实现和测试全文搜索的基础。nn首先，打开终端并以 `postgres` 用户身份连接到 PostgreSQL 交互式 shell (`psql`)。本实验中后续的所有 SQL 命令都将在该 shell 中运行。nn```bashnsudo -u postgres psqln```nn你现在将看到 PostgreSQL 提示符，它看起来像 `postgres=#`。nn接下来，创建 `articles` 表。此表将存储每篇文章的 ID、标题和主要内容。nn```sqlnCREATE TABLE articles (n id SERIAL PRIMARY KEY,n title VARCHAR(255) NOT NULL,n content TEXTn);n```nn现在，向 `articles` 表中插入三篇示例文章。这些数据将在后续步骤中用于你的搜索查询。nn```sqlnINSERT INTO articles (title, content) VALUESn('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),n('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),n('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');n```nn为了确认数据已正确插入，请运行一个 `SELECT` 查询来查看表中的所有记录。nn```sqlnSELECT * FROM articles;n```nn你应该会看到你刚刚插入的三行数据，输出类似于下方：nn```n id | title | contentn----+----------------------------------+-----------------------------------------------------------------------n 1 | PostgreSQL Tutorial | This is a compr完整结果如下，共 3 条记录： 1 | PostgreSQL 综合教程 | 系统讲解 PostgreSQL 的完整用法。 2 | PostgreSQL 全文搜索 | 介绍如何在 PostgreSQL 中使用 TSVECTOR 实现全文搜索。 3 | PostgreSQL 性能调优 | 分享提升 PostgreSQL 数据库性能的实用技巧。 （3 行） 为了实现全文搜索，需要准备一个 `tsvector` 类型的列。`tsvector` 本质上是一个排好序、去重后的词项列表，这些词项会经过标准化处理，用来合并同一词的不同变形。接下来这一步，就是新增一个 `tsvector` 列，并为它建立索引，从而让搜索查询更快。 先在 `articles` 表中新增一个名为 `search_vector` 的列，类型为 `TSVECTOR`。 ```sql ALTER TABLE articles ADD COLUMN search_vector TSVECTOR; ``` 然后，把每篇文章的 `title` 和 `content` 转换成 `tsvector`，并写入这个新列。这里用到的是 `to_tsvector` 函数。它会对文本做预处理，比如去掉常见停用词，并将单词还原为词干形式。ATE articlesnSET search_vector = to_tsvector('english', title || ' ' || content);n```nn在此命令中，`'english'` 指定了文本搜索配置，`||` 是用于连接标题和内容的连接运算符。nn为了显著加快全文搜索速度，请在 `search_vector` 列上创建一个 GIN（广义倒排索引）。GIN 索引对于索引复合值（如 `tsvector`）非常有效。nn```sqlnCREATE INDEX articles_search_idx ON articles USING GIN (search_vector);n```nn你可以通过使用 `\d` 命令检查表结构来验证列和索引是否已添加。nn```sqln\d articlesn```nn输出现在应包含 `search_vector` 列和 `articles_search_idx` GIN 索引。nn```n Table "public.articles"n Column | Type | Collation | Nullable | Defaultn---------------+------------------------+-----------+----------+-----------------------------------------n id | integer | | not null | nextval('articles_id_seq'::regclass)n title | character varying(255) | | not null |n content | text | | |n search_vector | tsvector | | |nIndexes:n "articles_pkey" PRIMARY KEY, btree (id)n "articles_search_idx" gin (search_vector)n```n","need_verify":true,"has_solution":false},{"position":3,"title":"执行全文搜索","layout":"doc-workbench-split","text":"## 执行全文搜索nn有了 `tsvector` 列和 GIN 索引后，你现在可以执行高效的全文搜索了。查询使用 `tsquery` 数据类型构建，它代表一个文本查询。`@@` 运算符用于将 `tsquery` 与 `tsvector` 进行匹配。nn首先，让我们搜索包含单词 `search` 的文章。`to_tsquery` 函数将搜索字符串转换为 `tsquery`。nn```sqlnSELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');n```nn此查询将返回关于“Full Text Search”的文章。nn```n title | contentn----------------------------------+-----------------------------------------------------------------------n Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.n(1 row)n```nn你也可以在查询中使用运算符。`&` (AND) 运算符查找包含所有指定词的文档。让我们搜索同时包含 `PostgreSQL` 和 `performance` 的文章。nn```sqlnSELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');n```nn这将返回关于性能调优的文章。nn```n titlen-------------------------------n PostgreSQL Performance Tuningn(1 row)n```nn`|` (OR) 运算符查找包含指定词中至少一个的文档。让我们搜索包含 `tutorial` 或 `tuning` 的文章。nn```sqlnSELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');n```nn此查询将返回两篇文章。nn```n titlen-------------------------------n PostgreSQL Tutorialn PostgreSQL Performance Tuningn(2 rows)n```n","need_verify":true,"has_solution":false},{"position":4,"title":"使用触发器自动化更新","layout":"doc-workbench-split","text":"## 使用触发器自动化更新nn当前，`search_vector` 列是静态的。如果你更新文章的标题或内容，`search_vector` 不会自动反映更改。为了解决这个问题，你可以创建一个触发器，在对 `articles` 表执行任何 `INSERT` 或 `UPDATE` 操作之前自动更新 `search_vector` 列。nn首先，定义一个将由触发器执行的函数。此函数更新新行或更新行的 `search_vector`。nn```sqlnCREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS TRIGGER AS $$nBEGINn NEW.search_vector :=n to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content);n RETURN NEW;nENDn$$ LANGUAGE plpgsql;n```nn函数中的 `NEW` 变量指的是正在插入或更新的行。nn接下来，创建触发器本身。此触发器将在每行插入或更新之前调用 `articles_tsvector_update` 函数。nn```sqlnCREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATEnON articles FOR EACH ROW EXECUTE 现在可以来验证一下这个触发器是否真的生效了。先插入一篇和数据库索引相关的新文章。 ```sql INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.'); ``` 因为触发器已经配置好了，这条新记录的 `search_vector` 会在写入时自动完成填充。要验证这一点，可以直接搜索 `indexes` 这个词。 ```sql SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes'); ``` 如果一切正常，这篇新文章就会出现在查询结果里。 ```sql title --------------------- Database Indexing (1 row) ``` 这也说明触发器运行正常，能够让全文搜索索引始终和实际数据保持同步。 ## 总结 通过这个实验，你已经掌握了在 PostgreSQL 中实现全文搜索的基本方法。整个过程包括：创建数据表、写入数据，并通过增加 `tsvector` 列让表具备全文搜索能力；使用 `to_tsvector` 函数处理文本；借助 GIN 索引提升搜索性能；再结合 `tsquery` 以及不同运算符完成搜索实践。最后，还实现了一个触发器，用来自动维护搜索索引与数据之间的一致性。这一步很关键，它直接关系到搜索系统是否稳定、可靠。eed_verify":false,"has_solution":false}