作业内容

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

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

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

CREATE TABLE test(id int, body text, title text);
INSERT INTO test VALUES(1, 'China, officially the People''s Republic of China(PRC), located in Asia, is the world''s most populous state.', 'China');
INSERT INTO test VALUES(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America');
SELECT title FROM test WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('china & asia');

• 创建GIN索引

CREATE INDEX idx ON test USING gin(to_tsvector('english', body));

• 清理数据

drop table test;

学习体会

今天学习了openGauss全文检索，openGauss提供了两种数据类型用于支持全文检索。tsvector类型表示为文本搜索优化的文件格式，tsquery类型表示文本查询，to_tsquery规范化转为tsquery类型，大文本可以建立gin索引来提高查询速度。