INSERT、UPDATE、DELETE

这一课你将能做到

1. 用 INSERT 完成单行、批量、INSERT … SELECT 三种写入，并理解自增主键的行为
2. 安全地执行 UPDATE / DELETE：先 SELECT 验证、带 WHERE、必要时用事务兜底
3. 说清 DELETE / TRUNCATE / DROP 的区别，以及生产环境为什么流行软删除

为什么要学

前 5 课全是只读，这一课开始动数据。后端的写接口——注册建用户、下单扣库存、删除草稿——最终都是一条 DML。DML 的特点是不可逆倾向：SELECT 写错大不了重查，UPDATE 少个 WHERE 就是全表事故。所以这一课一半在教语法，另一半在教「改数据的纪律」，这些纪律在面试和事故复盘里反复出现。

先建立图像

写操作的三种粒度：

INSERT：往表尾追加行          （行级，安全，自增 id 顺序分配）
UPDATE：原地修改匹配的行       （改多少行 = WHERE 匹配多少行，忘 WHERE = 全表）
DELETE：删除匹配的行          （同上，且可能沿外键 CASCADE 连锁删除）
TRUNCATE：整表清空            （表级，秒级，不可逐行回滚）
DROP：表都没了                （结构+数据全毁）

核心心智模型：UPDATE / DELETE 的影响范围 = WHERE 条件选中的行。写之前先用同一条 WHERE 跑 SELECT，看看会命中谁。

跟着做

本课会真正改动数据。玩坏了没关系：回第一课整段重跑建库脚本即可复原。以下语句在 sql_practice 执行。

步骤 1：INSERT 指定列 + 批量

INSERT INTO student (name, email, city, vip, balance)
VALUES ('测试一号', 'test1@example.com', '深圳', 0, 0.00);

SELECT id, name, email FROM student WHERE email = 'test1@example.com';
-- id = 8：没写 id，自增主键自动分配

INSERT INTO student (name, email, city, vip, balance)
VALUES ('测试二号', 'test2@example.com', '杭州', 0, 10.00),
       ('测试三号', 'test3@example.com', '北京', 1, 66.00);

习惯从第一天养好：永远写列名清单，不依赖表的列顺序。表将来加列，这种 INSERT 一行不用改。

步骤 2：INSERT … SELECT 批量搬运

给小吴（id=7）生成「SQL 与关系型数据库」全部已发布课时的学习记录（尚未开始）：

INSERT INTO study_record (student_id, lesson_id, completed)
SELECT 7, l.id, 0
FROM lesson l
WHERE l.course_id = 3
  AND l.status = 'published'
  AND NOT EXISTS (
      SELECT 1 FROM study_record r
      WHERE r.student_id = 7 AND r.lesson_id = l.id
  );
-- 插入 3 行（course 3 的已发布课时 6、7、8）

NOT EXISTS 用来跳过已有记录——本表有唯一键 uk_student_lesson(student_id, lesson_id)，重复插入会报 1062。批量初始化、老数据迁移，都是这个句式。

步骤 3：UPDATE 带 WHERE，先 SELECT 后 UPDATE

-- 第一步：用将要 UPDATE 的 WHERE 先查一遍，确认命中谁
SELECT id, name, balance FROM student WHERE id = 5;   -- 小夏 88.00

-- 第二步：再改
UPDATE student SET balance = balance + 100 WHERE id = 5;

SELECT balance FROM student WHERE id = 5;             -- 188.00

一次改多个列：

UPDATE course
SET price = 199.00, level = 'intermediate'
WHERE slug = 'redis-intro';

步骤 4：亲眼看一次「没有 WHERE 的 UPDATE」（用事务兜底）

START TRANSACTION;
UPDATE student SET balance = 0;                       -- 糟糕，忘写 WHERE
SELECT COUNT(*) AS zero_cnt FROM student WHERE balance = 0;  -- 10，全军覆没
ROLLBACK;                                             -- 撤销，数据回来了
SELECT COUNT(*) AS zero_cnt FROM student WHERE balance = 0;  -- 1（大鹏本来就是 0）

这条 ROLLBACK 就是写操作的安全网：改数据前 START TRANSACTION，验证结果对了再 COMMIT，错了 ROLLBACK。生产环境手写 UPDATE 前必做。

步骤 5：DELETE 与级联

DELETE FROM lesson WHERE status = 'draft';
-- 影响 3 行（id 3、9、14）。它们没有学习记录引用，无级联发生。
SELECT COUNT(*) FROM study_record;   -- 仍是 20

级联是什么？第一课建表时写了 ON DELETE CASCADE。验证一下：

INSERT INTO course (id, slug, title, category, level, price, status)
VALUES (99, 'tmp-course', '临时课', 'java', 'beginner', 0, 'published');
INSERT INTO lesson (course_id, title, order_index, status)
VALUES (99, '临时课时', 1, 'published');
SELECT COUNT(*) FROM lesson WHERE course_id = 99;  -- 1

DELETE FROM course WHERE id = 99;
SELECT COUNT(*) FROM lesson WHERE course_id = 99;  -- 0：课时被级联删除

删课程会顺着外键删掉它的课时，再顺着课时删掉学习记录。CASCADE 是双刃剑，DELETE 前必须想清楚连锁范围。

步骤 6：TRUNCATE vs DELETE vs DROP

CREATE TABLE pay_order_bak AS SELECT * FROM pay_order;  -- 复制一张备份表
TRUNCATE TABLE pay_order_bak;                            -- 清空：秒级
SELECT COUNT(*) FROM pay_order_bak;                      -- 0
DROP TABLE pay_order_bak;                                -- 表整个删掉
SHOW TABLES;                                             -- bak 不在了

维度	DELETE	TRUNCATE	DROP
粒度	按行，可带 WHERE	整表	整表+结构
速度	逐行删，慢	极快	快
事务回滚	可以	隐式提交，不可	不可
自增值	保留	重置回 1	—
触发器	触发	不触发	—

步骤 7：写操作安全清单（做成习惯）

1. UPDATE / DELETE 前，用同样的 WHERE 跑 SELECT 确认影响范围
2. 事务包裹：START TRANSACTION → 执行 → SELECT 复核 → COMMIT，不对就 ROLLBACK
3. MySQL Workbench 报错 1175 是 safe updates 模式在拦你没带主键条件的写语句；练习时可 SET SQL_SAFE_UPDATES = 0;，生产保持开启
4. 重要数据用软删除：加 status 或 deleted_at 列做标记，本站 cf_lesson.status 的 draft/published 就是这个思路
5. 大表清理不硬删：分批 DELETE … WHERE id BETWEEN … LIMIT 1000 循环，避免长事务和大 undo

讲清楚要点

1. 忘 WHERE 的 UPDATE/DELETE 是全表操作，这是数据库事故榜第一名。防线上手滑：账号权限分层（业务账号只给业务库）、safe updates、上线走工单。
2. INSERT 永远写列名清单：防表结构变更炸语句；自增 id 不写，交给数据库分配。
3. INSERT … SELECT 是批量神器：初始化、迁移、报表落表；配合 NOT EXISTS（或 INSERT IGNORE、ON DUPLICATE KEY UPDATE）处理唯一键冲突，三种策略语义不同，按业务选。
4. DELETE / TRUNCATE / DROP 按破坏力递增：能 DELETE 就别 TRUNCATE，能 TRUNCATE 就别 DROP；DDL 隐式提交不可回滚。
5. 外键 CASCADE 会让 DELETE 连锁：删父表行 = 删子表行。本站 schema 里 cf_lesson 挂 cf_track 的 CASCADE，删课程会连课时一起没——这是设计决策，不是意外。
6. 软删除是互联网惯例：物理删除不可恢复、外键引用会断；加标记列，查询侧统一过滤 WHERE deleted_at IS NULL。代价是所有查询都要记得带条件，索引设计也要照顾它。

你可能会懵的地方

• UPDATE 返回「0 rows affected」是失败吗？ 不一定：要么 WHERE 没匹配到行，要么匹配到了但值没变化（MySQL 默认返回 changed rows）。想按「匹配行数」判断要加连接参数 useAffectedRows=false。逻辑上先用 SELECT 验证。

• 能一条 UPDATE 改多张表吗？ 能（UPDATE a JOIN b ON … SET …），但可读性差、容易误伤，建议拆成事务里的多条单表语句。

• 误删了还能找回来吗？ 事务未提交：ROLLBACK。已提交：靠备份 + binlog 恢复（DBA 的活，代价大）。所以防永远大于救：事务、WHERE、权限、备份，四件套一个不能少。

• INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 是什么？ 都是撞唯一键时的策略：IGNORE 静默跳过冲突行；ODKU 把冲突行变成更新。注意 IGNORE 也会吞掉其他一些警告，别滥用。

• 为什么自增 id 会「跳号」？ 批量插入回滚、并发分配、InnoDB 重启后取最大值等原因都会造成空洞。自增只保证唯一不保证连续，业务别依赖连续性。

错法对照

错法	问题	正解
UPDATE/DELETE 无 WHERE	全表被改/删	永远带 WHERE，先 SELECT 验证
不开事务直接改生产数据	错了无法回滚	BEGIN → 复核 → COMMIT
INSERT INTO t VALUES (…) 不写列名	表加列就炸	写列名清单
DELETE 清大表	慢、长事务、binlog 巨大	分批删或 TRUNCATE（可清空时）
物理删除核心业务数据	不可追溯、引用断裂	软删除标记
撞唯一键硬插	1062 报错	NOT EXISTS / INSERT IGNORE / ODKU

卡点急救

1. 错误 1175（safe update）：Workbench 默认拦「无主键条件的 UPDATE/DELETE」。带 WHERE 主键 = … 即可；实在要全表操作，SET SQL_SAFE_UPDATES = 0; 后谨慎执行。
2. 错误 1062（Duplicate entry）：撞唯一键。先查重复数据，按业务选 NOT EXISTS / IGNORE / ODKU。
3. 错误 1452（外键约束）：插入的子表外键值在父表不存在。先插父表（如先有 course 才有 lesson）。
4. 改错了：事务内 ROLLBACK；已提交则回第一课重跑脚本复原（练习库），生产走备份恢复流程。

面试常问

• DELETE、TRUNCATE、DROP 的区别？ DELETE 按行、可带 WHERE、可回滚、触发器生效；TRUNCATE 清空整表、快、重置自增、隐式提交不可回滚；DROP 连表结构一起删。破坏力递增，权限也应递减。

• 线上误执行了不带 WHERE 的 UPDATE，怎么办？ 事务里且未提交 → ROLLBACK；已提交 → 立即评估影响面，用备份 + binlog 做 PITR 恢复，同时止损（下线接口、切流）。复盘补防护：safe updates、权限、审批。

• 软删除和硬删除怎么选？ 核心业务数据（用户、订单）软删除，可追溯、可恢复，查询统一过滤；日志、临时表硬删除。软删除要配套唯一索引设计（删除后释放唯一值，如 deleted_at 参与联合唯一键）。

• 大表删除大量数据怎么做？ 分批 + 主键游标循环删，控制每批行数和间隔，避免长事务、主从延迟；能 TRUNCATE/换表重命名（保留近期数据）就别逐行删。

自测三问

1. 一条 UPDATE 从写完到放心 COMMIT，中间应该做哪几步？
2. 删除一门课程时，它的课时和学习记录会怎样？由什么机制决定？
3. 为什么核心业务表推荐软删除？查询侧要付出什么代价？

小结一句话

DML 的语法只有三句，纪律却有一整套：写列名、带 WHERE、先 SELECT 验证、事务兜底、CASCADE 想清楚、核心数据软删除。

延伸

• MySQL INSERT / UPDATE / DELETE 语法
• 下一课：表设计与主键外键——回过头看这些表为什么长这样。