PostgreSQL存储过程中LIKE需注意大小写、NULL和索引失效：用ILIKE+参数化防注入并支持索引；NULL匹配返回NULL，须显式处理；全模糊查询应改用pg_trgm或全文检索。
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 和空串行——你查不到它们，但也不会报错或警告。
安全写法是显式声明意图：
- 想包含 NULL：用
(col LIKE '%x%' OR col IS NULL) - 想排除空字符串：加
AND NULLIF(col, '') IS NOT NULL - 统一转空串为 NULL：在函数入参处用
NULLIF($1, '')
尤其当函数被多个业务调用时，这个隐式过滤很容易导致数据一致性问题，上线前务必用含 NULL 和空值的测试数据跑一遍。
