学习目标

学习openGauss全文检索

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

准备工作

su - omm
gsql -r

课程作业

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

tsvector @@ tsquery:
select 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery as result;
tsquery @@ tsvector:
select 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector as result;

2. 创建表且至少有两个字段的类型为 text类型，在创建索引前进行全文检索

创建表，并进行插入数据：

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');

创建好表后，进行全文检索：

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

3. 创建GIN索引

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

4. 清理数据

drop schema tsearch cascade;

课程总结

Day20：学习了openGauss中全文检索的方法，能够通过tsvector等操作高效地进行信息检索
Day20 打卡 get√