基于Mybatis实现PG文本搜索的最简易方法

这可能是实现PG文本搜索的最简单的方法。

代码只需要实现搜索SQL的编写执行，CUD操作都通过索引自动完成。

示例的简单背景介绍

在数据库表sparring_workflow中，需要在workflow_name和workflow_desc中实现文本搜索。（前端可以在name和desc中搜索到用户需要的信息）

0. DBA操作

下面的操作需要超级用户权限才能执行。

 CREATE EXTENSION zhparser;

1. 服务的DB流水线

创建中文的文本搜索配置。

创建倒排索引GIN。

注意：实际搜索的SQL中，需要和GIN内部的写法一致时，才能使用倒排索引。

这个索引的意思是，workflow_name和workflow_desc都采用前面的zh文本搜索配置来生成vector，workflow_name的权重为A，workflow_desc的权重为C

CREATE TEXT SEARCH CONFIGURATION zh (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION zh alter MAPPING FOR r,n,v,a,i,e,l WITH simple;

CREATE INDEX workflow_vector_dynamic_idx ON sparring_workflow USING GIN ((setweight(to_tsvector('zh', workflow_name), 'A') ||  setweight(to_tsvector('zh', workflow_desc), 'C')));

2. 基于Mybatis的代码实现

@Mapper
public interface SparringWorkflowVectorCustomizedMapper {
    @SelectProvider(value = WorkflowVectorSqlBuilder.class, method = "searchWorkflowDynamicVector")
    List<SparringWorkflowEntity> searchWorkflowDynamic(String query, String workflowType);
}

核心的搜索SQL

下面是核心的搜索SQL语法。

public class WorkflowVectorSqlBuilder {
/*
websearch_to_tsquery是web搜索的场景，可以支持 or - ""的常见搜索语法格式

@@和ES的MATCH含义一样，代表怎样的query去哪里的vector中去搜索

排序采用cover density ranking,在输入只有1-3个词的时候，效果比TF-IDF好
ts_rank_cd的cd就是cover density，包含的输入词越多，得分越高
参考论文：Relevance ranking for one to three term queries
rank函数的第三个参数代表考虑文档长度的方式，设置为1，相当于TF-IDF的公式
 */
    final static String WEIGHT_VECTOR = "(setweight(to_tsvector('zh', workflow_name), 'A') ||  setweight(to_tsvector('zh', workflow_desc), 'C'))";

    public static String searchWorkflowVectorSimple(final String query, final String workflowType){
        return new SQL(){{
            SELECT(" * ");
            FROM(String.format("sparring_workflow, websearch_to_tsquery('zh', #{query}) search, ts_rank(%s, search, 1) rank", WEIGHT_VECTOR));
            WHERE(WEIGHT_VECTOR + " @@ search");
            WHERE("delete_flag = 0");
            if(!Strings.isBlank(workflowType)){
                WHERE("workflow_type = #{workflowType}");
            }
            ORDER_BY("rank DESC");
        }}.toString();
    }

}

搜索分页

采用Mybatis的PageHelper插件即可