PostgreSQL中ILIKE组合参数查询无结果问题排查

你当前的查询逻辑是将完整的_customer_info字符串（比如'name lastname 1234910'）作为匹配条件，去匹配first_name、last_name或national_code中的某一个字段。但实际每个字段仅存储单个维度的信息（比如first_name只存'name'，last_name只存'lastname'），没有任何一个字段会包含整个多关键词的长字符串，因此查询返回空结果。

需要将传入的多关键词字符串拆分为单个关键词，让每个关键词都能匹配三个字段中的任意一个。以下是两种可行的修改方式：

方式1：灵活匹配任意数量的关键词 #

利用PostgreSQL的字符串处理函数，将输入字符串按空格拆分后，让每个关键词都去匹配三个字段中的任意一个：

SELECT 
    t1.id, 
    concat(t1.first_name, ' ', t1.last_name) as full_name,
    t1.national_code,
    row_number() over (order by t1.published_at desc)
FROM tbl_customers as t1 
RIGHT JOIN tbl_ticketings as t2 ON t1.id = t2.customer_id 
WHERE 
    -- 将输入字符串转为带通配符的关键词数组，每个关键词匹配任意字段
    EXISTS (
        SELECT 1 
        FROM unnest(string_to_array(_customer_info, ' ')) AS keyword
        WHERE 
            t1.first_name ILIKE '%' || keyword || '%'
            OR t1.last_name ILIKE '%' || keyword || '%'
            OR t1.national_code ILIKE '%' || keyword || '%'
    )
GROUP BY t1.id
ORDER BY t1.published_at desc  

注：此方式支持任意数量的关键词，且只要有一个关键词匹配就会返回结果；如果需要所有关键词都匹配，将EXISTS改为ALL即可。

方式2：固定数量关键词的精准匹配 #

如果输入的关键词数量固定（比如3个），可以直接拆分后逐个匹配：

SELECT 
    t1.id, 
    concat(t1.first_name, ' ', t1.last_name) as full_name,
    t1.national_code,
    row_number() over (order by t1.published_at desc)
FROM tbl_customers as t1 
RIGHT JOIN tbl_ticketings as t2 ON t1.id = t2.customer_id 
WHERE 
    -- 匹配第一个关键词
    (t1.first_name ILIKE '%' || split_part(_customer_info, ' ', 1) || '%'
     OR t1.last_name ILIKE '%' || split_part(_customer_info, ' ', 1) || '%'
     OR t1.national_code ILIKE '%' || split_part(_customer_info, ' ', 1) || '%')
    -- 匹配第二个关键词
    AND (t1.first_name ILIKE '%' || split_part(_customer_info, ' ', 2) || '%'
         OR t1.last_name ILIKE '%' || split_part(_customer_info, ' ', 2) || '%'
         OR t1.national_code ILIKE '%' || split_part(_customer_info, ' ', 2) || '%')
    -- 匹配第三个关键词
    AND (t1.first_name ILIKE '%' || split_part(_customer_info, ' ', 3) || '%'
         OR t1.last_name ILIKE '%' || split_part(_customer_info, ' ', 3) || '%'
         OR t1.national_code ILIKE '%' || split_part(_customer_info, ' ', 3) || '%')
GROUP BY t1.id
ORDER BY t1.published_at desc  

注：如果需要“任意关键词匹配”，把条件中的AND改成OR即可。

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