本文来介绍下如何使用 Postgres 进行全文检索。

Postgres 本身目前还不支持中文的分词检索，因此我们需要借助第三方插件 Zhparser 来实现我们需要的中文全文检索的功能。

本文中的 Postgres 是 14.4 的版本，进行全文检索的字段是 jsonb 的类型，若您按照本文调试全文检索有问题的话，还请确认 Postgres 的版本是否过低，目前已知的是，在 Postgres 9.0 的版本中是不支持 json/jsonb 类型的全文检索的。

Docker 部署 Postgres

执行以下命令，使用 Docker 部署 Postgres。

➜  ~ WORKDIR=$(pwd)
➜  ~ docker run --name postgres -d -p 5432:5432 -e POSTGRES_PASSWORD=fnuxao2a17ZG -e PGDATA=/var/lib/postgresql/data/pgdata -v ${WORKDIR}/data/postgres:/var/lib/postgresql/data/pgdata postgres

出现以下结果表示部署成功了。

➜  ~ docker ps |egrep "postgres|IMAGE"
CONTAINER ID   IMAGE      COMMAND                  CREATED        STATUS       PORTS                    NAMES
49d9b92fff13   postgres   "docker-entrypoint.s…"   4 months ago   Up 4 hours   0.0.0.0:5432->5432/tcp   postgres

配置安装中文分词插件 Zhparser

进入到我们启动的 Postgres 的容器中。

➜  ~ docker exec -it postgres /bin/sh

配置国内 apt 源。

➜  ~ cp /etc/apt/sources.list /etc/apt/sources.list_bak # 备份原始源
➜  ~ cat > /etc/apt/sources.list <<EOF # 写入国内源
deb http://mirrors.ustc.edu.cn/debian bullseye main
deb http://mirrors.ustc.edu.cn/debian bullseye-updates main
EOF
➜  ~ apt-get update
➜  ~ apt-get install lsb-release wget -y # 安装这两个命令是便于后面安装 postgres 的 apt 源。

配置安装 postgres 依赖所需的源

添加清华源的 postgresql 的 apt 仓库

➜  ~ RELEASE=$(lsb_release -cs)
➜  ~ echo "deb https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ ${RELEASE}"-pgdg main | tee /etc/apt/sources.list.d/pgdg.list

➜  ~ wget --quiet -O - https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ACCC4CF8.asc | sudo apt-key add -

➜  ~ apt update

Zhparser 安装配置

安装依赖命令

➜  ~ apt-get update
➜  ~ apt-get install gcc wget make git bzip2 -y
➜  ~ apt install postgresql-server-dev-14 -y

编译安装 Zhparser

➜  ~ cd /tmp
➜  ~ wget http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2
➜  ~ tar -jxvf scws-1.2.3.tar.bz2 
➜  ~ cd scws-1.2.3
➜  ~ ./configure && make && make install
➜  ~ 
➜  ~ cd ..
➜  ~ git clone https://github.com/amutu/zhparser.git
➜  ~ cd zhparser/
➜  ~ make && make install

接下来进入数据库中，执行以下命令，若没有出现问题，则表示插件安装及配置成功了。

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple; # 修改词性

select ts_token_type('zhparser');
--
"(97,a,""adjective,形容词"")"
"(98,b,""differentiation,区别词"")"
"(99,c,""conjunction,连词"")"
"(100,d,""adverb,副词"")"
"(101,e,""exclamation,感叹词"")"
"(102,f,""position,方位词"")"
"(103,g,""root,词根"")"
"(104,h,""head,前连接成分"")"
"(105,i,""idiom,成语"")"
"(106,j,""abbreviation,简称"")"
"(107,k,""tail,后连接成分"")"
"(108,l,""tmp,习用语"")"
"(109,m,""numeral,数词"")"
"(110,n,""noun,名词"")"
"(111,o,""onomatopoeia,拟声词"")"
"(112,p,""prepositional,介词"")"
"(113,q,""quantity,量词"")"
"(114,r,""pronoun,代词"")"
"(115,s,""space,处所词"")"
"(116,t,""time,时语素"")"
"(117,u,""auxiliary,助词"")"
"(118,v,""verb,动词"")"
"(119,w,""punctuation,标点符号"")"
"(120,x,""unknown,未知词"")"
"(121,y,""modal,语气词"")"
"(122,z,""status,状态词"")"

简单演示一下，在 Postgres 的 console 中，执行如下 sql。

SELECT to_tsvector('chinese','人生得意须尽欢，莫使金樽空对月。天生我材必有用，千金散尽还复来。') as result;

以上 sql 的返回结果是 tsvector 类型的数据，tsvector 是 PostgreSQL 内置的一种字段类型，用来保存的是分词后的结果（文本向量）它是由 词，序列，[权重（可选返回）] 组成。

SELECT to_tsquery('chinese', '兰玉磊是真的帅啊，陌上人如玉，公子世无双。');

简单了解下全文检索会常用到的函数。

• to_tsvector()：分词用，将文本转为向量。 用它可以将字符串转成上边描述的 tsvector，默认不支持中文分词的。
• to_tsquery()：构建搜索的关键字，支持各种符号表示条件。例如 |（或），&（与）等，具体可搜索相关文档。
• setweight()：设置关键词权重，总共四个权重从高到低为 A-B-C-D。
• ts_rank ()：排序用，可以根据 to_tsquery 和 tsvector 的匹配度计算。

模拟百万数据，进行测试

创建演示所需的临时表

CREATE TABLE user_ini(id int4 ,user_id int8,
user_name character varying(64),
create_time timestamp(6) with time zone default clock_timestamp());

创建两个创建随机数据的函数

-- random_range
CREATE OR REPLACE FUNCTION random_range(int4, int4)
RETURNS int4
LANGUAGE SQL
AS $$
    SELECT ($1 + FLOOR(($2 - $1 + 1) * random() ))::int4;
$$;

-- random_text_simple
CREATE OR REPLACE FUNCTION random_text_simple(length int4)
RETURNS text
LANGUAGE PLPGSQL
AS $$
DECLARE
    possible_chars text := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    output text := '';
    i int4;
    pos int4;
BEGIN

    FOR i IN 1..length LOOP
        pos := random_range(1, length(possible_chars));
        output := output || substr(possible_chars, pos, 1);
    END LOOP;

    RETURN output;
END;
$$;

创建 json 数据保存表

CREATE TABLE tbl_user_search_json(id serial, user_info jsonb);

将临时表的数据，转为 json 存入到 json 数据演示的表中。

INSERT INTO tbl_user_search_json(user_info)
 SELECT row_to_json(user_ini) FROM user_ini;

创建全文检索 索引，若是不创建索引的话，数据检索比较慢，百万数据量的话，差不多要 4，5 秒。加上索引的话，仅几十毫秒。

CREATE INDEX idx_gin_search_chinese_json ON tbl_user_search_json USING
gin(to_tsvector('chinese',user_info));

全文检索查询

SELECT * FROM tbl_user_search_json
WHERE to_tsvector('chinese',user_info) @@ to_tsquery('chinese','ABWO95');

https://blog.csdn.net/dl425134845/article/details/122696784

https://www.modb.pro/db/530796