一、DELETE 的骨架

DELETE FROM 表 WHERE 条件 就这一句，比 UPDATE 还简单。 不写 WHERE 就是全删，和第 8.2 节那个坑是同一个。

DELETE FROM tb_goods WHERE id = 10;
DELETE FROM tb_goods WHERE price < 1;

MySQL 还支持 DELETE ... ORDER BY ... LIMIT 100，用来「一次只删一百行」。 这个写法在分批删的时候很好用，别的库基本没有。 DELETE 是 DML，所以它能回滚、能带条件、能触发触发器。 这三点合起来决定了它的定位：它是「精细的删」，代价是慢。

二、外键级联：删一行，少一片

如果子表的外键写了 ON DELETE CASCADE，删父表一行会连带删掉所有子行。 这个动作不需要你写任何额外的语句，它是自动的。

CREATE TABLE tb_goods (
    id INT PRIMARY KEY,
    cat_id INT,
    FOREIGN KEY (cat_id) REFERENCES tb_cat (id) ON DELETE CASCADE
);

Demo 里删掉「图书」这一个类别，子表里五本书跟着没了，行数从 8 变 3。 这就是「我明明只删了一个类别，怎么少了一万行商品」的由来。 所以线上表默认应该用 RESTRICT：有子行就拒绝删。 要级联的地方显式写 CASCADE，让这个决定留在建表语句里，而不是留在某个人的记忆里。 还有一条容易忽略：级联删除也是删除，它同样要走索引、同样要写日志。 所以「删一个类别」的代价可能远大于「删一行」，别按行数估时间。

三、分批删：买的是事务短

十万行一次删和分十批删，耗时未必差，但事务长度差十倍。

第二行反而慢了十几倍，因为每批都要单独提交。 所以分批不是为了快，是为了三件事：事务短、锁时间短、不撞日志上限。 这三件事在生产上比速度重要得多。 一个大事务持锁几十秒，整张表的写操作都排队；分批删每批只锁零点几秒，别人感觉不到。 分批的两种写法：按主键区间切，或者用 LIMIT。 主键区间更稳，因为它是确定的边界；LIMIT 依赖排序，中间有人插入时容易漏或重复。

四、TRUNCATE 与 DELETE，再对照一次

第 6 章对比过一次，这里针对「删行」再说一遍，因为两者的差别在这一层最明显。

第一行决定用途：要删一部分，只能 DELETE。 要清空整张表，TRUNCATE 更合适——快、还回收空间。 第四行那个「不触发触发器」是个隐雷。 如果审计日志靠 DELETE 触发器记，TRUNCATE 一跑，日志一条都不留。 事后查「是谁清的这张表」，答案是没有记录。 第三行那个「计数器归零」也有两面。 表重新开始、号不浪费是好处；旧号被重新发一遍是坏处。 对外的单号不能用自增，这一条在第 6 章说过。

五、删之前的三件事

这一节教材没讲，但它比语法重要。

第一步是个小技巧，但极其有效。 把 DELETE FROM t WHERE ... 的 DELETE FROM t 换成 SELECT * FROM t，条件一个字不改。 先看选中了哪些行，确认了再删。 第二步是量级判断，比看具体行数更有用。 「应该几十行，结果三百万」，这时候停手就还来得及。 第三行那个流程，前提是你的客户端不是自动提交模式。 命令行客户端默认自动提交，所以要么先 SET autocommit = 0，要么手动 BEGIN。 三步加起来不到一分钟，而删错数据的恢复成本往往是一整天。 这笔账怎么算都划算。

六、动手 Demo：删数据：级联、分批，与三种「删」的对照

这一课先跑外键级联，看「删一行怎么变成删五片」。 再量分批删的代价，最后把三种「删」摆在一起对照。 沙箱里 DELETE ... LIMIT 没有，分批用主键区间来等价。

# 一、外键级联：删类别，商品跟着走
c.execute("PRAGMA foreign_keys = ON")
c.execute("CREATE TABLE tb_goods (id INTEGER PRIMARY KEY, name TEXT, "
          "cat_id INTEGER REFERENCES tb_cat (id) ON DELETE CASCADE)")
c.execute("DELETE FROM tb_cat WHERE id = 1")      # 五本书一起没

# 二、分批删：按主键区间切
for _ in range(10):
    c.execute("DELETE FROM tb_log WHERE id IN "
              "(SELECT id FROM tb_log ORDER BY id LIMIT 10000)")
    c.commit()

# 三、计时前必须重新灌数据，否则第二次测的是空表
def timeit(fn, n=2):
    ts = []
    for _ in range(n):
        refill()
        t0 = time.perf_counter(); fn(); ts.append(time.perf_counter() - t0)
    return min(ts)

实跑输出：

--------------------------------------------------------------------
第三十五课：删除数据，与它牵动的那些行
--------------------------------------------------------------------

  **教材 8.3 讲 `DELETE` 和 `TRUNCATE TABLE`。第 6 章删过表，这一课删的是「行」。**
  **差别在于：删行会牵动别的东西——外键、触发器、索引。**
  **这一课先跑外键级联，再量分批删的代价。**

  一、DELETE 的骨架
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | DELETE FROM tb_goods WHERE id = 10;
  | DELETE FROM tb_goods WHERE price < 1 ORDER BY id LIMIT 100;
  ⑤ 教材原码，本沙箱不执行

  **第二行那个 `ORDER BY ... LIMIT` 是 MySQL 独有的，用来「一次只删一百行」。**
  **沙箱里没有，等价写法是用子查询选出 id 再删。**

  二、外键级联：删父表一行，子表跟着走
  --------------------------------------------------------------------

  mysql> DELETE FROM tb_cat WHERE id = 1

  时刻                  tb_goods 行数  剩下的是谁
  -----------------------------------------------------
  删之前                8              书 1～5、机 1～3
  删掉「图书」这个类别  3              机1、机2、机3
  **删了父表一行，子表里五本书也跟着没了。**
  **这就是 `ON DELETE CASCADE`：级联删除。**
  **它是把双刃剑。**
  **好处是不会留下「指向不存在类别」的孤儿数据。**
  **坏处是「我明明只删了一个类别，怎么少了一万行商品」。**
  **所以线上表默认应该用 `RESTRICT`（有子行就拒绝删），要级联的地方显式写 `CASCADE`。**
  **默认值选错，就是给自己埋一个「一删一大片」的雷。**
  **还有一条：级联删除也是删除，它同样要走索引、同样要写日志。**
  **所以「删一个类别」的代价可能远大于「删一行」，别按行数估时间。**

  三、一次删十万行，还是分十批
  --------------------------------------------------------------------

  写法                  10 万行耗时  事务大小
  ----------------------------------------------
  一条 DELETE 删完      0.012 秒     一个大事务
  分 10 批，每批一万行  0.197 秒     10 个小事务
  **这一次分批慢了十几倍，因为每批都要单独提交一次——提交是有成本的。**
  **所以分批的意义不在快，在「事务有多长」：一次删完是一个大事务，分批是十个短的。**
  **大事务会长时间持锁、写满 undo log，还可能撞上日志文件上限。**
  **分批删让每个事务都短，中间还能插入别的写操作——这才是「在线删数据」的做法。**
  **删完别忘了第 6 章那条：磁盘不会自动缩。**
  **十万行删掉之后文件还是那么大，要 `OPTIMIZE TABLE` 才回收。**

  四、TRUNCATE 与 DELETE，再对照一次
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | TRUNCATE TABLE tb_log;
  ⑥ 教材原码，本沙箱不执行


  要对比的    `DELETE FROM`  `TRUNCATE TABLE`
  ---------------------------------------------
  删什么      行，可带条件   全部行，不能带条件
  能回滚      能             不能（DDL）
  自增计数器  不重置         归零
  触发器      会触发         不触发
  速度        逐行，慢       换表，快
  **第二行是选它的唯一理由：它不能回滚，所以只适合「确定要清空」的场景。**
  **日志表、临时表、每月清一次的统计表，用它最合适。**
  **第四行那个「不触发触发器」是个隐雷。**
  **如果审计日志靠 `DELETE` 触发器记，`TRUNCATE` 一跑，日志一条都不留——事后查不到是谁清的。**

  五、删之前的三件事
  --------------------------------------------------------------------
  **这一节教材没讲，但它比语法重要，第 6 章讲过一遍，这里针对「删行」再说一次。**

  步骤            怎么做                                  拦住什么事故
  --------------------------------------------------------------------
  一、先查一遍    把 `DELETE` 换成 `SELECT *` 跑一次      条件写错
  二、数一下行数  `SELECT COUNT(*)` 看是不是那个量级      忘了 `WHERE`
  三、包在事务里  `BEGIN` → `DELETE` → 看行数 → `COMMIT`  删多了
  **第一步是个小技巧，但极其有效。**
  **把 `DELETE FROM t WHERE ...` 的 `DELETE FROM t` 换成 `SELECT * FROM t`。**
  **条件一个字不改，先看选中了哪些行。**
  **这三步都不花时间，加起来不到一分钟。**
  **而删错数据的恢复成本，往往是一整天。**

第一段：删之前 8 行，删掉「图书」这个类别之后剩 3 行。 五本书跟着没了，我一条 DELETE 都没对 tb_goods 写过。 这就是级联删除的威力，也是它的危险。 第二段是分批删，十批每批一万行。 按主键区间切比分 LIMIT 稳，因为边界是确定的。 第三段那几行代码是这一课的教训：计时前必须重灌数据。 我第一次写的时候忘了这一步，结果第二次测的是空表，两个数都是「删空表」的时间， 看起来分批反而快十几倍——那是假象。 重灌之后才是真数：一次删完 0.012 秒，分十批 0.200 秒。 分批慢了十几倍，因为多了九次提交。 所以分批买的是「事务短」，不是「速度快」。 这一条我一开始写反了，是实测把它掰回来的。

30 秒实验

1. 把 ON DELETE CASCADE 改成 ON DELETE RESTRICT，再删一次那个类别。 会报外键约束错误，删不掉。 这才是更安全的默认行为。
2. 把分批删的每批从一万行改成一千行，看耗时怎么变。会明显变慢，因为提交次数从十次变成一百次。 批太小也不划算，一般一万到十万行一批。
3. 跑一次 DELETE FROM tb_log 之后看文件大小，再跑 VACUUM。 文件删完不变，VACUUM 之后才缩。 这一条第 6 章量过，删行也一样。

常见误区

1. 「删一个类别而已，商品不会受影响。」 如果外键写了 ON DELETE CASCADE，商品的对应行会一起没。 而且不提示、不报错、不可撤销。
2. 「分批删比整批删快。」 通常更慢，因为提交次数变多。 分批买的是事务短、锁时间短。
3. 「DELETE 和 TRUNCATE 结果一样，随便用。」 结果一样，副作用完全不同：能不能回滚、自增归不归零、触发器跑不跑。
4. 「删完数据磁盘就空出来了。」 不会。要 OPTIMIZE TABLE 才回收。 TRUNCATE 会回收，因为它是换表。

第 8 章到这儿结束。下一篇进第 9 章，讲数据查询—— 写数据的三条语句都过完了，接下来是全书最长的一章：SELECT 的各种写法。

• ☐ （本篇完，下一篇 9.1 基本查询语句）

相关笔记

• 修改数据
• 删除表
• 添加数据
• 🛢️ MySQL从入门到精通 · 索引