一、骨架：改哪张表、改成什么、改哪些行

UPDATE 的语法就三行，正好对应三件事。

UPDATE tb_goods
   SET price = price * 1.1, updated_at = NOW()
 WHERE cat_id = 2;

第一行说改哪张表，第二行说改成什么，第三行说改哪些行。 三条里只有第三行可以不写——而它就是事故的入口。 SET 那行可以改多列，用逗号隔开。 多列同时改有它的意义：它们在同一条语句里完成，要么都改要么都不改。 如果拆成两条 UPDATE，中间失败就会出现「一半改了」的脏状态。

二、WHERE 缺了会怎样

不写 WHERE 的 UPDATE 不是语法错，它会把整张表的每一行都改掉。 而且它不问你、不提示、不报错。

两行的耗时只差一倍不到，因为瓶颈都在「改多少行」。 这就是它危险的地方：从执行结果上看不出区别，只有影响行数不同。 而很多人根本不看影响行数。 客户端打一行 Query OK, 10000 rows affected，扫一眼就过去了。 所以有一个很实用的习惯：改数据之前，先把 UPDATE ... SET 换成 SELECT *，条件一个字不改。 先看选中了哪些行，确认了再改。

三、用表达式改：每一行拿自己的旧值算

SET price = price * 1.1 这句话里有两个 price，它们不是一个东西。 左边是「要写到的新值」，右边是「这一行原来的值」。 因为 UPDATE 是逐行执行的，每一行都拿自己的旧值算新值。 所以一百个商品各自涨 10%，而不是全变成同一个数。 这一点决定了「改成平均值」不能这么写：

-- 错的：报错，聚合函数不能出现在 SET 里
UPDATE tb_goods SET price = AVG(price);

-- 对的：先用子查询算出平均值
UPDATE tb_goods SET price = (SELECT AVG(price) FROM tb_goods);

子查询那种写法还有个坑：SELECT 里查的就是正在被改的这张表。 MySQL 会报 You can't specify target table for update in FROM clause。 绕法是套一层临时表，或者先算出来存到变量里。

四、Rows matched 和 Changed 不是一回事

这是很多人栽过的一个坑：UPDATE 执行成功了，可它说「影响了 0 行」。 不是没执行，是「值本来就等于新值，所以不用改」。

MySQL 客户端会把两个数一起打出来：Rows matched: 10000 Changed: 0。 只看 Changed 会以为语句没生效，然后去查半天权限、连接、锁。 要让它无论如何都算「改了」，可以顺手加一句 SET updated_at = NOW()。 时间戳每次都变，Changed 就永远等于 matched。 这一条还有一个副作用要知道：Changed 少，说明这次 UPDATE 几乎没有产生新版本。 在有索引的列上，这可能意味着「索引也不用更新」——所以它快是应该的，不是坏了。

五、用事务自保：四步流程

UPDATE 是 DML，能回滚——前提是你还没提交。 这一条决定了线上改数据的标准动作。

BEGIN;
UPDATE tb_keep SET price = 0;      -- 假装忘了写 WHERE
SELECT SUM(price) FROM tb_keep;    -- 看一眼，不对
ROLLBACK;                          -- 退回去

上面四行真跑出来的数是 5050 变成 0、又变回 5050。 关键是第三行：改完先别提交，先回头看一眼结果对不对。 所以线上改数据的标准动作是这四步： BEGIN → UPDATE → 看影响行数 → 对了才 COMMIT。 很多客户端默认是自动提交模式，这四步不存在。 UPDATE 一执行就落盘，那时候只能靠 binlog 或备份——而 binlog 恢复是另一门手艺。 所以第一条建议不是「小心点」，是「先确认你的客户端是不是自动提交」。 命令行客户端默认自动提交，图形工具通常有个开关，看一眼再动手。

六、分批改大表

整批改和分批改，耗时未必差，但「事务有多长」差很多。

第二行反而慢了几倍，因为每批都要单独提交。 所以分批不是为了快，是为了「不把别人堵住」。 一个大事务会长时间持有行锁、写满 undo log，期间别人改同一张表就得排队。 更糟的情况是撞上 innodb_log_file_size 上限，事务直接失败。 所以大表改数据按「一万到十万行一批」切，是稳妥做法。 分批的写法有两种：按主键区间（WHERE id > 0 AND id <= 10000）， 或者按 LIMIT（MySQL 支持 UPDATE ... LIMIT 10000）。

七、动手 Demo：UPDATE：两个影响行数，和一句忘写的 WHERE

这一课真跑一次「忘写 WHERE」，再用事务把自己拦住。 同时把 Rows matched 和 Changed 这两个数摆清楚。 注意沙箱不区分这两个数，所以那张表里有两列，一列是沙箱报的、一列是 MySQL 报的。

# 一、带 WHERE 与不带 WHERE
c.execute("UPDATE tb_goods SET price = price WHERE cat = 2")
c.execute("UPDATE tb_goods SET price = price")            # 全表

# 二、表达式改：每一行拿自己的旧值算
c.execute("UPDATE tb_goods SET price = price * 1.1 WHERE cat = 2")

# 三、两个影响行数
base = c.total_changes
c.execute("UPDATE tb_goods SET price = price WHERE cat = 3")
matched = c.total_changes - base      # SQLite 只有这一个数

# 四、事务自保：改完先别提交
c.execute("BEGIN")
c.execute("UPDATE tb_keep SET price = 0")   # 忘了 WHERE
print(c.execute("SELECT SUM(price) FROM tb_keep").fetchone())   # 0.0
c.execute("ROLLBACK")

# 五、分批改 10 万行
for start in range(0, 100000, 10000):
    c.execute("UPDATE tb_big SET v = v + 1 WHERE id > ? AND id <= ?", (start, start + 10000))
    c.commit()

实跑输出：

--------------------------------------------------------------------
第三十四课：UPDATE 的两个数，和一句忘写的 WHERE
--------------------------------------------------------------------

  **教材 8.2 讲 `UPDATE`。语法只有一句，但它是四种 DML 里最容易出事故的一种。**
  **因为 `DELETE` 忘了 `WHERE` 还能靠备份救，`UPDATE` 忘了 `WHERE` 会把全表改成同一个值。**
  **这一课真跑一次「忘写 WHERE」，再演示怎么用事务把自己拦住。**

  一、骨架：改哪张表、改成什么、改哪些行
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | UPDATE tb_goods
  |    SET price = price * 1.1, updated_at = NOW()
  |  WHERE cat_id = 2;
  ④ 教材原码，本沙箱不执行

  **三行对应三件事，缺一不可。**
  **`WHERE` 那行是可以不写的，而它就是事故的入口。**
  **先造一万行数据，`cat` 分三类（1、2、3），价格 0 到 99。**
  **一共 10000 行。**

  二、带 WHERE 与不带 WHERE
  --------------------------------------------------------------------

  写法             改了哪些行           耗时
  -----------------------------------------------
  `WHERE cat = 2`  3334 行（三分之一）  0.0024 秒
  不带 `WHERE`     10000 行（全部）     0.0034 秒
  **两行都是「改」，但一行改三分之一，一行改全表。**
  **这里我把 `price = price` 写成「值没变」，所以数据不受影响——这是在演示里保护自己。**
  **真忘了 `WHERE` 而且写的是 `price = 0`，那一万行就全变 0 了。**
  **而且 `UPDATE` 是 DML，能回滚——前提是你还没提交。**

  三、改一个表达式：全体涨价 10%
  --------------------------------------------------------------------
  code   price
  ------------
  G00001 1
  G00004 4
  G00007 7
  3 rows in set
  code   price
  ------------
  G00001 1.1
  G00004 4.4
  G00007 7.7
  3 rows in set
  **两张表是同一批数据，改前改后。**
  **`SET price = price * 1.1` 里的 `price` 指的是「这一行原来的值」，不是某一行的值。**
  **这一点很重要：`UPDATE` 是按行执行的，每一行都拿自己的旧值算新值。**
  **所以「把所有价格改成平均值」不能写 `SET price = AVG(price)`——那要用子查询。**

  四、Rows matched 和 Changed 不是一回事
  --------------------------------------------------------------------

  指标          含义                 沙箱报的      MySQL 报的
  -----------------------------------------------------------
  Rows matched  符合 `WHERE` 的行数  3333          3333
  Changed       值真的变了的行数     3333（不分）  0
  **第三列是沙箱报的，第四列是 MySQL 报的，两者只在 `Changed` 那一行不同。**
  **SQLite 不区分这两个数，它把「符合条件」的都算成「影响」；MySQL 区分。**
  **这是很多人栽过的一个坑：`UPDATE` 执行成功了，可 MySQL 说 `Changed: 0`。**
  **不是没执行，是「值本来就等于新值，所以不用改」。**
  **真 MySQL 的客户端会打 `Rows matched: 10000  Changed: 0`，两个数摆在一起就好懂了。**
  **只看 `Changed` 会以为语句没生效，然后去查半天权限和连接。**
  **要让它无论如何都算「改了」，可以在 `UPDATE` 里加一句 `SET updated_at = NOW()`。**
  **时间戳每次都变，`Changed` 就永远等于 `matched`。**

  五、忘写 WHERE，用事务把自己拦住
  --------------------------------------------------------------------

  时刻                   price 总和  说明
  ---------------------------------------------
  改之前                 5050.0      1 加到 100
  UPDATE 之后（未提交）  0.0         全变 0 了
  ROLLBACK 之后          5050.0      原样回来了
  **三行就是一套完整的自保流程：先看总和、再改、发现不对就回滚。**
  **关键是「改完先别提交」——只要没提交，一切都还来得及。**
  **所以线上改数据的标准动作是这四步：**
  **`BEGIN` → `UPDATE` → 看一眼影响行数对不对 → 对了才 `COMMIT`。**
  **自动提交模式下这四步不存在，`UPDATE` 一执行就落盘，那时候只能靠 binlog 或备份。**

  六、一次改一万行：分批还是整批
  --------------------------------------------------------------------

  写法                  10 万行耗时  事务大小
  ----------------------------------------------
  一条 UPDATE 改完      0.039 秒     一个大事务
  分 10 批，每批一万行  0.145 秒     10 个小事务
  **这一格分批反而慢了几倍，因为每批都要单独提交一次。**
  **所以分批的意义不在快，在「不把别人堵住」。**
  **一个大事务会长时间持锁、写满 undo log，期间别人改同一张表就得等。**
  **真 MySQL 里还有个硬指标：事务太大可能撞上 `innodb_log_file_size` 限制，直接报错。**
  **所以大表改数据按「一万到十万行一批」切，是稳妥做法。**

第一段两张表：带 WHERE 改 3334 行，不带改 10000 行。 我把 price = price 写成「值没变」，是在演示里保护自己——真忘了 WHERE 写的是 price = 0， 那一万行就全变 0 了。 第二段改前是 1、4、7，改后是 1.1、4.4、7.7。 每一行涨自己的 10%，不是全变成同一个数。 这就是「按行执行」的意思。 第三段那张表是这一课最该看懂的地方。 沙箱把「符合条件」的都算成影响，所以两行都是 3333；MySQL 会分开，Changed 那一行会给 0。 看到 0 不要以为没执行——它只是说「值没变，不用改」。 第四段三行数是 5050、0、5050。 中间那个 0 是「全表被改成 0」的样子，第三个 5050 是回滚之后。 这一套动作建议背下来，它是线上改数据的最低保护。 第五段两行是 0.038 秒对 0.142 秒——分批反而慢。 所以要清楚分批买的是什么：买的是「事务短」，不是「速度快」。

30 秒实验

1. 把事务那一段的 ROLLBACK 换成 COMMIT，再想想怎么恢复。只能靠备份或 binlog。 这就是「提交」这两个字的分量。
2. 给 UPDATE 加上 ORDER BY id DESC LIMIT 100（MySQL 才有），想想什么时候用得上。 「先改最近的一百条」这种场景。 沙箱没有这个语法，等价写法是子查询选 id。
3. 跑一次 UPDATE tb_goods SET price = (SELECT AVG(price) FROM tb_goods)，看报什么错。 在 MySQL 上会报「不能在 FROM 子句里指定正在更新的表」。 绕法是套一层 (SELECT AVG(price) FROM (SELECT price FROM tb_goods) t)。

常见误区

1. 「UPDATE 不写 WHERE 会报错提醒我。」 不会。它会把全表改掉，然后告诉你「成功」。
2. 「影响 0 行说明语句没执行。」 是执行了，但新值等于旧值，所以不用改。 看 Rows matched 才对。
3. 「UPDATE 出错了直接重新跑一遍就行。」 如果那条语句是 SET count = count + 1，重跑就多加了一次。 这类语句不幂等，重跑前先确认它有没有执行成功。
4. 「分批改比整批改快。」 通常更慢，因为提交次数变多。 分批买的是「事务短、不堵别人」，不是速度。

8.2 到这儿结束。下一篇 8.3 讲删除数据—— DELETE 和 TRUNCATE 的差别，比 UPDATE 和 INSERT 之间的差别大得多。

• ☐ （本篇完，下一篇 8.3 删除数据）

相关笔记

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