逻辑删除与唯一约束冲突

一、核心问题

业务要求：用户在同一时间只能有一条有效记录。

技术实现：(user_id, recorded_at) 唯一索引 + deleted 逻辑删除。

实际冲突：已逻辑删除的记录仍占着索引位置，新记录无法插入。

二、真实案例

1. 预约系统场景

典型代表：医疗预约系统

问题表现：患者A预约了周一10点，后取消预约。数据库中将该记录标记为deleted=1，但(医生ID, 时间)的唯一索引仍被占用。导致患者B无法预约同一时段。

业务影响：医疗资源闲置，用户体验差。

2. 用户系统场景

典型代表：SaaS企业员工管理系统

问题表现：员工A（工号1001）离职，账号被禁用（deleted=1）。新员工入职时无法复用工号1001，因为唯一索引(公司ID, 工号)已被占用。

业务影响：员工编号管理混乱，HR系统需要额外处理逻辑。

（其实这个例子不太好，从审计角度来说工号不应该给第二个人复用。）

3. 电商促销场景

典型代表：优惠券领取系统

问题表现：用户领取优惠券后退券，记录标记为deleted=1。用户再次尝试领取同类型优惠券时，因(用户ID, 优惠券类型)唯一索引冲突而失败。

业务影响：促销活动效果打折，用户参与度降低。

三、解决方案分析

方案一：部分唯一索引（推荐）

-- MySQL 8.0.13+ 支持
CREATE UNIQUE INDEX idx_user_record_active 
ON records (
  (CASE WHEN deleted = 0 THEN user_id ELSE NULL END),
  (CASE WHEN deleted = 0 THEN recorded_at ELSE NULL END)
);

优点：

• 精准约束：只对deleted=0的记录生效
• 业务语义正确：用户删除后可重建
• 数据库层保证：无需应用层额外校验

适用：MySQL 8.0+ 新项目

方案二：包含 deleted 的联合索引

(user_id, recorded_at, deleted)

缺陷：不允许“删除→重建→再删除”的循环操作。

适用场景：确保每个组合只被删除一次的特定业务。

方案三：应用层校验

-- 插入前检查
SELECT COUNT(*) FROM records 
WHERE user_id = ? AND recorded_at = ? AND deleted = 0;

风险：高并发下可能同时通过校验，导致重复数据。

补救：需配合悲观锁或分布式锁，增加系统复杂度。

四、延伸：状态感知的唯一约束

这个问题本质是状态相关的唯一性要求，其他类似场景包括：

1. 订单系统：仅进行中的订单要求(用户, 商品)组合唯一
2. 设备绑定：仅激活的设备要求(用户, 设备)绑定唯一
3. 会议室预约：仅确认的预约要求时间段唯一

通用模式：只有当数据处于特定状态时，某些字段组合才需要唯一。

五、实施建议

设计阶段检查

• 明确唯一性是否包含逻辑删除记录
• 确认MySQL版本支持情况
• 设计对应索引方案

代码开发注意

• 异常处理：区分“记录已存在”和“记录被逻辑删除占用”
• 用户提示：明确告知用户不能操作的原因

测试要点

• 删除后重建的流程验证
• 并发操作下的数据一致性
• 数据迁移的兼容性

六、总结

关键点：

1. 逻辑删除影响唯一约束，这是设计时易忽略的点
2. 部分唯一索引是最佳解决方案（MySQL 8.0+）
3. 问题在预约、用户管理、电商等系统中普遍存在

实践建议：新项目设计阶段就采用方案一，避免后期重构成本。老项目根据业务影响评估改造必要性。

最终检查：每个唯一索引都应问一句：“这个唯一是针对所有记录，还是仅针对有效记录？”