openGauss每日一练第20天|学习openGauss全文检索学习心得

原创梦终究只能在梦里圆 2021-12-20

835

👉openGauss SQL学习参考资料
https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html

学习目标

学习openGauss全文检索

openGauss 提供了两种数据类型，用于支持全文检索。tsvector 类型表示为文本搜索优化的文件格式，tsquery 类型表示文本查询

课程学习

连接数据库

#第一次进入等待15秒 #数据库启动中... su - omm gsql -r

1.tsvector

–把一个字符串跟着空格进行分词，分词的顺序是按照长短和字母排序的，自动消失分词中重复的词条

SELECT 'The Fat Rats'::tsvector;

–词条位置常量也可以用词汇中

SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;

——拥有位置的词汇甚至可以用一个权利来标记，表现文档结构，这个权利可以是A，B，C或D。默认是D，因此输出中不会出现

SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;

–to_tsvector函数对这些单词进行规范化处理，罗状词条并图片文档中的位置

SELECT to_tsvector('english', 'The Fat Rats');

2.tsquery

SELECT 'fat & rat'::tsquery;

– 规范化转为tsquery类型

SELECT to_tsquery('Fat:ab & Cats');

3.基本文本匹配

–全文检索基于匹配算子@@，当一个tsvector匹配到一个tsquery时，则返回true，tsvector和tsquery两种数据类型可以任意排序。

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery AS RESULT;
SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector AS RESULT;

– to_ts和to_tsquery向量处理

SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat') AS RESULT;
SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & cow') AS RESULT;

4.分词器

–查看所有分词器

\dF

–查看默认分词器

show default_text_search_config;

5.表和索引

CREATE SCHEMA tsearch;
CREATE TABLE tsearch.pgweb(id int, body text, title text, last_mod_date date);
INSERT INTO tsearch.pgweb VALUES(1, 'China, officially the People''s Republic of China(PRC), located in Asia, is the world''s most populous state.', 'China', '2010-1-1');
INSERT INTO tsearch.pgweb VALUES(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1');
INSERT INTO tsearch.pgweb VALUES(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England','2010-1-1');

– 将body 领域中包含美国的行打印出来

SELECT id, body, title FROM tsearch.pgweb WHERE to_tsvector(body) @@ to_tsquery('america');

–检索出在title或者body领域中包含china和asia的行

SELECT title FROM tsearch.pgweb WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('china & asia');

——为了加速文本搜索，可以创建GIN索引（指定英文配置来解析和规范化字符串）

CREATE INDEX pgweb_idx_1 ON tsearch.pgweb USING gin(to_tsvector('english', body));

–连接列的索引

CREATE INDEX pgweb_idx_3 ON tsearch.pgweb USING gin(to_tsvector('english', title || ' ' ||
body));

–查看索引定义

\d+ tsearch.pgweb

6. 清理数据

删除模式 tsearch 级联；

课程作业

#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r

1.用tsvector @@tsquery 和tsquery @@tsvector 完成两个基本文本匹配

SELECT 'HM'::tsvector @@'cat & rat'::tsquery AS RESULT;
SELECT 'fat & cow'::tsquery @@ 'HM'::tsvector AS RESULT;

3.创建GIN索引

CREATE SCHEMA tsearch;
CREATE TABLE tsearch.pgweb(id int, body text, title text, last_mod_date date);
INSERT INTO tsearch.pgweb VALUES(1, 'GHM，GH(PRC)，GHMNFB。', 'GHM', '2010-1- 1');
INSERT INTO tsearch.pgweb VALUES(2, 'GHMN，GHMNFB', 'America', '2010-1-1');
INSERT INTO tsearch.pgweb VALUES(3, 'GHMNF。GHMNFB。', 'England','2010-1-1' );

–检索出在title或者body字段中包含china和asia的行
SELECT title FROM tsearch.pgweb WHERE to_tsvector(title || ' ' || body) @@to_tsquery('china & asia');
CREATE INDEX pgweb_idx_1 ON tsearch.pgweb USING gin(to_tsvector('english', body));
CREATE INDEX pgweb_idx_3 ON tsearch.pgweb USING gin(to_tsvector('english', title || ' ' ||body));

4. 收拾数据
drop schema tsearch；