PostgreSql 模糊查询

PostgreSql模糊查询sql语句

PostgreSQL中用于模糊查询的操作符有六大类

% 表示任意个字符；_ 表示单个任意字符；escape 用来转义
左模糊查询效率低下，一般不推荐在应用中去使用

like、not like
模糊匹配字符串时区分大小写

示例：
select * from table where name like '张/_小%' escape '/';
这里表明 / 作为转义符，所以就可以在模糊查询中将通配符作为普通字符来搜索

ilike、not ilike
模糊匹配字符串时不区分大小写，i是ignore的意思

~、!~ 
匹配正则表达式，且区分大小写
不匹配正则表达式，且区分大小写

示例：
通过这个操作符来实现like和ilike一样的效果
1.匹配以“张”开头的字符串
select * from table where name ~ '^张';
2.匹配以“小”结尾的字符串
select * from table where name ~ '小$';

其实这里的^和$就是正则表达式里的用法。

~*、!~*
匹配正则表达式，且不区分大小写
不匹配正则表达式，且不区分大小写

~~、!~~
等效于like、not like

~~*、!~~*
等效于ilike、not ilike

使用三元索引提升模糊查询速度

1. 新建测试表

CREATE TABLE material_video
(
	ID INT,
	TextData1 TEXT,
	TextData2 TEXT,
	TextData3 TEXT
);

2. 为TextData1字段添加三元模式索引

CREATE INDEX trgm_idx_material_video ON material_video USING GIST (TextData1 gist_trgm_ops);

如报索引方法不存在，则需要安装 postgresql-contrib 包

3. 安装 postgresql-contrib 包

查看 yum 源是否存在 postgresql-contrib.x86_64
yum search contrib

安装当前pgsql对应的版本扩展包，否则报如下错误
错误:  无法打开扩展控制文件 "/usr/pgsql-13/share/extension/pg_trgm.control": 没有那个文件或目录

select version();  // PostgreSQL 13.5 on x86_64-pc-linux 数据库版本为13.5

安装版本为 13 的 contrib 包
yum -y install postgresql13-contrib.x86_64

4. 查看库中现在已安装的扩展
   进入psql命令行，进入指定数据库

su - postgres
psql
\c ziwei   // ziwei为库名

查看扩展两种方式
命令行 ：\dT  
SQL：select * from pg_extension

此时库中未添加扩展
5. 加载扩展

CREATE EXTENSION pg_trgm;

gtrgm 为已加载的扩展

6. 再次对 TextData1 字段添加索引

CREATE INDEX trgm_idx_material_video ON material_video USING GIST (TextData1 gist_trgm_ops);

7. 测试验证

添加测试数据
INSERT INTO material_video VALUES
(1,'Anvesh Patel','会报路径找不到','中国人长的帅')
,(2,'Database Research & Development','我也不知道啊','谁长的帅')
,(3,'PostgreSQL is better than SQL Server','ni 是猪吗','666冲冲弄')
,(4,'PostgreSQL is supported powerful BRIN Indexes','sdfsf双方都水电费','sfsdfsdfsdfsdfsdfsdfsdf');

查询测试
SELECT * FROM material_video WHERE TextData1 ~ 'me';

8. 中文查询问题
   中文无法匹配，如果是中文需要先转为bytea类型
   参考 Postgres bytea类型 转换及查看方法 bytea_output = ‘escape’

三元索引生成结果分析

执行下面语句可以查看三元索引生成结果

select show_trgm('supported') as notes; 
{"  s"," su","ed ",ort,por,ppo,rte,sup,ted,upp}

select show_trgm('中国') as notes; 
{0xca1dc3,0x1b0419,0x780fe8}

执行下面语句检查相似性

SELECT SIMILARITY('中国人','国') AS SimilarPercentage;
SELECT SIMILARITY('i am china','in') AS SimilarPercentage;