postgres 模糊匹配
1:前缀模糊
create index index_table_field on table (left(address,3) varchar_pattern_ops);
select * from table where address ~ '^abc';
` 附链接:https://www.coder.work/article/2609033
2:后缀索引
craete index index_table_field on table using btree (reverse(address)));
select * from table where address ~ 'abc$';
3:中缀索引
1°:声明pg_trgm create extension pg_trgm;
2°:create index index-table_field on table using gin(address gin_trgm_ops);
3°:select * from table where address ~ '?=abc';
4°:中文模糊查询需要转码:
##建立转换函数并声明为immutable类型
create or replace function textsend_i (text) returns bytea as $$ select textsend($1); $$ language sql strict immutable;
##创建索引create
index
idx_name_1
on
jd_daojia_product_1225 using gin(text(textsend_i(address)) gin_trgm_ops);
参考博文:
https://www.cnblogs.com/guoxueyuan/p/8625458.html
https://blog.csdn.net/u014539401/article/details/72794503
说明:后续会对pg_trgm进行详解