PostgreSQL：如何基于全名列按姓名、父称灵活实现筛选？

首先纠正你原查询中的笔误：语句里的 lastname ILIKE '%'||lastname||'%' 应该改为 lastname ILIKE '%'||:lastname||'%'，否则该条件会永远成立（字段匹配自身），导致筛选逻辑失效。

针对数据库存在 full_name 列的场景，以下是几种实用的筛选实现方案，支持单字段（名字/姓氏/父称）或多字段组合查询：

方案1：固定逻辑组合匹配（简单直接） #

如果 full_name 格式固定为「姓氏 名字 父称」的顺序，可通过多条件模糊匹配实现精准筛选：

SELECT * 
FROM employee 
WHERE is_deleted != true 
  AND (
    -- 所有输入为空时，返回全部未删除数据
    (:lastname IS NULL AND :firstname IS NULL AND :middlename IS NULL)
    OR
    -- 匹配所有非空输入对应的内容
    full_name ILIKE '%' || COALESCE(:lastname, '') || '%'
    AND full_name ILIKE '%' || COALESCE(:firstname, '') || '%'
    AND full_name ILIKE '%' || COALESCE(:middlename, '') || '%'
  );

• 用 COALESCE 处理空输入：若某个参数为空，自动替换为空字符串，对应的模糊匹配会忽略该条件。
• 逻辑规则：只有 full_name 同时包含所有非空的输入字段时，才会被选中。

方案2：灵活组合匹配（支持任意输入顺序） #

如果用户输入的姓名组合顺序不固定（比如输入「名字 姓氏」而非「姓氏 名字」），或 full_name 格式不严格，可通过多条件 OR 覆盖所有可能的匹配场景：

SELECT * 
FROM employee 
WHERE is_deleted != true 
  AND (
    (:lastname IS NULL AND :firstname IS NULL AND :middlename IS NULL)
    OR
    (:lastname IS NOT NULL AND full_name ILIKE '%' || :lastname || '%')
    OR
    (:firstname IS NOT NULL AND full_name ILIKE '%' || :firstname || '%')
    OR
    (:middlename IS NOT NULL AND full_name ILIKE '%' || :middlename || '%')
    -- 支持姓氏+名字的双向组合匹配
    OR
    (:lastname IS NOT NULL AND :firstname IS NOT NULL 
     AND full_name ILIKE '%' || :lastname || '%' || :firstname || '%')
    OR
    (:lastname IS NOT NULL AND :firstname IS NOT NULL 
     AND full_name ILIKE '%' || :firstname || '%' || :lastname || '%')
  );

• 该方案既支持单字段独立匹配，也支持姓氏与名字的无序组合匹配。
• 若需支持更多组合（如名字+父称、姓氏+父称），可继续添加对应的 OR 分支。

方案3：全文搜索（大表场景性能最优） #

如果表数据量较大，ILIKE 模糊匹配的性能会明显下降，可使用PostgreSQL的全文搜索功能优化：

1. 先为 full_name 创建全文索引：

-- 英文场景使用english配置，中文场景需替换为zhparser等中文分词插件的配置
CREATE INDEX idx_employee_full_name_ts ON employee USING GIN (to_tsvector('english', full_name));

2. 编写全文搜索查询语句：

SELECT * 
FROM employee 
WHERE is_deleted != true 
  AND (
    (:lastname IS NULL AND :firstname IS NULL AND :middlename IS NULL)
    OR
    to_tsvector('english', full_name) @@ to_tsquery('english', 
      array_to_string(
        ARRAY[
          CASE WHEN :lastname IS NOT NULL THEN :lastname END,
          CASE WHEN :firstname IS NOT NULL THEN :firstname END,
          CASE WHEN :middlename IS NOT NULL THEN :middlename END
        ]
        , ' & '
      )
    )
  );

• 全文搜索通过 & 连接所有非空输入字段，实现精准的多字段匹配，性能远高于模糊匹配。
• 中文场景需提前配置中文分词插件，确保姓名拆分匹配的准确性。

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