UPDATE 是什么
UPDATE 更新满足条件的行，SET 支持多列赋值，右侧表达式可以引用当前行的旧值。它是变更类语句里最容易造成生产事故的一种：漏写或写错 WHERE 会把整表刷成同一个值，这类操作通常无法靠备份逐行回退，只能依赖 binlog 或闪回工具。稳妥流程是三步：先用同一条件 SELECT COUNT(*) 估算影响行数，再把语句包进事务执行并核对影响行数，确认无误后提交；MySQL 还可以打开 sql_safe_updates 作为最后的护栏。
基本信息
| 语法 | UPDATE 表 SET 列 = 表达式 [, …] WHERE 条件 |
|---|---|
| 多列赋值 | 逗号分隔，右侧表达式可引用该行旧值 |
| 影响行数 | 由客户端返回；MySQL 默认只统计真正发生变化的行为 |
| 事务支持 | InnoDB 与 PostgreSQL 都可在事务内 ROLLBACK |
| MySQL 护栏 | SET SESSION sql_safe_updates = 1 拒绝无 WHERE 或未走索引的更新 |
| 平台说明 | MySQL 支持 UPDATE … JOIN 多表更新，PostgreSQL 用 FROM 子句 |
典型使用场景
- 状态流转：把一批订单从待支付改为已支付并写入支付时间
- 数据修正：统一改正城市字段的拼写错误
- 计数器增减：SET view_cnt = view_cnt + 1，利用行锁保证原子累加
- 批量改归属：把某账号下的数据迁移到另一个账号
- 补历史数据：给存量行回填新增列的值
- 数据清洗：SET name = TRIM(name) WHERE name <> TRIM(name)
示例
示例
UPDATE orders
SET status = 'paid', # 多列赋值
paid_at = NOW(), # 写入时间戳
amount = amount - discount # 右侧可引用旧值
WHERE id = 100 # 优先用主键精确定位
AND status = 'pending'; # 带状态条件，避免重复更新安全执行流程
安全执行流程
-- 第一步：用同一条件预估影响行数
SELECT COUNT(*) FROM orders WHERE created_at < '2023-01-01';
-- 第二步：事务内执行，核对返回的影响行数
START TRANSACTION;
UPDATE orders SET status = 'archived' WHERE created_at < '2023-01-01';
-- affected rows 与预期一致再提交，不一致就回滚
COMMIT; -- 异常时执行 ROLLBACK;
-- MySQL 护栏：开启后不带 WHERE 的更新直接被拒绝
SET SESSION sql_safe_updates = 1;风险与护栏
| 风险 | 后果 | 护栏 |
|---|---|---|
| 漏写 WHERE | 整表被刷成同一个值 | 先 SELECT COUNT(*) 预估，开 sql_safe_updates |
| WHERE 走不到索引 | 锁范围放大，阻塞并发写入 | EXPLAIN 确认走索引后再执行 |
| SET 子查询关联写错 | 所有行被更新为同一值 | 先在只读环境验证子查询结果 |
| 一次改百万行 | 长事务占 undo，主从延迟上升 | 按主键区间分批提交 |
| 并发读改写 | 丢失更新，计数不准 | 用 SET col = col + 1 原子写法或在事务内锁读 |
UPDATE 不带 WHERE 会更新全表。执行前把 WHERE 子句单独跑一遍 SELECT COUNT(*)，再包进事务确认，这是最低成本的事故拦截手段。
MySQL 的 sql_safe_updates 会拒绝未使用索引条件的 UPDATE 与 DELETE，在开发与运维客户端里常开；生产执行大批量变更前临时开启更稳。
分批更新的写法：每次加 LIMIT 并在循环里推进主键区间，例如 WHERE id > 上次最大 id AND id <= 上次最大 id + 5000 LIMIT 5000，避免长事务。
PostgreSQL 的多表更新用 UPDATE … SET … FROM 其他表 WHERE 关联条件，关联条件漏写会产生与笛卡尔积等价的放大更新。
批量更新实践
- 分批提交：每批几千行提交一次，兼顾速度与锁持有时间
- 更新前后各统计一次行数，确认影响范围与预期一致
- 给大表回填新列用分批 UPDATE，而不是一条语句改全表
- 读操作放事务外：不要在长事务里夹着 SELECT 一起持有锁
- 生产变更先在预发环境用等量数据验证执行计划与耗时
- 变更窗口避开业务高峰，并提前确认主从延迟的监控报警阈值
MySQL 的 affected rows 默认只统计真正发生变化的行为，重复执行同一条更新可能返回 0，这与部分数据库的口径不同，判断成功与否不要只看这个数字。