删除之后仍然被占着的键

软删的行仍然是行,所以它仍然占着唯一键——这就是为什么朴素的 UNIQUE (owner_id, slug) 会挡住用户重建自己刚删掉的东西。解法是用一个生成列把死行投影成 NULL,让索引只覆盖存活行:slug_live = IF(deleted_at IS NULL, slug, NULL)。同时,「存活」的判据必须是 deleted_at IS NULL,永远不是 status <> "deleted"。

投诉永远是同一个形状:「我删掉了,它却说已经存在。」

为什么删除没有释放键

软删是一条 UPDATE,不是 DELETE。行还在、列还在,唯一索引里的条目也还在。于是用户以为已经消失的那条记录,挡住了他期待成功的第二次插入。

直觉会说「NULL 不等于 NULL,所以索引会忽略软删行」。这句话只在被约束的列本身为 NULL 时成立,而 slug 不是 NULL。

被人写错的「存活」判据

动手之前先定清楚什么叫存活,而且要从 schema 里读,不要凭记忆里的枚举:

-- 存活的判据只有这一条
SELECT COUNT(*) FROM projects WHERE deleted_at IS NULL;

在真实的一张表里,status 枚举根本没有 deleted 这个值——软删只写 deleted_at。用 WHERE status <> 'deleted' 统计,把 13 条软删行都算成了「存活重复」,于是得出「加索引前必须先清数据」的错误结论。数据是干净的,是判据在编故事。

为什么朴素的 UNIQUE 一定不行

两步,都成立:

  1. 软删行仍然持有 (owner_id, slug),所以硬约束会拒绝合法的重建。
  2. 「NULL 技巧」只对真正为 NULL 的列有效,而你在意的那几列正是身份列。

所以要把死行投影成 NULL,然后约束投影。

DDL

ALTER TABLE projects
  ADD COLUMN slug_live VARCHAR(100)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, slug, NULL)) VIRTUAL,
  ADD UNIQUE KEY uk_projects_owner_slug_live (owner_id, slug_live);

跑出来的结果,按顺序:

ERROR 1062 (23000) at line 24: Duplicate entry '1-demo' for key 'projects.uk_projects_account_slug_live'
+----+------------+------+------+-----------+
| id | account_id | slug | live | slug_live |
+----+------------+------+------+-----------+
|  1 |          1 | demo |    0 | NULL      |
|  3 |          1 | demo |    1 | demo      |
+----+------------+------+------+-----------+
ERROR 1062 (23000) at line 43: Duplicate entry '1-demo' for key 'projects_naive.uk_naive'

一段日志三个事实:第二条存活行被拒;软删行的投影是 NULL,所以重建成功;同样的操作放在朴素的 UNIQUE (owner_id, slug) 上失败——最后那条报错,就是用户拿来投诉的那条。

两个 ALTER 坑

坑 症状 修法
加了 ALGORITHM=INPLACE ERROR 1846 … INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions 去掉提示,走默认 COPY
VARCHAR 长度照抄示例 索引被拒,报错看不出来是长度问题 与源列长度完全一致

还有第三个,过一阵才出现:生成列不可写(3105)。任何按 INFORMATION_SCHEMA 动态拼出来的 INSERT 都会试图写它。

动 DDL 前的三项检查

  1. 有没有地方 SELECT *? 虚拟列会让 * 多一列,位置式扫描整体错位——在能编译的语言里,静默错位。
  2. 有没有按 INFORMATION_SCHEMA 动态建 INSERT? 就是上面那条 3105。
  3. 现在有存活重复吗? 有的话索引建不起来。用正确的判据数一遍,并且先 dump:mysqldump projects | gzip > projects.sql.gz。

每条写入路径都要把 1062 翻译掉

约束只是一半。另一半是用户看到的那句话:

if err != nil && (strings.Contains(err.Error(), "Duplicate entry") || strings.Contains(err.Error(), "1062")) {
    return 0, fmt.Errorf("slug %q already exists for this account — refresh and retry", slug)
}

把这张表上的每一条 INSERT 数一遍。我们那次有两条:自动建链路径 + 通用 CRUD handler。改一条等于没改。

怎么知道它真的修好了

  • 存活重复被拒:START TRANSACTION; INSERT …; ROLLBACK; → 期望 1062。
  • 软删行不挡重建:先软删,再走同样的插入 → 期望成功。
  • 索引只覆盖存活行:SELECT COUNT(*) FROM projects WHERE slug_live IS NOT NULL 等于存活行数。
  • 并发:八个同时插入同一键,只落一行,其余拿 1062。用 xargs -P 8,不要用 shell 的 &——很多 shell 和 CI 会拒绝后台作业,而串行的「并发测试」什么都证明不了。

怎么回滚

  • 索引和列一起删。 只删索引会留下一个 SELECT * 调用方已经在扫的列。
  • 要恢复的是数据,不只是结构:删掉虚拟生成列是安全的(不存任何东西),但如果你在上线过程中顺手清了重复行,那部分删除是 DDL 回不来的。
  • 留住 pre-DDL 的 dump,直到你看到新约束下完整跑通一轮写入。只有 dump 能恢复行,而不只是恢复结构。
  • 如果这张表的修复手段当时是 ON DUPLICATE KEY UPDATE,现在重测一遍:它多了一个以前没有的生成列。

我们没验的三件事

  • 表大到 COPY ALTER 会疼时的行为。 我们那张很小;五千万行要另一套上线方式(gh-ost / pt-online-schema-change),我们没跑过。
  • 生成列会不会干扰 MySQL 自己的 ON DUPLICATE KEY UPDATE,就在这个形状下。
  • 生产写入压力下 COPY 路径的锁足迹。 我们量的是正确性,不是阻塞。

可运行的复现(同一个文件里含朴素约束的对照)在这个仓库里。

常见问题

为什么不直接物理删除?

因为审计、外键和报表通常还要它。一旦是软删,唯一索引就必须知道这件事,而 MySQL 没有部分索引。

生成列占存储吗?

VIRTUAL 生成列在读取时计算、不存储,但参与索引。占空间的是索引项,而且现在只有存活行才有索引项。

「存活」的判据是什么?

deleted_at IS NULL。不是 status <> "deleted"——在真实的一张表里,那个枚举值根本不存在,于是错误的判据把 13 条软删行算成「存活重复」,得出「必须先清数据」的错误结论。

这个索引能在线加吗?

加 ALGORITHM=INPLACE 不行:虚拟列与其它 ALTER 动作一起提交会报 1846。走默认的 COPY 路径,小表几秒。

来源


本文最后一次核验:2026-11-18 · 2026-11-18 在 MySQL 8.4.13(Ubuntu 26.04 / WSL)上复现:存活重复报 1062(生成列索引)、软删行不挡重建、同一套操作在朴素 UNIQUE 上失败——输出原样贴在下面。