PostgreSQL：如何检测词组中是否包含完整商标词？

我在PostgreSQL中创建了以下表用于存储词汇：

CREATE TABLE IF NOT EXISTS words
(
    id bigint NOT NULL DEFAULT nextval('processed_words_id_seq'::regclass),
    keyword character varying(300) COLLATE pg_catalog."default",
);

insert into words (keyword)
VALUES ('while swam is interesting');

CREATE TABLE IF NOT EXISTS trademarks
(
   id bigint NOT NULL DEFAULT nextval('trademarks_id_seq'::regclass),
   trademark character varying(300) COLLATE pg_catalog."default",
);

trademarks表存储数千个已注册的商标名称，需要检测words表keyword字段的词组中是否包含trademarks表中的完整商标词（而非部分词）。例如：当words.keyword为"while swam is interesting"，且trademarks.trademark存在"swam"时，应检测到匹配。

我尝试了以下SQL语句，但结果不符合预期，只能匹配词的部分内容而非完整词：

select 
    w.id, w.keyword, t.trademark 
from
    words w
inner join 
    trademarks t 
on
  t.trademark ilike  '%'||w.keyword||'%' 
where 
   w.keyword = 'all';

请问该如何修正这段SQL？

你的SQL存在两个核心问题：

1. 关联条件逻辑颠倒：应该是检测w.keyword是否包含t.trademark，而非反过来；
2. 未处理完整词匹配：直接用%通配符会匹配包含目标字符串的任意子串，无法保证是完整单词。

针对这两个问题，提供两种可行的修正方案：

方案一：使用正则表达式匹配完整单词 #

利用PostgreSQL的正则表达式功能，通过\y标记单词边界（匹配单词的开头或结尾），确保只匹配完整的商标词：

select 
    w.id, w.keyword, t.trademark 
from
    words w
inner join 
    trademarks t 
on
    w.keyword ~* ('\y' || regexp_replace(t.trademark, '([\^\$\.\*\+\?\(\)\[\]\{\}\|\\])', '\\\1', 'g') || '\y')
-- 这里的regexp_replace是为了转义商标中可能含有的正则特殊字符，避免匹配出错
where 
    w.keyword = 'while swam is interesting'; -- 替换为你需要查询的keyword值

说明： #

• ~*表示不区分大小写的正则匹配；
• \y是PostgreSQL中的单词边界锚点，确保匹配的是完整单词；
• regexp_replace用于转义商标中的正则特殊字符（如.、*等），防止这些字符干扰正则匹配逻辑。

方案二：使用全文搜索（更高效） #

如果trademarks表数据量较大（数千条），全文搜索的性能会优于正则表达式。可以通过将keyword转换为tsvector，将商标转换为tsquery来实现精准的完整词匹配：

-- 先创建索引优化性能（可选但推荐）
CREATE INDEX idx_words_tsvector ON words USING gin(to_tsvector('english', keyword));
CREATE INDEX idx_trademarks_trademark ON trademarks(trademark);

-- 查询语句
select 
    w.id, w.keyword, t.trademark 
from
    words w
inner join 
    trademarks t 
on
    to_tsvector('english', w.keyword) @@ plainto_tsquery('english', t.trademark)
where 
    w.keyword = 'while swam is interesting';

说明： #

• to_tsvector将文本转换为可搜索的词向量；
• plainto_tsquery将商标转换为全文查询语句，默认会按完整单词匹配；
• 创建GIN索引可以大幅提升大表下的查询效率。

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