UPDATE

导学

数据不是一成不变的。员工调部门、分数更新、状态变更——这些都需要 UPDATE 语句来修改已有数据。本节从单字段更新开始，逐步讲解多字段更新、条件更新、关联更新以及更新中的常见陷阱。

定义

UPDATE：DML（数据操作语言）语句，用于修改表中已存在的数据行。UPDATE 必须配合 WHERE 条件使用（否则更新全表），可以一次修改多个字段。

核心语法

-- 单字段更新
UPDATE 表名 SET 列 = 新值 WHERE 条件;

-- 多字段更新
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

-- 使用表达式更新
UPDATE 表名 SET score = score + 10 WHERE 条件;

-- 关联更新
UPDATE 表1 t1 JOIN 表2 t2 ON t1.id = t2.id
SET t1.列 = t2.列 WHERE t2.条件;

演示数据准备

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表中的完整数据如下：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

SQL 示例

场景一：单字段更新

当前数据状态：见上文 employees 表完整数据。

执行语句：

UPDATE employees SET dept = '产品部' WHERE emp_id = 2;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100
2白歌产品部NULL

结果解读：WHERE emp_id = 2 精确匹配白歌，SET dept = '产品部' 将其部门从"技术部"改为"产品部"。score 等其他字段不受影响。返回受影响行数 1。

场景二：多字段同时更新

当前数据状态：见上文更新后的 employees 表。

执行语句：

UPDATE employees
SET dept = '运营部', score = 88
WHERE emp_id = 2;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100
2白歌运营部88

结果解读：一条 UPDATE 语句同时修改多个字段，用逗号分隔。白歌的 dept 变为"运营部"，score 从 NULL 更新为 88。注意：SET 中各列的赋值是同时进行的，不存在先后顺序导致的依赖问题。

场景三：使用表达式更新（累加/计算）

当前数据状态：见上文更新后的 employees 表。

执行语句：

UPDATE employees SET score = score + 5 WHERE emp_id = 1;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部105
2白歌运营部88

结果解读：SET score = score + 5 使用当前值参与计算。大翔原 score 为 100，更新后为 105。这种表达式更新在统计加分、积分累加等场景非常常见。

注意：如果 score 为 NULL，score + 5 的结果也是 NULL。如需处理 NULL，应使用 IFNULL(score, 0) + 5。

场景四：UPDATE 与 CASE WHEN 配合（条件分支更新）

当前数据状态：重新初始化数据：

UPDATE employees SET dept = '技术部', score = NULL WHERE emp_id = 2;
UPDATE employees SET score = 100 WHERE emp_id = 1;
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

执行语句：

UPDATE employees
SET score = CASE
    WHEN score IS NULL THEN 60
    WHEN score >= 90 THEN score + 5
    ELSE score
END
WHERE dept = '技术部';

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部105
2白歌技术部60

结果解读：CASE WHEN 实现单条 UPDATE 中的分支逻辑：

  • 白歌 score IS NULL → 赋值为 60（保底分）
  • 大翔 score >= 90 → 在原有 100 基础上加 5，变为 105
  • 其他情况保持不变

这比写多条 UPDATE 语句更高效（只需一次表扫描）。

场景五：关联更新（JOIN UPDATE）

当前数据状态：创建关联表：

CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    score DECIMAL(5,2)
);

INSERT INTO scores (emp_id, score) VALUES
(1, 95),
(2, 75);

employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

scores 表：

score_idemp_idscore
1195
2275

执行语句：

UPDATE employees e
INNER JOIN scores s ON e.emp_id = s.emp_id
SET e.score = s.score
WHERE s.score > 80;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部95
2白歌技术部NULL

结果解读：UPDATE ... JOIN ... SET 语法将 scores 表中的数据同步到 employees 表。只有 scores.score > 80 的大翔被更新（95 > 80），白歌的 scores.score = 75 不满足条件，因此 employees 表中白歌的 score 保持 NULL。

场景六：UPDATE 与 ORDER BY + LIMIT 配合（控制更新数量）

当前数据状态：重新初始化：

UPDATE employees SET score = NULL WHERE emp_id = 2;
emp_idemp_namedeptscore
1大翔技术部95
2白歌技术部NULL

执行语句：

UPDATE employees SET score = 70 WHERE score IS NULL ORDER BY emp_id LIMIT 1;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部95
2白歌技术部70

结果解读：ORDER BY emp_id LIMIT 1 控制只更新 1 条满足 score IS NULL 的记录。白歌的 emp_id = 2 更大，因此她被更新为 70。这是批量更新中控制影响范围的安全手段。

常见误区

误区正解
"UPDATE 不带 WHERE 是更新全表的快捷方式"生产环境绝对禁止不带 WHERE 的 UPDATE，会修改全表所有行。
"SET score = score + 5 中 score 用更新后的值"不会。SET 右侧的列引用都是更新前的值，不存在自增依赖问题。
"UPDATE 可以同时更新多张表"MySQL 支持 UPDATE t1 JOIN t2 SET t1.col = ...，但只能更新一张主表，其他表用于提供数据。
"UPDATE 后自增 ID 会变化"不会。UPDATE 不修改主键值（除非显式 SET 主键 = 新值，但通常不推荐）。
"UPDATE 和 DELETE 一样可以用 TRUNCATE 替代"不能。TRUNCATE 是清空全表，不能用于更新字段值。

面试考点

Q：UPDATE 执行时锁的范围？

UPDATE 会对满足 WHERE 条件的行加排他锁（X锁）。如果 WHERE 条件能走索引，只锁匹配行；如果全表扫描，可能升级为表锁或锁大量行。大数据量 UPDATE 应考虑分批执行，避免长时间锁表。

Q：UPDATE 中 SET 多个字段，赋值有先后顺序吗？

没有。MySQL 会先读取所有列的旧值，然后统一计算新值，最后写入。因此 SET a = b, b = a 可以实现两列交换值，而不会出现 a 先被覆盖导致 b 拿到新值的问题。

Q：如何安全地执行批量 UPDATE？

  1. 先 SELECT 验证 WHERE 条件匹配的行数和预期一致；2. 在事务中执行，确认无误后 COMMIT；3. 大数据量分批更新（配合 ORDER BY ... LIMIT n）；4. 避免在业务高峰期执行大范围更新。

Q：UPDATE 会触发触发器吗？

会。UPDATE 会触发 BEFORE UPDATE 和 AFTER UPDATE 触发器。如果触发器中又修改了同表数据，需注意避免递归或循环触发。

小结

  • UPDATE 用于修改已有数据行，生产环境必须带 WHERE
  • 支持多字段同时更新、表达式更新、CASE WHEN 分支更新
  • 支持关联更新（UPDATE ... JOIN ... SET）实现跨表数据同步
  • 支持 ORDER BY + LIMIT 控制更新数量，适合分批处理
  • 执行前务必用 SELECT 验证条件范围，避免误更新

下一章引子：INSERT 插入、UPDATE 修改、DELETE 删除——如果希望"存在则更新，不存在则插入"，用 REPLACE 语句可以一步到位。