PostgreSQL 模糊查询

在MySQL中,只要字段对应建立B-Tree索引,那么前缀LIKE就可以走索引。不过在PostgreSQL中的设置就稍微复杂一点。

#建表和建索引
create table test(id int, name varchar(32));
create index idx on test(name);

#对应如下详细SQL语句
CREATE TABLE "public"."test" (
"id" int4,
"name" varchar(32) COLLATE "default"
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."test" OWNER TO "postgres";
CREATE INDEX "idx" ON "public"."test" USING btree ("name" "pg_catalog"."text_ops");

注意,字段“name”的字符检验是“default”,表示继承自数据库的设置。创建的索引类型是btree(默认),未指定字符校验集,类型模式是text_ops(默认)。这个情况下(数据库的校验集不是“C”),直接like ‘xxx%’是无法走索引的。

如果需要走索引,有两种方式:
1 建立索引时,明确指定校验集为“C”,查询时也指定校验集为“C”(如果数据校验集是“C”,不需要指定)

#建索引时指定
create index idx on test(name collate "C"); 

#查询时指定
like 'xxx%' collate "C"

这个索引的搜索是二进制搜索,效率高效。

2 建立索引时,明确指定字段类型对应模式,比如varchar对应varchar_pattern_ops,text对应text_pattern_ops,其它类推

create index idx on test(name varchar_pattern_ops);

使用类型对应的 pattern ops 时,索引搜索不仅支持 LIKE 的写法,还支持规则表达式的写法(name ~ ‘^abcd’)。使用pattern ops将使用字符查询而非binary查询的搜索方式。

一般,第二种方式比较常用(因为默认字符校验一般都不会是“C”), 基于btree的后缀模糊搜索(建索引时调用reverse()函数),跟上面的讨论一致。前后模糊搜索,无法应用到btree索引。

在PostgreSQL中,内置了一般数据库都没有的pg_trgm插件,还内置了表达式索引和GIN索引的功能。PostgreSQL 支持使用 pg_trgm 索引来加速同时包含前模糊和后模糊的查询。为使索引过滤有较好的效果,前模糊查询至少需输入 1 个字符,后模糊查询至少需输入 2 个字符。若要高效支持多字节字符(如中文),数据库的 lc_ctype 不能为 “C”,只有 TOKEN 分割正确才能有较好的效果。

#创建gin索引
create index idx_test on test using gin (c1 gin_trgm_ops);

#查询
select * from test where c1 like 'x%';
select * from test where c1 like '%xx';

注意:对于后默认查询(2个字符以上),并不需要像btree索引那样另外调用翻转函数建立索引。

对于前后模糊搜索,需要至少3个字符:

select * from test where c1 like '%xxx%'