如何在PostgreSQL中检测词表关键词与商标表词汇的匹配？

表结构说明 #

你给出的建表语句存在一处小错误（insert字段名写错），修正后的完整表结构及测试数据如下：

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"
);

需求实现方案 #

针对检测words.keyword短语中是否包含trademarks.trademark中完整单词的需求，提供两种可行方案：

方案一：正则匹配（适用于小数据量场景） #

利用PostgreSQL的正则表达式匹配，通过\m和\M锚定单词的开头和结尾，确保匹配完整单词而非子串：

SELECT w.id, w.keyword, t.trademark
FROM words w
JOIN trademarks t ON w.keyword ~* ('\m' || t.trademark || '\M');

• ~*表示不区分大小写的正则匹配，若需要区分大小写，替换为~即可。
• 该方法无需额外创建索引，适合商标数量较少的场景。

方案二：全文搜索（适用于大数据量场景） #

当商标数量达到数千级时，正则匹配的性能会下降，此时推荐使用PostgreSQL的全文搜索功能，并通过索引优化查询速度：

1. 先给words表的keyword字段创建全文索引：

CREATE INDEX idx_words_keyword_tsv ON words USING gin(to_tsvector('english', keyword));

2. 执行匹配查询：

SELECT w.id, w.keyword, t.trademark
FROM words w
JOIN trademarks t ON to_tsvector('english', w.keyword) @@ to_tsquery('english', t.trademark);

• 全文搜索会自动处理单词边界，且索引能大幅提升查询效率，适合大规模数据场景。
• 若需要支持多语言，可将'english'替换为对应语言的全文配置。

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