DML 操作

这一课你将能做到

1. 用多值 INSERT、INSERT ... SELECT 批量写入数据，并理解 upsert 的三种形态与取舍
2. 编写带 WHERE、先 SELECT 预览、必要时带 LIMIT 的安全 UPDATE / DELETE
3. 说出每条 DML 语句「影响行数」的语义（插入 1、更新 2、无变化 0），用返回值做幂等判断

为什么要学

生产事故的来源里，UPDATE 忘带 WHERE、批量 DELETE 不分批、upsert 用错导致重复数据常年霸榜。DML 的每一条语法都不难，难的是「写的时候就知道自己在动多少行」——这是初级和资深工程师在 SQL 上的第一个分水岭，也是面试问「如何安全地批量删除」想听的东西。

人话：查询读错了顶多重来，写错就是改生产数据。DML 三件套（INSERT/UPDATE/DELETE）的规矩只有一条：先 SELECT 看清楚要动哪些行，再动手。

先建立图像

DML 影响行数的语义（客户端返回的 Rows matched: x Changed: y）：

INSERT  成功 1 条          → 影响行数 1
UPDATE  匹配 5 条,值有变化 3 条 → matched 5 / changed 3
ON DUPLICATE KEY UPDATE
  ├─ 新插入        → 1
  ├─ 冲突且更新了  → 2   ← 用影响行数判断是否走更新分支
  └─ 冲突但值没变  → 0
DELETE  删除 3 条          → 影响行数 3

记住 2 的含义：冲突走了更新分支——很多幂等导入逻辑靠这个返回值判断。

跟着做

步骤 0：数据准备

若还没建表，先跑第 1 课的建库建表脚本；然后重置数据保证结果可核对：

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 随后原样执行第 1 课步骤 4 的四段 INSERT（6 用户、6 题、12 提交、9 练习记录）

下文所有「预期结果」都基于这份初始数据。

步骤 1：多值 INSERT

INSERT INTO cf_submission (user_id, problem_id, language, status, runtime_ms, submitted_at) VALUES
  (6, 1, 'java', 'accepted',     20, '2025-06-08 10:00:00'),
  (3, 2, 'java', 'wrong_answer', 35, '2025-06-08 10:05:00');

SELECT COUNT(*) FROM cf_submission;  -- 预期 14

一次网络往返写入多行，比循环单条 INSERT 快一个数量级。批量导入的推荐大小是几百到一千行/批。

步骤 2：INSERT ... SELECT

把 qian 对题目 1 的 AC 提交同步成一条练习记录：

INSERT INTO cf_practice_record (user_id, problem_id, is_correct, duration_sec, practiced_at)
SELECT s.user_id, s.problem_id, 1, 45, s.submitted_at
FROM cf_submission s
WHERE s.user_id = 6 AND s.status = 'accepted';

SELECT COUNT(*) FROM cf_practice_record;  -- 预期 10（原 9 + 1）

INSERT ... SELECT 是数据搬迁、数据修正、报表落表的标准姿势，SELECT 部分能跑出什么就写入什么。

步骤 3：安全 UPDATE——先看名单，再动手

目标：给 AC 数 ≥ 2 的活跃用户升一级。

-- 先 SELECT 预览要动的行
SELECT u.id, u.username, u.level,
       (SELECT COUNT(*) FROM cf_submission s
        WHERE s.user_id = u.id AND s.status = 'accepted') AS ac_cnt
FROM cf_user u
WHERE u.status = 'active';
-- 预期：lin(2)、zhao(2)、zhou(2) 满足；wang 1、qian 1、sun 是 banned

-- 再 UPDATE，用 LEAST 兜底防止 level 越过 5
UPDATE cf_user u
SET u.level = LEAST(u.level + 1, 5)
WHERE u.status = 'active'
  AND (SELECT COUNT(*) FROM cf_submission s
       WHERE s.user_id = u.id AND s.status = 'accepted') >= 2;

-- 预期影响 3 行：lin 3→4, zhou 4→5, zhao 保持 5（LEAST 兜住）

步骤 4：安全 DELETE——预览行数再删

删除刚才 qian 的测试提交：

-- 预览将删除的行
SELECT COUNT(*) FROM cf_submission
WHERE user_id = 6 AND submitted_at >= '2025-06-08';
-- 预期 1

-- 正式删除（严格模式安全更新开启时需带索引列或 LIMIT）
DELETE FROM cf_submission
WHERE user_id = 6 AND submitted_at >= '2025-06-08';

SELECT COUNT(*) FROM cf_submission;  -- 预期回到 13

大表删除的纪律：单条 DELETE 带上索引条件，一次只删几千行，循环执行——避免大事务、避免长时间锁。

步骤 5：upsert——ON DUPLICATE KEY UPDATE

-- 老用户：冲突后升级
INSERT INTO cf_user (username, email, level) VALUES ('lin', 'lin@example.com', 4) AS new
ON DUPLICATE KEY UPDATE level = LEAST(cf_user.level + 1, new.level);
-- 预期影响行数 2（走了更新分支）

-- 新用户：正常插入
INSERT INTO cf_user (username, email, level) VALUES ('liu', 'liu@example.com', 1) AS new
ON DUPLICATE KEY UPDATE level = new.level;
-- 预期影响行数 1

说明：AS new 行别名是 8.0.20+ 的推荐写法；旧写法 VALUES(level) 已标记废弃。只要命中任意一个唯一键（这里 username 或 email），就触发更新分支。

步骤 6：对比 REPLACE 与 INSERT IGNORE

-- INSERT IGNORE：冲突就静默跳过，影响行数 0，无报错
INSERT IGNORE INTO cf_user (username, email) VALUES ('lin2', 'lin@example.com');

-- REPLACE：先 DELETE 旧行再 INSERT 新行
REPLACE INTO cf_user (username, email, level) VALUES ('lin', 'lin@example.com', 3);
-- 影响行数 2；但注意：lin 的 id 变成了新的自增值！
SELECT id, username FROM cf_user WHERE username = 'lin';

REPLACE 把 lin 的行删掉重建，自增 id 从 1 变成新值——旧 id 在其他表里的引用全部悬空。这就是「能不用 REPLACE 就不用」的原因。

讲清楚要点

1. WHERE 是 DML 的安全带：UPDATE/DELETE 不带 WHERE 是全表操作，生产上等于事故。写完先 SELECT 用同样的 WHERE 预览，确认行数和内容再改。
2. 影响行数的三种值各有语义：ON DUPLICATE KEY UPDATE 的 1/2/0 分别对应插入/更新/无变化，幂等导入靠它判断是否新数据。
3. upsert 依赖唯一键：没有 UNIQUE/PK 约束，ON DUPLICATE KEY UPDATE 永远不会触发更新分支，只会越插越多——先确认唯一键存在再写 upsert。
4. VALUES() 已废弃：8.0.20 起推荐 INSERT ... AS new ON DUPLICATE KEY UPDATE col = new.col 别名写法，引用更直观且官方明确后续移除 VALUES()。
5. REPLACE 的本质是 DELETE+INSERT：副作用包括自增 id 变化、触发器触发 DELETE 分支、从库复制语义复杂。做「存在则更新」一律优先 ON DUPLICATE KEY UPDATE。
6. INSERT IGNORE 会吞错误：它不仅忽略主键/唯一键冲突，还会把某些数据类型转换错误降级成警告，掩盖脏数据。明确知道「冲突可跳过」时才用。

你可能会懵的地方

• UPDATE 返回影响行数 0，但 WHERE 明明匹配到了行？ 匹配到的行的值本来就没变。想让「匹配即计数」需要在连接串开启 CLIENT_FOUND_ROWS。判断业务是否生效时看 changed 而不是 matched。

• ERROR 1175 safe update mode 是什么？ 客户端开启安全更新模式后，不带主键/索引列条件的 UPDATE/DELETE 会被拒绝。这是保护机制，临时关闭：SET SQL_SAFE_UPDATES = 0;，写完改回来。

• 大批量 INSERT 太慢怎么办？ 多值合并成一条（几百行/批）、包在事务里（避免每行自动提交刷盘）、必要时 SET unique_checks=0, foreign_key_checks=0（仅限空表导入）。终极方案是 LOAD DATA。

• DELETE 了 1000 万行，磁盘空间怎么不降？ InnoDB 删除只是打标记，空间留在表空间里复用。要真正释放需 OPTIMIZE TABLE t（会锁表重建）或 ALTER TABLE t ENGINE=InnoDB 重建，评估后低峰执行。

• 一条语句能同时改多张表吗？ 可以，UPDATE a JOIN b ON ... SET a.x=..., b.y=...。但跨表 DML 更建议拆成多条放进一个事务——出错时能精确定位。

错法对照

错法	问题	正解
UPDATE t SET ... 忘 WHERE	全表被改，生产事故	固定流程：先 SELECT 预览，再带 WHERE 更新
用 REPLACE 做「存在则更新」	删行重建，id 漂移、引用悬空	ON DUPLICATE KEY UPDATE
INSERT IGNORE 处理所有冲突	类型错误被降级为警告，脏数据入库	明确冲突范围后再用 IGNORE
一次 DELETE 1000 万行	大事务、锁表、主从延迟	带索引条件分批删（每批几千行）
循环单条 INSERT	每行一次网络+刷盘	多值 INSERT 或 INSERT ... SELECT

卡点急救

1. ERROR 1175 (safe update)：按提示给 UPDATE/DELETE 加上主键条件，或 SET SQL_SAFE_UPDATES=0（会话级，用完关掉）。
2. ERROR 1062 Duplicate entry：唯一键冲突。是期望行为就用 upsert；不是就查数据来源，别盲目 IGNORE。
3. ERROR 1452 Cannot add or update a child row：外键约束——引用的 user_id 不存在。先插父表（用户）再插子表（提交）。

面试常问

• ON DUPLICATE KEY UPDATE 和 REPLACE 的区别？ 前者是原地 UPDATE，保留原行与自增 id；后者是 DELETE+INSERT，id 变化、触发 DELETE 触发器。需要保留行身份（被其他表引用）时只能用前者。

• 如何安全地删除一张大表里的 5000 万行数据？ 按 id 或时间分批（每批几千行）、每批一个事务、批间 sleep 让复制追上、DELETE 前先 SELECT 预览；有外键关联先处理引用；必要时先归档后删除。

• INSERT 影响行数 0/1/2 分别代表什么？ 仅对 ON DUPLICATE KEY UPDATE 有意义：1 新插入、2 冲突后更新、0 冲突但值未变。REPLACE 场景删除+插入合计 2。

自测三问

1. 写 upsert 前必须确认什么数据库对象？为什么？
2. 为什么「先 SELECT 再 UPDATE」是强制流程？在事务里又该如何做？
3. REPLACE 会导致什么连锁问题？举一个具体场景。

小结一句话

DML 的核心纪律是「影响行数可控」：先 SELECT 预览、WHERE 必带、批量分批、upsert 用 ON DUPLICATE KEY UPDATE 而不是 REPLACE。

延伸

• MySQL 8.0 Reference Manual — INSERT Statement
• MySQL 8.0 Reference Manual — UPDATE Statement
• 下一课：查询与聚合——把 SQL 的逻辑执行顺序彻底掰开，写对 GROUP BY 与 HAVING。