openGauss每日一练 第20天 全文检索

学习目标

学习openGauss全文检索

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

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

课程学习

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

课后作业

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

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;

-- 分词器
\dF
show default_text_search_config;

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

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

3.创建GIN索引

-- 指定english配置来解析和规范化字符串
CREATE INDEX idx_pgweb_body1 ON tsearch.pgweb USING gin(to_tsvector('english', body));

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

\d+ tsearch.pgweb

4.清理数据

drop schema tsearch cascade;

作业执行结果

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery AS RESULT;
 result 
--------
 t
(1 row)

omm=# SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector AS RESULT;
 result 
--------
 f
(1 row)

omm=# -- 分词器
omm=# 
omm=# \dF
               List of text search configurations
   Schema   |    Name    |              Description              
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | ngram      | ngram configuration
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | pound      | pound configuration
 pg_catalog | zhparser   | zhparser configuration
(19 rows)


omm=# show default_text_search_config;
 default_text_search_config 
----------------------------
 pg_catalog.english
(1 row)

omm=# CREATE SCHEMA tsearch;
CREATE SCHEMA
omm=# CREATE TABLE tsearch.pgweb(id int, body text, title text, last_mod_date date);
CREATE TABLE
omm=# 
omm=# 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 0 1
omm=# 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 0 1
omm=# 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');
INSERT 0 1
omm=# 
omm=# -- 全文检索
omm=# SELECT id, body, title FROM tsearch.pgweb WHERE to_tsvector(body) @@ to_tsquery('China');
 id |                                                    body                                    
                 | title 
----+--------------------------------------------------------------------------------------------
-----------------+-------
 populous state. | China
(1 row)

  1 | China, officially the People's Republic of China(PRC), located in Asia, is the world's most
omm=# 
omm=# SELECT title FROM tsearch.pgweb WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('china & asia');
 title 
-------
 China
(1 row)

omm=# 
omm=# omm=# -- 指定english配置来解析和规范化字符串
CREATE INDEX idx_pgweb_body1 ON tsearch.pgweb USING gin(to_tsvector('english', body));
CREATE INDEX
omm=# 
omm=# CREATE INDEX idx_pgweb_body2 ON tsearch.pgweb USING gin(to_tsvector('english', title || ' ' || body));
CREATE INDEX
omm=# 
\d+ tsearch.pgweb
omm=#                             Table "tsearch.pgweb"
    Column     |  Type   | Modifiers | Storage  | Stats target | Description 
---------------+---------+-----------+----------+--------------+-------------
 id            | integer |           | plain    |              | 
 body          | text    |           | extended |              | 
 title         | text    |           | extended |              | omm=# 
 last_mod_date | date    |           | plain    |              | 
Indexes:
    "idx_pgweb_body1" gin (to_tsvector('english'::regconfig, body)) TABLESPACE pg_default
    "idx_pgweb_body2" gin (to_tsvector('english'::regconfig, (title || ' '::text) || body)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# omm=# 
omm=# drop schema tsearch cascade;
NOTICE:  drop cascades to table tsearch.pgweb
DROP SCHEMA