VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

pg_zhtrgm插件

功能描述

pg_zhtrgm插件的主要作用是做相似度匹配。该插件提供了多种用于字母数字文本相似度匹配的函数和操作符,可以简化全文索引的使用方式。

该扩展不仅支持对英文字符的全文检索,还允许对中文字符进行全文检索。

注意事项

  • 创建此扩展要求用户在当前数据库中具有create权限。

  • 若安装Vastbase时采取非实例化安装,使用initdb工具初始化实例时需注意:initdb指定的 -E 和--locale参数对应的编码规则必须一致,否则将无法使用pg_zhtrgm插件。

  • 不同版本的pg_zhtrgm插件支持的功能范围不同,支持使用如下语句查看当前插件的版本:

    select * from pg_available_extensions WHERE name like 'pg_zhtrgm';
    

    同时,pg_zhtrgm插件的版本与当前使用的Vastbase数据库的补丁版本号有关,当数据库版本为Vastbase G100 V2.2 Build 10时:

    • 若补丁版本号≤Patch No.6,则插件版本为1.0。

    • 若从低版本使用VPatch工具升级至Patch No.7,插件版本仍为1.0。

    • 若使用数据库安装包全量安装Vastbase G100 V2.2 Build 10 Patch No.7,插件版本为1.1。

    • 若补丁版本号≥Patch No.8,则插件版本为1.4。

支持范围

表1 pg_zhtrgm支持的函数

函数 返回类型 描述 插件支持版本
similarity(text,text) real 返回一个数字指示两个参数有多相似。该结果的范围是0(指示两个字符串完全不相似)到1(指示两个字符串完全一样)。 1.0及以上
show_trgm(text) text 返回一个给定字符串中所有的trigram组成的一个数组(实际上除了调试很少有用)。 1.0及以上
show_limit() real 返回%操作符使用的当前相似性阈值。 1.0及以上
set_limit(real) real 设置%操作符使用的当前相似性阈值。 1.0及以上
show_trgm3(text) text[] 返回三元匹配的trigram数组。 1.1及以上
similarity3(text,text) real 返回三元匹配的similarity相似度。 1.1及以上
show_limit(integer) real integer参数指定的是匹配方式,支持2和3。
如果是2等价于show_limit(),如果是3则返回%%操作符使用的当前词相似度阈值。
1.4及以上
set_limit(real,integer) real integer参数指定的是匹配方式,支持 2和3。
如果是2等价于set_limit(real),如果是3设置%%操作符使用的当前词相似度阈值,取值范围[0,1]。
1.4及以上
word_similarity(text,text) real 返回二元匹配的word_similarity相似度。 1.4及以上
word_similarity3(text,text) real 返回三元匹配的word_similarity相似度。 1.4及以上
show_word_limit() real 返回<%和%>操作符使用的当前词相似度阈值。 1.4及以上
set_word_limit(real) real 设置<%和%>操作符使用的当前词相似度阈值,取值范围[0,1]。 1.4及以上
show_word_limit(integer) real integer参数指定的是匹配方式,支持2和3。
如果是2等价于show_word_limit(),如果是3则返回<%%和%%>操作符使用的当前词相似度阈值。
1.4及以上
set_word_limit(real,integer) real imteger参数指定的是匹配方式,支持 2和3。
如果是2等价于set_word_limit(real),如果是3设置<%% 和 %%>操作符使用的当前词相似度阀值,取值范围[0,1]。
1.4及以上
strict_word_similarity(text,text) real 返回二元匹配的strict_word_similarity相似度。 1.4及以上
strict_word_similarity3(text,text) real 返回三元匹配的strict_word_similarity相似度。 1.4及以上
show_strict_word_limit() real 返回<<%和%>>操作符使用的当前词相似度阈值。 1.4及以上
set_strict_word_limit(real) real 设置<<%和%>>操作符使用的当前词相似度阈值,取值范围[0,1]。 1.4及以上
show_strict_word_limit(integer) real integer参数指定的是匹配方式,支持 2和3。
如果是2等价于show_strict_word_limit();如果是3,则返回<<%%和%%>>操作符使用的当前词相似度阈值。
1.4及以上
set_strict_word_limit(real,integer) real integer参数指定的是匹配方式,支持2和3。
如果是2等价于set_strict_word limit(real);如果是3设置<<%%和%%>>操作符使用的当前词相似度阈值,取值范围[0,1]。
1.4及以上

pg_zhtrgm模块提供了Gist和Gin索引操作符类,此类全文索引仅支持模糊查询,不支持等值匹配。对应的操作符如表2所示:

表2 pg_zhtrgm支持的操作符

操作符 返回类型 描述 插件支持版本
text <-> text real 返回参数之间的“距离”,即1-similarity()的值。 1.0及以上
text %% text boolean 如果参数的similarity3计算超过show_limit()阈值,则返回true。 1.1及以上
如果参数的similarity3计算超过show_limit(3)阈值,则返回true。 1.4及以上
text <<->> text boolean 返回参数之间的距离,即1-similarity3()的值。 1.1及以上
text <% text boolean 如果第一个参数中的bigram集合与第二个参数中有序 bigram集合的一个连续部分之间的相似度超过show_word_limit()阈值,则返回true。 1.4及以上
text %> text boolean <%的交换子。 1.4及以上
text <%% text boolean <%对应的三元匹配操作。如果第一个参数中的trigram集合与第二个参数中有序trigram集合的一个连续部分之间的相似度超过show_word_limit(3)阈值,则返回true。 1.4及以上
text %%> text boolean <%%的交换子。 1.4及以上
text <<% text boolean 如果第二个参数中包含序bigram集合的一个连续部分匹配词边界,并且其与第一个参数的bigram集合的相似度超过show_strict_word_limit()阈值,则返回 true。 1.4及以上
text %>> text boolean <<%的交换子。 1.4及以上
text <<%% text boolean <<%对应的三元匹配操作。如果第二个参数包含有序 bigram集合的一个连续部分匹配词边界,并且其与第一个参数的bigram集合的相似度超过show_strict_word_limit(3)阈值,则返回true。 1.4及以上
text %%>> text boolean <<%%的交换子。 1.4及以上
text <<-> text real 返回参数之间的距离,即1-word_similarity()的值。 1.4及以上
text <->> text real <<->的交换子。 1.4及以上
text <<<->> text real 返回参数之间的“距离”,即1-word_similarity3()的值。 1.4及以上
text <<->>> text real <<<->>的交换子。 1.4及以上
text <<<-> text real 返回参数之间的距离,即1-strict_word_similarity()的值。 1.4及以上
text <->>> text real <<<->的交换子。 1.4及以上
text <<<<->> text real 返回参数之间的距离,即1-strict_word_similarity3()的值 1.4及以上
text <<->>>> text real <<<<->>的交换子。 1.4及以上

pg_zhtrgm提供了gist和gin索引操作符类,该操作符类支持在文本上创建一个索引,使用匹配操作提升检索速度或性能。

表3 索引支持

索引 描述 插件支持版本
gist_zhtrgm_ops gist索引操作符。
支持%、<->、like、ilike操作符。
1.0及以上
支持~、~*、%>、<->>、%>>、<->>>操作符。 1.4及以上
gin_zhtrgm_ops gin索引操作符。
支持like、ilike、%操作符。
1.0及以上
支持~、~*、%>、%>>操作符。 1.4及以上
gist_zhtrgm3_ops 三元匹配的gist索引操作符。
支持like、ilike、%%、<<->>操作符。
1.1及以上
支持~、~*、%%>、<<->>>、%%>>、<<->>>>操作符。 1.4及以上
gin_zhtrgm3_ops 三元匹配的gin索引操作符。
支持like、ilike、%%操作符。
1.1及以上
支持~、~*、%%>、%%>>操作符。 1.4及以上

示例

1、创建插件。

create extension pg_zhtrgm;

2、创建测试表。

create table t_full_text(id int, info text);

3、插入如下中文数据:

insert into t_full_text values (1, ' Vastbase是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),Vastbase支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外,因为许可证的灵活,任何人都可以以任何目的免费使用、修改和分发PostgreSQL。');

4、在此表上创建以pg_zhgrtm提供的gin_zhtrgm_ops、gist_zhtrgm_ops操作符类所支持的gin索引或gist索引,该索引创建不再需要借助to_tsvector函数,同样地,使用该索引进行查询时也不再需要借助to_tsquery函数。

create index idx_full_text1 on t_full_text using gin(info gin_zhtrgm_ops);
create index idx_full_text2 on t_full_text using gist(info gist_zhtrgm_ops);

5、可以进行单字、词语、条件、短句的全文搜索:

  • 使用单字搜索。

    select * from t_full_text where info like '%函%';
    

    结果显示如下:

    id |                                                                                                                                                                                
            info                                                                                                                                                                                          
    ----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------
    1 |  Vastbase是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),Vastbase支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多
    版本并发控制等。同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外,因为许可证的灵活,任何人都可以以任何目的免费使用、
    改和分发PostgreSQL。
    (1 row)
    
  • 使用词语搜索。

    select * from t_full_text where info like '%视图%';
    

    结果显示如下:

    id |                                                                                                                                                                                  
            info                                                                                                                                                                                            
    ----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------
    1 |  Vastbase是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),Vastbase支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多
    版本并发控制等。同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外,因为许可证的灵活,任何人都可以以任何目的免费使用、
    改和分发PostgreSQL。
    (1 row)
    
  • not like条件搜索。

    select * from t_full_text where info like '%查询%' and info not like '%外键%';
    

    结果显示如下:

    id | info 
    ----+------
    (0 rows)
    
  • 使用语句搜索。

    select * from t_full_text where info like '%Vastbase是一种特性非常齐全的自由软件的对象%';
    

    结果显示如下:

    id |                                                                                                                                                                                  
            info                                                                                                                                                                                          
    ----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------
    1 |  Vastbase是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),Vastbase支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多
    版本并发控制等。同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外,因为许可证的灵活,任何人都可以以任何目的免费使用、
    改和分发PostgreSQL。
    (1 row)