pgsql扩展-全文搜索

查看pgsql一共有哪些扩展

SELECT * FROM pg_available_extensions;

查看我安装了哪些扩展

SELECT * FROM pg_extension;

很多时候，你的项目并不需要为了几十万条数据专门去维护一个笨重的 ES 集群。PG 内置的 tsvector 和 tsquery 就能实现专业的全文搜索。

核心原理： tsvector：将文本分词并转换成“搜索向量”。

tsquery：你的搜索关键词。

基础搜索示例：

-- 创建文章表
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT
);

-- 插入一些演示数据（包含英文和中文，方便对比）
INSERT INTO posts (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive guide to PostgreSQL full-text search.'),
('Go Language Web Framework', 'Learn how to build high-performance web apps with Go and Gin.'),
('枫枫老师的数据库课', 'PostgreSQL 是一款功能强大的开源关系型数据库。'),
('枫枫网盘项目实战', '手把手教你用 Go 语言和 MinIO 实现分布式网盘。');

中文搜索：默认的 PG 不支持中文分词，通常我们会安装zhparser插件。这样它就能像 ES 一样，把“枫枫网盘项目”切分成“枫枫”、“网盘”、“项目”来进行匹配。
为什么用它？

事务一致性：数据更新后，搜索索引立刻生效，不会像 ES 那样有同步延迟。

省钱省资源：一个数据库干两份活，不用额外开内存给 Java 虚拟机（ES）。

英文全文检索示例

-- 场景：搜索包含 "PostgreSQL" 或 "Guide" 的文章
-- 使用 'english' 分词配置
SELECT title, content
FROM posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'PostgreSQL | Guide');

原理拆解：

to_tsvector：它会把一段话拆成一个个“词元”，并去掉“the”、“is”这种没意义的虚词。
@@：这是全文搜索的操作符，表示“匹配”。
&(与)、|(或)、!** (非)**：可以在查询中使用逻辑判断。

预分词

但是这是是在查询的时候去分词然后去查询，数据量大的会性能很差，所以正常情况下都是预分词

CREATE TABLE posts1 (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    -- 定义一个自动生成的列，专门用于搜索
    -- STORED 关键字表示这个结果会物理存储在硬盘上，而不是查询时才计算
    searchable_index_col tsvector GENERATED ALWAYS AS (
        to_tsvector('english', title || ' ' || content)
    ) STORED
);

-- 建完表后，紧接着给这个预处理好的列加上 GIN 索引
CREATE INDEX idx_posts_search ON posts1 USING GIN (searchable_index_col);

-- 插入时不需要管 searchable_index_col，数据库会自动生成它
INSERT INTO posts1 (title, content)
VALUES ('PostgreSQL Guide', 'This is a post about full-text search index.');

INSERT INTO posts1 (title, content)
VALUES ('Go Programming', 'Let us learn how to use Gin framework in Go.');

SELECT title, searchable_index_col FROM posts1;
-- 查包含 "PostgreSQL" 的文章
-- 注意：这里依然要用 to_tsquery 将你的搜索词转化为查询向量
SELECT title, content
FROM posts1
WHERE searchable_index_col @@ to_tsquery('english', 'PostgreSQL');

-- 复杂的逻辑查询：包含 "Go" 但不包含 "Gin"
SELECT * FROM posts1
WHERE searchable_index_col @@ to_tsquery('english', 'Go & !Gin');

中文分词

pgsql默认只支持英文分词，中文分词需要编译扩展，建议直接用对应的docker镜像

# 这里推荐使用社区预集成好的镜像，省去手动编译的痛苦
docker pull zhparser/zhparser:alpine-16
docker run -d --name pg-chinese -p 5432:5432 -e POSTGRES_PASSWORD=root zhparser/zhparser:alpine-16

在库中初始化配置（只需执行一次）：

CREATE EXTENSION IF NOT EXISTS zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple;

如何检查有没有安装成功

SELECT to_tsvector('chinese', '枫枫老师的 Go 语言教程'); 
-- 看看这个能不能正常输出
-- 'go':4 '教程':6 '枫':1,2 '老师':3 '语言':5

中文分词的示例

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    -- 定义一个自动生成的列，专门用于搜索
    -- STORED 关键字表示这个结果会物理存储在硬盘上，而不是查询时才计算
    searchable_index_col tsvector GENERATED ALWAYS AS (
        to_tsvector('chinese', title || ' ' || content)
    ) STORED
);

-- 建完表后，紧接着给这个预处理好的列加上 GIN 索引
CREATE INDEX idx_posts_search ON posts USING GIN (searchable_index_col);

-- 插入时不需要管 searchable_index_col，数据库会自动生成它
INSERT INTO posts (title, content)
VALUES ('枫枫老师的pgsql视频教程', '这是一门非常有趣的数据库教程');

INSERT INTO posts (title, content)
VALUES ('刘波的mysql教程', '是一门有趣的数据库教程');

SELECT title, searchable_index_col FROM posts;

SELECT title, content
FROM posts
WHERE searchable_index_col @@ to_tsquery('chinese', '枫枫 | 数据库');

SELECT * FROM posts
WHERE searchable_index_col @@ to_tsquery('chinese', 'mysql');