如何实现PostgreSQL跨多列多关键词部分匹配查询

你使用的是PostgreSQL数据库（从建表语句的serial类型可判断），可以按以下方式实现需求：

实现思路 #

1. 将用户输入的搜索词按空格分割为独立分词，统一转为小写消除大小写影响
2. 每条记录的三个姓名字段也统一转为小写用于匹配
3. 要求所有搜索分词都能匹配到当前记录的任意一个姓名字段，分词顺序不影响匹配结果，多个相同分词需要匹配到不同的字段

具体查询代码 #

固定分词数量写法（简单易读，适合固定搜索词长度场景） #

以搜索joh smi为例：

SELECT * FROM contact
WHERE
  -- 第一个分词joh匹配任意字段
  (lower("firstName") LIKE 'joh%' OR lower("middleName") LIKE 'joh%' OR lower("lastName") LIKE 'joh%')
  AND
  -- 第二个分词smi匹配任意字段
  (lower("firstName") LIKE 'smi%' OR lower("middleName") LIKE 'smi%' OR lower("lastName") LIKE 'smi%');

如果需要任意位置子串匹配而非前缀匹配，把'joh%'改成'%joh%'即可。

通用动态写法（支持任意数量空格分隔的搜索词） #

不需要修改SQL结构，直接替换搜索词即可：

SELECT * FROM contact
WHERE (
  SELECT count(*)
  FROM unnest(string_to_array(lower('替换为你的搜索词'), ' ')) AS search_token
  WHERE search_token <> ''
  AND EXISTS (
    SELECT 1 FROM unnest(ARRAY[lower("firstName"), lower("middleName"), lower("lastName")]) AS name_part
    WHERE name_part LIKE search_token || '%'
  )
) = -- 计算有效分词总数量
array_length(string_to_array(lower('替换为你的搜索词'), ' '), 1) - (string_to_array(lower('替换为你的搜索词'), ' ') @> ARRAY[''])::int;

需求验证 #

1. 搜索joh smi/smi joh
   • 第一条John Mark Smith：John匹配joh，Smith匹配smi，满足所有分词匹配要求，返回
   • 第二条Barbara Alice Johnson：仅Johnson匹配joh，无匹配smi的字段，不返回
   • 第三条John Bob Johson：仅John匹配joh，无匹配smi的字段，不返回
     完全符合要求。
2. 搜索joh joh
   • 第一条John Mark Smith：仅John匹配joh，只有1个匹配字段，不满足2个分词的匹配要求，不返回
   • 第二条Barbara Alice Johnson：仅Johnson匹配joh，只有1个匹配字段，不返回
   • 第三条John Bob Johson：John匹配joh，Johson匹配joh，共2个匹配字段，满足要求，返回
     完全符合要求。

性能优化建议 #

如果数据量较大，可以给三个字段建小写函数索引提升匹配速度：

CREATE INDEX idx_contact_firstname_lower ON contact (lower("firstName") varchar_pattern_ops);
CREATE INDEX idx_contact_middlename_lower ON contact (lower("middleName") varchar_pattern_ops);
CREATE INDEX idx_contact_lastname_lower ON contact (lower("lastName") varchar_pattern_ops);

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