如何在PostgreSQL存储过程中进行字符串模糊匹配_使用LIKE或正规表达式-SQL-PHP中文网

postgresql存储过程中like需注意大小写、null和索引失效：用ilike+参数化防注入并支持索引；null匹配返回null，须显式处理；全模糊查询应改用pg_trgm或全文检索。

如何在postgresql存储过程中进行字符串模糊匹配_使用like或正规表达式

LIKE在存储过程中怎么写才不踩坑

直接在 CREATE OR REPLACE FUNCTION 或 DO 块里用 LIKE 没问题，但要注意三件事：大小写、NULL 和索引失效。PostgreSQL 默认区分大小写，'abc' LIKE 'ABC%' 是 false；name LIKE '%x%' 在 WHERE 里写没问题，但放进函数体后如果参数是动态拼接的（比如 sql := 'SELECT * FROM t WHERE col LIKE ''' || keyword || ''';'），容易被 SQL 注入，也破坏执行计划缓存。

推荐做法是用参数化方式 + ILIKE 统一处理大小写：

用 $1 占位符传参，避免字符串拼接
匹配前缀时优先用 ILIKE 'pattern%'，能走 B-tree 索引
对可能为 NULL 的字段，显式加 AND col IS NOT NULL，否则 col LIKE '%x%' 对 NULL 行静默跳过
要匹配字面量 % 或 _，必须用 ESCAPE，例如 col LIKE '100\%' ESCAPE '\'

正则表达式（~ 和 ~*）在函数里为什么变慢了

在存储过程里用 ~ 或 ~* 不会报错，但性能会断崖式下跌——尤其是数据量超过 10 万行后。原因很实在：~ 解析器比 LIKE 复杂得多，每次调用都要编译正则模式，且普通 B-tree 索引完全无效。即使你建了 GIN 索引（如 CREATE INDEX idx ON t USING GIN (col gin_trgm_ops)），对中文或宽字符支持也不稳定，实测扫描开销常翻 5–10 倍。

除非真需要以下能力，否则别在函数里默认选正则：

需要锚点控制，比如只匹配开头 ^abc 或结尾 xyz$
要写字符类 [a-z0-9]、量词 a{2,4} 或分组 (jpg|png)
做格式校验（邮箱、手机号前缀、身份证号结构）

注意：~* 是大小写不敏感版本，但和 ILIKE 不同，它不走 trigram 索引加速，纯靠 CPU 匹配。

存储过程里模糊匹配要不要建索引

要看你怎么用。如果函数里固定写 WHERE name LIKE 'John%'，那给 name 加个普通 B-tree 索引就行；但如果函数接收参数并拼成 WHERE name LIKE '%' || $1 || '%'，B-tree 索引就完全失效，只能全表扫。

这时有两个务实选择：

加 pg_trgm 扩展 + GIN 索引：运行 CREATE EXTENSION IF NOT EXISTS pg_trgm;，再建 CREATE INDEX CONCURRENTLY idx_name_trgm ON users USING GIN (name gin_trgm_ops);。它能让 LIKE '%x%' 和 ~ 'x' 都提速，但对 ASCII 字符效果明显，中文需确保 lc_collate 不是 C
避免在函数里做全模糊：把 %keyword% 类逻辑提到应用层预处理，或改用全文检索 to_tsvector + @@

别忘了：GIN 索引写入开销比 B-tree 高，高频更新的表要权衡。

NULL 和空字符串在模糊匹配中的静默行为

这是最常被忽略的点：NULL LIKE '%x%'、NULL ~ 'x'、'' LIKE '%x%' 全部返回 NULL（即 SQL 的“未知”），不是 FALSE。所以在存储过程的 WHERE 条件里，如果字段可能为空，WHERE col LIKE '%x%' 会自动过滤掉所有 NULL 和空串行——你查不到它们，但也不会报错或警告。

安全写法是显式声明意图：