删除之后仍然被占着的键
软删的行仍然是行,所以它仍然占着唯一键——这就是为什么朴素的 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 一定不行
两步,都成立:
- 软删行仍然持有
(owner_id, slug),所以硬约束会拒绝合法的重建。 - 「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 前的三项检查
- 有没有地方
SELECT *? 虚拟列会让*多一列,位置式扫描整体错位——在能编译的语言里,静默错位。 - 有没有按
INFORMATION_SCHEMA动态建INSERT? 就是上面那条 3105。 - 现在有存活重复吗? 有的话索引建不起来。用正确的判据数一遍,并且先 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 上失败——输出原样贴在下面。