2.2 UPDATE 与 DELETE：改数据之前先想清楚

2.2 UPDATE 与 DELETE：改数据之前先想清楚

本节摘要：UPDATE 与 DELETE 的语法五分钟就能学会，但安全使用它们是一辈子的功课。本节给出"先查后改"的完整流程、UPDATE 的表达式技巧、DELETE/TRUNCATE/DROP 三档删除的差别，以及误操作后用事务自救的实操——这最后一招会在第 9 章展开成完整的事务理论。

一次误删事故的复盘

先看一段真实世界里反复上演的剧本。某周五下午，运营提需求："把 ID 是 107 的订单状态改成已取消。"执行者在客户端里敲下语句，回车，回执亮了：

UPDATE orders SET status = '已取消';
​

Query OK, 4213 rows affected (0.35 sec)
​

4213 行——全表的订单都被改成"已取消"了。他漏写了 WHERE id = 107，而数据库严格服从了他写的语句，而不是他心里想的那句。这类事故的共同点：语句没有错，错的是它精确执行了一个没有限定的意图。防范手段不是"下次小心"，而是把流程设计得让仓促的人也安全：先查后改。把 WHERE 条件先放进 SELECT 里跑一遍，肉眼确认命中的行，再把 SELECT ... 部分替换成 UPDATE/DELETE：

-- 第一步：用 SELECT 预演，确认 WHERE 命中的就是目标行
SELECT id, status FROM orders WHERE id = 107;
​

+-----+-----------+
| id  | status    |
+-----+-----------+
| 107 | 已支付    |
+-----+-----------+
1 row in set (0.00 sec)      -- 只有一行，放心动手
​

-- 第二步：确认无误，把 SELECT 换成 UPDATE，WHERE 原封不动抄过去
UPDATE orders SET status = '已取消' WHERE id = 107;
​

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​

Rows matched 与 Changed 两行回执也值得养成阅读习惯：matched 是 WHERE 命中的行数，changed 是实际发生变化的行数。若更新后值恰好没变，matched 照样计数而 changed 为 0——看到 matched: 4213 的那一刻，就该意识到 WHERE 又丢了。

UPDATE 的三个进阶用法

表达式更新：SET 右边不必是常量，可以是列自身的表达式。全场图书降价 10%：

-- 价格与库存同时按表达式更新
UPDATE books
SET price = ROUND(price * 0.9, 2),      -- 九折，保留两位小数
    stock = LEAST(stock + 5, 50)        -- 补 5 本库存，封顶 50
WHERE category_id = 2;
​

Query OK, 2 rows affected (0.02 sec)
​

条件更新：SET 的值也可以按行变化，CASE 表达式（第 3 章详解）在这里先露一面：

-- 按库存档位差异化补货：低于 10 补 20，低于 20 补 10，其余补 5
UPDATE books
SET stock = stock + CASE
         WHEN stock < 10 THEN 20
         WHEN stock < 20 THEN 10
         ELSE 5
       END;
​

Query OK, 4 rows affected (0.03 sec)
​

一条语句完成分档处理，比写三条 UPDATE 更快，也避免了三条语句执行到一半被中断导致的状态混杂。

多表关联更新：按另一张表的信息更新本表，比如把"数据库"分类下所有书的价格上调 5 元：

-- MySQL 多表更新：用 JOIN 带来 categories 的信息
UPDATE books b
JOIN categories c ON b.category_id = c.id
SET b.price = b.price + 5
WHERE c.name = '数据库';
​

Query OK, 2 rows affected (0.01 sec)
​

JOIN 语法第 5 章才正式讲，这里先记住形态：UPDATE 也能挂 JOIN，"按别的表的条件改这张表"在生产中极为常见。

删除的三个档位：DELETE、TRUNCATE、DROP

结论很好记：删部分行用 DELETE；清空大表且确认不要回滚用 TRUNCATE；连结构一起不要了才用 DROP。DELETE 演示：

-- 删除库存为 0 且 2015 年前出版的书（两个条件缺一不可）
DELETE FROM books
WHERE stock = 0 AND published_at < '2015-01-01';
​

Query OK, 0 rows affected (0.00 sec)
​

当前数据里没有书同时满足两条件，删除 0 行。这个"0 行"同样值得看：它说明 WHERE 过严还是数据本来就没有，动手前的那次 SELECT 预演能提前回答这个问题。

图 2-2 写操作安全流程卡

误操作自救：事务的第一次实战

上一节的表格里提到"DELETE 在事务内可回滚"，现在实操一次。秘密在于：DML 的改动默认"自动提交"（每条语句自成一个事务，执行即落盘）；而显式开启事务后，改动先挂起，COMMIT 才落盘、ROLLBACK 则全部作废：

-- 自救演练：在事务里做一次"事故"，再整体撤销
START TRANSACTION;                      -- 开启事务，自动提交暂停

UPDATE books SET price = 0.00;          -- "事故"：忘了 WHERE
SELECT title, price FROM books LIMIT 3; -- 灾情确认
​

Query OK, 4 rows affected (0.02 sec)

+-----------------------+---------+
| title                 | price   |
+-----------------------+---------+
| 数据库系统概念        |    0.00 |
| SQL必知必会           |    0.00 |
| 算法导论              |         |
+-----------------------+---------+
​

ROLLBACK;                               -- 全部撤销，价格原样归来

SELECT title, price FROM books LIMIT 2;
​

+-----------------------+---------+
| title                 | price   |
+-----------------------+---------+
| 数据库系统概念        |   89.00 |
| SQL必知必会           |    52.00 |
+-----------------------+---------+
Rollback 之后查询，价格完好如初
​

三个注意点。第一，窗口有限：ROLLBACK 只能撤销当前事务内的改动，一旦 COMMIT（或断开连接触发隐式提交），后悔药过期。第二，DDL 是隐形杀手：事务里执行任何 CREATE/ALTER/DROP，多数数据库会隐式提交之前的改动，事务保护瞬间失效。第三，TRUNCATE 在 MySQL 里同样绕过事务，别指望它能回滚。

⚠️ 常见坑：在图形客户端里关掉事务自动提交选项后忘了恢复，之后每条语句都悬而未提交，连接一断全部蒸发，还以为数据库"丢数据"了。会话级参数改过什么，用完就改回去。

💡 关键直觉：把 UPDATE 与 DELETE 读成"下达命令"，把 SELECT 预演读成"沙盘推演"。职业军人和莽夫的差别不在会不会开枪，而在开枪前有没有推演。

本节要点回顾

• 先查后改：WHERE 条件先在 SELECT 里跑通，再原样搬进 UPDATE/DELETE，行数回执必须核对；
• matched 与 changed 分开读：前者是命中数，后者是实际变化数，异常大数字是最高危信号；
• SET 支持表达式、CASE 与多表 JOIN：一条语句做分档更新比多条更安全也更高效；
• 删除三档：DELETE 删行可回滚、TRUNCATE 清表快而不入事务、DROP 连结构一起没；
• 事务是时间有限的后悔药：COMMIT 与 DDL 都会关上窗口，平时备份才是真正的底牌。

数据已经能进能出能改能删，练习场齐活。下一章回到主线：在单表之内把表达力补齐——表达式、函数、NULL 与 CASE，查询阶梯的第三级。