如何创建能匹配null或空字段的唯一键，有无对应通用解决方案？

这类需求属于自定义部分匹配唯一性约束，没有标准SQL的原生唯一键支持，行业内有三种成熟的通用解决方案：

方案1：前置触发器（全数据库兼容） #

这是适配性最高的方案，所有支持触发器的关系型数据库都可以直接实现，逻辑和你写的校验规则完全对齐：

• 编写BEFORE INSERT/BEFORE UPDATE行级触发器，在数据写入前执行匹配校验，命中规则就抛出异常终止操作。

PostgreSQL实现示例：

-- 定义校验函数
CREATE OR REPLACE FUNCTION check_person_duplicate()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM person
    WHERE (firstname = NEW.firstname OR firstname IS NULL)
      AND (lastname = NEW.lastname OR lastname IS NULL)
      AND (dob = NEW.dob OR dob IS NULL)
  ) THEN
    RAISE EXCEPTION '匹配的人员记录已存在，禁止写入';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到person表
CREATE TRIGGER person_unique_check
BEFORE INSERT OR UPDATE ON person
FOR EACH ROW EXECUTE FUNCTION check_person_duplicate();

• 优势：逻辑100%自定义，不需要修改原有表结构和业务代码
• 注意：高并发场景需要在校验逻辑中加行锁（比如SELECT 1 FROM person ... FOR UPDATE），避免竞态条件导致的重复写入。

方案2：应用层校验+事务 #

如果你的系统并发量较低，不需要严格的数据库层面强约束，可以直接在业务代码中实现校验：

• 把「校验是否存在匹配记录」和「插入新记录」两个操作放在同一个事务内执行，校验不通过就回滚事务。
• 优势：实现成本最低，不需要修改数据库配置，规则调整灵活
• 注意：仅适用于并发写入少的场景，高并发下依然有概率出现竞态写入。

方案3：虚拟列+联合唯一索引（支持虚拟列的数据库适用） #

如果你的业务可以定义全局唯一的null占位值（比如不会出现在业务数据里的特殊字符串/日期），可以用这个方案获得比触发器更高的写入性能：

• 给每个校验字段生成对应的虚拟列，把null替换为占位值，再给虚拟列加联合唯一索引即可。

MySQL实现示例：

-- 生成替换null的虚拟列
ALTER TABLE person 
ADD COLUMN firstname_unique VARCHAR(255) GENERATED ALWAYS AS (IFNULL(firstname, '__NULL_PLACEHOLDER__')) STORED,
ADD COLUMN lastname_unique VARCHAR(255) GENERATED ALWAYS AS (IFNULL(lastname, '__NULL_PLACEHOLDER__')) STORED,
ADD COLUMN dob_unique VARCHAR(255) GENERATED ALWAYS AS (IFNULL(dob, '1000-01-01')) STORED;

-- 加联合唯一索引
CREATE UNIQUE INDEX idx_person_unique ON person(firstname_unique, lastname_unique, dob_unique);

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