PostgreSQL 11多关键词全匹配高级搜索的查询方案求助

核心实现 SQL #

针对你的需求，这里提供直接可用的查询语句，包含搜索文本处理、原文本匹配逻辑：

WITH search_terms AS (
    -- 处理搜索文本，生成无空元素的关键词数组
    SELECT array_remove(
        string_to_array(regexp_replace('alive dumas ! franshesco', '[^\w]+', ' ', 'g'), ' '),
        ''
    ) AS terms
),
normalized_original AS (
    -- 原文本统一转小写，消除大小写匹配差异
    SELECT lower('dumas, franshesco robert Ferrer Lombardy alive') AS original_str
)
SELECT 
    CASE 
        WHEN bool_and(original_str LIKE '%' || term || '%') THEN 'ok'
        ELSE 'not ok'
    END AS match_result
FROM search_terms, unnest(terms) AS term, normalized_original;

逻辑拆解 #

1. 搜索文本处理：
   • 用regexp_replace清除!,.?等非单词字符，替换为空格
   • string_to_array拆分字符串为数组，再通过array_remove过滤拆分后产生的空元素（比如搜索文本首尾空格导致的空值）
2. 原文本归一化：
   • 转成小写，避免大小写差异导致的匹配失败（比如原文本的Ferrer和搜索词的ferrer能正常匹配）
3. 全关键词验证：
   • unnest把关键词数组展开为单行数据
   • bool_and聚合函数检查所有关键词是否都满足子串匹配条件，全部满足则返回ok，否则返回not ok

扩展场景适配 #

如果原文本存储在数据表中（比如表docs的content字段），可以调整为批量验证逻辑：

WITH search_terms AS (
    SELECT array_remove(
        string_to_array(regexp_replace('dumas franshesco , robert', '[^\w]+', ' ', 'g'), ' '),
        ''
    ) AS terms
)
SELECT 
    id,
    CASE 
        WHEN bool_and(lower(content) LIKE '%' || term || '%') THEN 'ok'
        ELSE 'not ok'
    END AS match_result
FROM docs, search_terms, unnest(terms) AS term
GROUP BY id;

进阶优化：完整单词匹配 #

如果需要确保匹配完整单词（避免原文本dumas被搜索词dum误匹配），可以把LIKE替换为正则单词边界匹配：

-- 将匹配条件替换为正则单词边界
WHEN bool_and(original_str ~* ('\m' || term || '\M')) THEN 'ok'

其中\m表示单词开头，\M表示单词结尾，~*代表不区分大小写的正则匹配。

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