PostgreSQL教程FG058-PG全文检索：基础配置与关键词查询实操

本文档风哥主要介绍PostgreSQL教程058相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

1. 全文检索概述

全文检索（Full Text Search）是一种在文本数据中搜索关键词的技术，比LIKE查询更高效、更智能。

全文检索优势：

支持自然语言搜索
支持词干提取和词形还原
支持相关性排序
支持多语言

— 查看可用的文本搜索配置
SELECT cfgname, cfgnamespace::regnamespace
FROM pg_ts_config
ORDER BY cfgname;

执行结果：

更多学习教程公众号风哥教程itpux_com

2. 文本分词

2.1 基本分词操作

— 将文本转换为tsvector
SELECT to_tsvector(‘english’, ‘The quick brown fox jumps over the lazy dog’);

执行结果：

from oracle:www.itpux.com

to_tsvector
———————————————-
‘brown’:3 ‘dog’:9 ‘fox’:4 ‘jumps’:5 ‘lazy’:8 ‘quick’:2
(1 row)

— 中文简单分词
SELECT to_tsvector(‘simple’, ‘PostgreSQL是一个功能强大的开源数据库’);

执行结果：

to_tsvector
————————————————————-
‘postgresql’:1 ‘一个’:2 ‘功能’:3 ‘强大’:4 ‘开源’:5 ‘数据库’:6
(1 row)

2.2 分词器解析

— 查看分词过程
SELECT * FROM ts_debug(‘english’, ‘The quick brown fox jumps over the lazy dog’);

执行结果：

alias | description | token | dictionaries | dictionary | lexemes
———–+—————–+———–+—————–+————–+———–
asciiword | Word, all ASCII | The | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | quick | {english_stem} | english_stem | {quick}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | brown | {english_stem} | english_stem | {brown}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | fox | {english_stem} | english_stem | {fox}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | jumps | {english_stem} | english_stem | {jump}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | over | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | the | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | lazy | {english_stem} | english_stem | {lazy}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | dog | {english_stem} | english_stem | {dog}
(21 rows)

3. 全文检索查询

3.1 创建查询向量

— 创建tsquery查询
SELECT to_tsquery(‘english’, ‘quick & brown’);

执行结果：

to_tsquery
————
‘brown’ & ‘quick’
(1 row)

— 创建短语查询
SELECT phraseto_tsquery(‘english’, ‘quick brown fox’);

执行结果：

风哥提示：

phraseto_tsquery
—————————
‘quick’ <-> ‘brown’ <-> ‘fox’
(1 row)

3.2 匹配操作

— 使用@@操作符匹配
SELECT
‘The quick brown fox’::tsvector @@ ‘quick & brown’::tsquery AS match_1,
‘The quick brown fox’::tsvector @@ ‘quick & cat’::tsquery AS match_2,
‘The quick brown fox’::tsvector @@ ‘quick | cat’::tsquery AS match_3,
‘The quick brown fox’::tsvector @@ ‘!cat’::tsquery AS match_4;

执行结果：

更多视频教程www.fgedu.net.cn

3.3 查询操作符

— 查询操作符示例
SELECT
to_tsquery(‘quick & brown’) AS and_query,
to_tsquery(‘quick | brown’) AS or_query,
to_tsquery(‘!quick’) AS not_query,
to_tsquery(‘quick <-> brown’) AS phrase_query,
to_tsquery(‘quick <5> brown’) AS near_query;

执行结果：

4. 创建全文检索索引

4.1 创建测试表

— 创建文章表
CREATE TABLE fgedu_articles_fts (
article_id SERIAL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
author VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入测试数据
INSERT INTO fgedu_articles_fts (title, content, author) VALUES
(‘PostgreSQL入门教程’, ‘PostgreSQL是一个功能强大的开源关系数据库管理系统，支持SQL标准和ACID事务。’, ‘风哥1号’),
(‘数据库优化技巧’, ‘数据库性能优化包括索引优化、查询优化、配置优化等多个方面。PostgreSQL提供了丰富的工具。’, ‘风哥2号’),
(‘SQL查询进阶’, ‘SQL查询包括简单查询、连接查询、子查询等。掌握SQL查询是数据库开发的基础技能。’, ‘王五’),
(‘PostgreSQL高可用架构’, ‘PostgreSQL高可用方案包括流复制、逻辑复制、Patroni等。保证数据库服务持续可用。’, ‘赵六’),
(‘全文检索实践’, ‘全文检索是数据库的重要功能，PostgreSQL内置全文检索支持，可以高效搜索文本内容。’, ‘风哥1号’);

— 创建全文检索索引
CREATE INDEX idx_articles_content_fts ON fgedu_articles_fts
USING GIN(to_tsvector(‘english’, content));

— 创建标题+内容联合索引
CREATE INDEX idx_articles_title_content_fts ON fgedu_articles_fts
USING GIN(to_tsvector(‘english’, title || ‘ ‘ || content));

执行结果：

学习交流加群风哥QQ113257174

CREATE TABLE fgedu_INSERT 0 5
CREATE INDEX
CREATE INDEX

4.2 使用索引查询

— 全文检索查询
SELECT
article_id,
title,
author,
ts_headline(‘english’, content, to_tsquery(‘english’, ‘PostgreSQL’)) AS highlighted
FROM fgedu_articles_fts
WHERE to_tsvector(‘english’, content) @@ to_tsquery(‘english’, ‘PostgreSQL’);

执行结果：

学习交流加群风哥微信: itpux-com

5. 中文全文检索

5.1 使用zhparser扩展

— 检查zhparser是否安装
SELECT * FROM pg_available_extensions WHERE name = ‘zhparser’;

— 如果已安装，可以创建中文配置
— 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;

— 使用simple配置处理中文
SELECT to_tsvector(‘simple’, ‘PostgreSQL数据库是一个功能强大的开源数据库系统’);

执行结果：

name | default_version | installed_version | comment
——+—————–+——————-+———
(0 rows)

to_tsvector
————————————————————-
‘postgresql’:1 ‘数据库’:2,6 ‘一个’:3 ‘功能’:4 ‘强大’:5 ‘开源’:7 ‘系统’:8
(1 row)

5.2 中文全文检索查询

— 中文全文检索
SELECT
article_id,
title,
content
FROM fgedu_articles_fts
WHERE to_tsvector(‘simple’, content) @@ to_tsquery(‘simple’, ‘数据库 & 优化’);

执行结果：

article_id | title | content
————+—————–+——————————————————-
2 | 数据库优化技巧 | 数据库性能优化包括索引优化、查询优化、配置优化等多个方面…
(1 row)

6. 实战案例：文章搜索

6.1 相关性排序

— 按相关性排序
SELECT
article_id,
title,
author,
ts_rank(to_tsvector(‘english’, content), to_tsquery(‘english’, ‘PostgreSQL’)) AS rank,
ts_headline(‘english’, content, to_tsquery(‘english’, ‘PostgreSQL’)) AS highlighted
FROM fgedu_articles_fts
WHERE to_tsvector(‘english’, content) @@ to_tsquery(‘english’, ‘PostgreSQL’)
ORDER BY rank DESC;

执行结果：

6.2 综合搜索功能

— 创建搜索函数
CREATE OR REPLACE FUNCTION fgedu_search_articles(
p_keyword TEXT,
p_limit INTEGER DEFAULT 10
) RETURNS TABLE (
article_id INTEGER,
title VARCHAR,
author VARCHAR,
rank REAL,
snippet TEXT
) AS $$
BEGIN
RETURN QUERY
SELECT
a.article_id,
a.title,
a.author,
ts_rank(
to_tsvector(‘english’, a.title || ‘ ‘ || a.content),
to_tsquery(‘english’, p_keyword)
)::REAL AS rank,
ts_headline(
‘english’,
a.content,
to_tsquery(‘english’, p_keyword),
‘MaxWords=50, MinWords=10’
) AS snippet
FROM fgedu_articles_fts a
WHERE to_tsvector(‘english’, a.title || ‘ ‘ || a.content) @@ to_tsquery(‘english’, p_keyword)
ORDER BY rank DESC
LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

— 使用搜索函数
SELECT * FROM fgedu_search_articles(‘fgedudb | PostgreSQL’, 5);

执行结果：

7. 清理环境

— 删除函数
DROP FUNCTION IF EXISTS fgedu_search_articles(TEXT, INTEGER);

— 删除测试表
DROP TABLE IF EXISTS fgedu_articles_fts;

执行结果：

DROP FUNCTION
DROP TABLE

风哥教程风哥教程风哥教程总结：PostgreSQL全文检索功能强大，支持多语言分词、复杂查询表达式、相关性排序等特性。使用GIN索引可以加速全文检索查询。对于中文全文检索，可以安装zhparser扩展获得更好的分词效果。全文检索适用于文章搜索、日志分析、文档管理等场景。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG058-PG全文检索：基础配置与关键词查询实操

1. 全文检索概述

2. 文本分词

2.1 基本分词操作

2.2 分词器解析

3. 全文检索查询

3.1 创建查询向量

3.2 匹配操作

3.3 查询操作符

4. 创建全文检索索引

4.1 创建测试表

4.2 使用索引查询

5. 中文全文检索

5.1 使用zhparser扩展

5.2 中文全文检索查询

6. 实战案例：文章搜索

6.1 相关性排序

6.2 综合搜索功能

7. 清理环境

相关推荐

联系我们