PostgreSQL如何筛选主表不含另一子表名称字段值的记录

方案1：常规关联写法（小数据量适用） #

直接用NOT EXISTS搭配不区分大小写匹配运算符ILIKE实现逻辑，代码如下：

SELECT m.data
FROM main m
WHERE NOT EXISTS (
    SELECT 1
    FROM secondary s
    WHERE m.data ILIKE '%' || s.name || '%'
);

注意：该写法未做优化的情况下，1000万行main表关联4万行secondary表性能极差，不推荐生产环境直接使用。

方案2：正则数组匹配（性能更优，无需额外扩展） #

将secondary表所有name拼成正则数组，仅需扫描两张表各一次即可完成匹配，性能远高于关联写法：

SELECT m.data
FROM main m
WHERE NOT m.data ~* ANY(
    SELECT array_agg(regexp_escape(s.name))
    FROM secondary s
);

说明：

• ~* 是PostgreSQL内置的不区分大小写正则匹配运算符
• regexp_escape 用于转义name中可能存在的正则特殊字符（如.、*、+等），避免匹配逻辑异常
• ANY 匹配数组中任意一个规则即返回真，加NOT即筛选出不匹配所有规则的记录

方案3：pg_trgm 索引优化（超高查询性能，适合高频查询场景） #

如果需要反复执行该查询，可通过pg_trgm扩展创建 trigram 索引进一步优化匹配速度：

1. 开启扩展（需要superuser权限）：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 给main表的data列创建GIN类型的trigram索引：

CREATE INDEX idx_main_data_trgm ON main USING GIN (data gin_trgm_ops);

3. 直接执行方案1的NOT EXISTS查询即可自动走索引，查询速度可提升数倍到数十倍。

内容的提问来源于stack exchange，提问作者porgrammer3124