Postgres中如何将字符串与其他表列匹配并提取匹配词与结构？

嘿，我来帮你解决这个在PostgreSQL里提取匹配词和结构的需求！先跟你捋清楚咱们的核心表结构，方便后续理解：

• Table I（Strings表）：存输入字符串的主表，有string（待匹配的输入串）、match_words（用来存匹配到的词）、match_structures（用来存匹配到的结构）这三列
• Table II（Words表）：关键词库，只有word列，比如你提到的hello、hi、bird、name
• Table III（Structures表）：结构规则库，只有structure列，比如你说的h...

下面分两种常用场景给你方案，你可以按需选择：

1. 一次性查询出匹配结果 #

如果只是想临时查一下每个输入串对应的匹配内容，不需要更新原表，用这个查询就够了：

SELECT
    s.string,
    -- 把所有匹配到的不重复关键词用逗号拼接起来
    STRING_AGG(DISTINCT w.word, ', ') AS match_words,
    -- 把所有匹配到的不重复结构用逗号拼接起来
    STRING_AGG(DISTINCT st.structure, ', ') AS match_structures
FROM
    "Strings" s
-- 关联Words表，模糊匹配输入串里的关键词（不区分大小写）
LEFT JOIN
    "Words" w ON s.string ILIKE '%' || w.word || '%'
-- 关联Structures表，用正则匹配结构（这里假设结构是正则规则，比如h...表示h开头+3个任意字符）
LEFT JOIN
    "Structures" st ON s.string ~* st.structure
GROUP BY
    s.string;

关键细节说明： #

• 用ILIKE是做不区分大小写的模糊匹配，如果你的需求是严格区分大小写，换成LIKE就行
• STRING_AGG(DISTINCT ..., ', ')：确保同一个词/结构不会重复出现，最后用逗号分隔成友好的字符串格式
• 结构匹配这里用了~*（PostgreSQL的不区分大小写正则匹配），如果你的结构是用SQL标准的LIKE通配符（比如_匹配单个字符、%匹配任意长度），可以把这行改成：

  LEFT JOIN "Structures" st ON s.string ILIKE st.structure
  

  比如如果结构是h%（匹配所有h开头的子串），这样就会生效。

2. 直接更新Table I的匹配列 #

如果要把匹配结果直接写入Table I的match_words和match_structures列，用这个UPDATE语句：

UPDATE "Strings" s
SET
    match_words = COALESCE(
        (SELECT STRING_AGG(DISTINCT w.word, ', ')
         FROM "Words" w
         WHERE s.string ILIKE '%' || w.word || '%'),
        '' -- 如果没匹配到词，设为空字符串而不是NULL
    ),
    match_structures = COALESCE(
        (SELECT STRING_AGG(DISTINCT st.structure, ', ')
         FROM "Structures" st
         WHERE s.string ~* st.structure),
        '' -- 如果没匹配到结构，设为空字符串而不是NULL
    );

优化建议： #

如果你的表数据量比较大，模糊匹配可能会慢，可以试试pg_trgm扩展来加速：

1. 先创建扩展：CREATE EXTENSION IF NOT EXISTS pg_trgm;
2. 给Strings.string、Words.word列加GIN索引：

   CREATE INDEX idx_strings_string_trgm ON "Strings" USING GIN (string gin_trgm_ops);
   CREATE INDEX idx_words_word_trgm ON "Words" USING GIN (word gin_trgm_ops);
   

   这样模糊匹配的速度会提升很多！

举个实际例子，针对你的示例输入串'Hi, my name is dan'：

• 匹配到的关键词是hi和name，所以match_words会是hi, name
• 如果Structures表的结构是h...，用正则匹配的话，会匹配到Hi,这个子串，所以match_structures会是h...

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