软删除 + 唯一索引的幽灵冲突：MySQL 不懂你的 is_deleted

某后台管理系统有张配置表，支持「添加 → 删除 → 再添加同名配置」。删除走软删除（is_deleted=1），列表查询过滤掉已删除行，看起来完全正常。但用户一旦把某条配置删掉再重建，必然报 500：

ER_DUP_ENTRY: Duplicate entry 'report-42-fieldKey' for key 'uniq_report_key'

前端显示「该 key 不存在」，数据库却说「重复了」。

现象

第一次创建：成功
删除后再创建同名配置：ER_DUP_ENTRY 报错
SELECT ... WHERE is_deleted=0 查不到任何冲突行
触发条件精确：必须经历「创建 → 软删除 → 再创建」这一个完整循环

根因

软删除的本质是逻辑删除，物理行依然存在于表中。而 MySQL 唯一索引不理解任何业务语义——它只看索引列的值，不管 is_deleted 是 0 还是 1。

假设唯一索引定义为：

UNIQUE KEY uniq_report_key (report_id, `key`)

软删除后，(report_id='42', key='fieldKey', is_deleted=1) 这一行还躺在表里，牢牢占住了索引槽位。再 INSERT 同样的 (report_id, key) 组合，唯一约束立刻触发。

坑：软删除行是唯一索引的幽灵占位符

is_deleted=1 的行对业务层是「不可见」的，但对 MySQL 唯一索引是完全可见的。只要物理行还在，该 key 组合就永久被占用，后续任何同 key 的 INSERT 都会失败。这个 bug 在第一次删除前完全无法复现，往往在灰度或上线后才爆出来。

正解

有两条出路，按场景选一个：

方案 A：软删除时顺手清除同 key 的旧 tombstone

// DELETE 接口核心逻辑
const [rows] = await pool.execute<RowDataPacket[]>(
  'SELECT report_id, sort, `key` FROM configs WHERE id = ? AND is_deleted = 0',
  [id]
)
const { report_id, sort, key: fieldKey } = rows[0]
 
// 先清理同 key 的旧软删除行，释放唯一索引槽位
await pool.execute(
  'DELETE FROM configs WHERE report_id = ? AND `key` = ? AND is_deleted = 1',
  [report_id, fieldKey]
)
 
// 再标记当前行为软删除
await pool.execute('UPDATE configs SET is_deleted = 1 WHERE id = ?', [id])

逻辑很直白：同一个 key 在历史上只可能有一条「有效」记录，旧的 tombstone 没有任何保留价值，直接物理清除即可。

方案 B：把 is_deleted 纳入唯一索引

-- MySQL 8.0+
ALTER TABLE configs
  DROP KEY uniq_report_key,
  ADD UNIQUE KEY uniq_report_key (report_id, `key`, is_deleted);

这样 (42, 'fieldKey', 0) 和 (42, 'fieldKey', 1) 被视为两个不同的 key 组合，可以共存。

注意局限：这个方案允许同一个 key 存在多条 is_deleted=1 的历史行，查询时必须始终带 is_deleted=0 过滤，否则历史行会乱入。

正解：软删除前清理同 key 的旧 tombstone

推荐方案 A。在软删除当前行之前，先 DELETE 掉同 key 的所有已删除行——它们是无用的幽灵，释放掉即可。既不改表结构，又让「删除 → 重建」循环永远通畅。

额外加固：POST 时提前 409

光靠数据库报 ER_DUP_ENTRY 还不够优雅，在 API 层加一道前置校验，把数据库异常转成可读的业务错误：

// POST 创建接口，先做存在性检查
const [existing] = await pool.execute(
  'SELECT id FROM configs WHERE report_id = ? AND `key` = ? AND is_deleted = 0',
  [reportId, key]
)
if ((existing as RowDataPacket[]).length) {
  return errorResponse('该 key 已存在', 409)
}

一句话外卖

软删除的「逻辑不可见」只对业务层有效，MySQL 唯一索引永远活在物理层——软删除前必须清理同 key 的旧 tombstone，否则每次「删除再重建」都是定时炸弹。