MariaDB中如何按日期范围或基于其他列值强制唯一性约束

MariaDB 原生不支持 PostgreSQL 那种带过滤条件的部分唯一索引，但可以通过两种不需要修改历史数据的方案实现需求。

方案1：虚拟计算列 + 唯一索引（推荐） #

这是性能最优、侵入性最低的实现方式，原理是利用 MariaDB 唯一索引允许存在多个 NULL 值的特性，仅对符合约束条件的行生成非 NULL 的校验值：

• 新增一个虚拟计算列，当行符合你的约束规则（比如指定日期之后、特定字段满足条件）时，值为要做唯一性校验的列的内容，不符合规则的历史行生成 NULL 值
• 对该计算列创建唯一索引即可

示例代码（以表t_biz、需唯一列serial_num、仅校验create_time >= '2024-01-01'的行为例）：

-- 新增虚拟计算列，VIRTUAL 类型不会持久化存储，不占用额外空间
ALTER TABLE t_biz ADD COLUMN unique_check_col VARCHAR(255) 
GENERATED ALWAYS AS (IF(create_time >= '2024-01-01', serial_num, NULL)) VIRTUAL;

-- 对计算列创建唯一索引
CREATE UNIQUE INDEX idx_uq_serial_num_new ON t_biz(unique_check_col);

如果需要多条件校验，直接修改计算列的生成规则即可，比如同时要求is_valid = 1才校验唯一性：
GENERATED ALWAYS AS (IF(create_time >= '2024-01-01' AND is_valid = 1, serial_num, NULL)) VIRTUAL

方案2：触发器实现 #

适合约束逻辑更复杂的场景，通过前置触发器拦截插入/更新操作做自定义校验：
示例代码（和上述规则一致）：

DELIMITER //
-- 插入校验触发器
CREATE TRIGGER trg_check_serial_unique_insert BEFORE INSERT ON t_biz
FOR EACH ROW
BEGIN
  IF NEW.create_time >= '2024-01-01' THEN
    IF EXISTS (SELECT 1 FROM t_biz WHERE serial_num = NEW.serial_num AND create_time >= '2024-01-01') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '2024年1月1日之后的serial_num不允许重复';
    END IF;
  END IF;
END //

-- 更新校验触发器
CREATE TRIGGER trg_check_serial_unique_update BEFORE UPDATE ON t_biz
FOR EACH ROW
BEGIN
  IF NEW.create_time >= '2024-01-01' THEN
    IF EXISTS (SELECT 1 FROM t_biz WHERE serial_num = NEW.serial_num AND create_time >= '2024-01-01' AND id != NEW.id) THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '2024年1月1日之后的serial_num不允许重复';
    END IF;
  END IF;
END //
DELIMITER ;

方案对比 #

• 计算列+唯一索引：性能更高，是数据库原生索引校验，逻辑简单不易出错，不会额外占用历史数据的存储空间，适合高并发场景
• 触发器：灵活性更高，支持跨表校验、多规则联动等复杂逻辑，但并发场景下性能略差，需要注意边界条件避免漏判

两种方案均不会修改或删除任何历史数据，完全符合权限限制要求。

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