17. 更新数据 UPDATE

本节目标：学会用 UPDATE 修改表中已有的数据，掌握单列更新、多列更新、带条件更新，以及跨表关联更新（UPDATE JOIN），同时养成安全更新的习惯，避免误改全表。

17.1 UPDATE 的基本语法

数据插进去了，后来发现填错了，怎么改？用 UPDATE 语句。

基本结构很简单：

UPDATE 表名
SET 列名 = 新值
WHERE 条件;

举个例子，把 id 为 1 的用户年龄改成 26：

UPDATE users
SET age = 26
WHERE id = 1;

执行后返回：

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

Rows matched：匹配到几行（满足 WHERE 条件的）
Changed：实际改了几行（如果新值跟旧值一样，匹配了但不算改变）

17.2 永远别忘了 WHERE

WHERE 是 UPDATE 里最重要的部分。忘了写它，整张表都会被更新：

-- 危险操作！没有 WHERE，所有用户的年龄都变成 26 了
UPDATE users
SET age = 26;

Warning

漏写 WHERE 是 SQL 操作中最常见的事故之一。我见过有人忘了加 WHERE，直接把几万条用户数据的密码全改成了一个值。养成习惯：写 UPDATE 时先写 WHERE，再写 SET。

MySQL 有个安全模式可以防这种事故。启动 mysql 客户端时加 --safe-updates 参数：

mysql -u root -p --safe-updates

在这个模式下，UPDATE 和 DELETE 必须带 WHERE 条件（且条件里要有索引列），否则会拒绝执行。

-- safe-updates 模式下，没有 WHERE 直接报错
UPDATE users SET age = 26;
-- ERROR 1175: You are using safe update mode

Tip

生产环境操作数据库时，建议始终开启 --safe-updates。虽然写 SQL 会多打几个字，但能防住大批量误操作。

17.3 更新多个列

SET 后面可以跟多个列，用逗号分隔：

UPDATE users
SET age = 27, email = 'zhangsan_new@example.com'
WHERE id = 1;

一条语句同时改年龄和邮箱。注意列之间用逗号，最后一个列后面不要加逗号。

你还可以用表达式来更新，不一定是固定值：

-- 所有用户的年龄加 1
UPDATE users
SET age = age + 1
WHERE id = 1;

-- 订单金额打九折
UPDATE orders
SET amount = amount * 0.9
WHERE id = 100;

17.4 用 CASE 实现条件更新

有时候你想根据不同条件更新成不同的值。比如订单状态：金额大于 500 的标记为”大额”，否则标记为”普通”。用 CASE 表达式一条语句搞定：

UPDATE orders
SET status = CASE
    WHEN amount > 500 THEN 'large'
    WHEN amount > 100 THEN 'normal'
    ELSE 'small'
END
WHERE status = 'pending';

这比写多条 UPDATE 语句高效得多，也避免了多次扫描表。

Note

CASE 表达式的逻辑跟编程语言里的 if-else 一样：从上往下匹配，命中一个 WHEN 就跳出。所有 WHEN 都不匹配时走 ELSE。别忘了写 END 收尾。

17.5 UPDATE JOIN：跨表更新

如果更新的值需要参考另一张表的数据怎么办？比如：根据用户表的年龄，给所有年龄大于 30 的用户的订单打上”vip”标记。

这就需要 UPDATE JOIN—在 UPDATE 里用 JOIN 关联另一张表：

UPDATE orders o
INNER JOIN users u ON o.user_id = u.id
SET o.status = 'vip'
WHERE u.age > 30;

拆解一下这个语句：

UPDATE orders o — 要更新的表是 orders，起别名 o
INNER JOIN users u ON o.user_id = u.id — 关联 users 表
SET o.status = 'vip' — 更新 orders 表的 status 列
WHERE u.age > 30 — 只更新关联用户年龄大于 30 的订单

再来看一个例子：把每个用户的邮箱更新为”用户名@example.com”的格式：

UPDATE users u
INNER JOIN orders o ON o.user_id = u.id
SET u.email = CONCAT(u.username, '@example.com')
WHERE o.status = 'paid';

Tip

UPDATE JOIN 中，SET 只能更新 UPDATE 后面那张表（以及 JOIN 的表）的列。要分清楚哪张表是要改的，哪张表只是来”帮忙”提供条件的。

LEFT JOIN 更新

如果用 LEFT JOIN，还能更新那些在关联表中没有匹配的行：

-- 给没有任何订单的用户改个标记
UPDATE users u
LEFT JOIN orders o ON o.user_id = u.id
SET u.email = CONCAT(u.username, '@inactive.com')
WHERE o.id IS NULL;

o.id IS NULL 表示左连接没匹配到，也就是这个用户没有订单。

17.6 更新操作的注意事项

更新会触发自增吗

不会。UPDATE 不会改变自增列（AUTO_INCREMENT）的值，也不会消耗自增计数器。只有 INSERT 才会。

更新会触发触发器吗

会。如果你在表上定义了 AFTER UPDATE 或 BEFORE UPDATE 触发器（后面章节会讲），每次 UPDATE 都会触发。批量更新时要注意触发器的执行开销。

LIMIT 限制更新行数

大批量更新时，可以用 LIMIT 限制一次更新的行数，分批处理：

-- 每次只更新 100 行
UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
LIMIT 100;

反复执行这条语句，直到 Rows matched 为 0，就全部更新完了。这种方式对大表特别有用，避免长时间锁表。

ORDER BY 配合 LIMIT

如果想知道更新的是哪几行，可以配合 ORDER BY：

-- 更新最早的 100 条待支付订单
UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
ORDER BY created_at ASC
LIMIT 100;

17.7 UPDATE 和 INSERT 的关系

如果你发现自己在写”先查有没有，有就 UPDATE，没有就 INSERT”的逻辑，其实可以用上一章学的 INSERT ... ON DUPLICATE KEY UPDATE 一条语句搞定。

-- 等价于"有就更新，没有就插入"
INSERT INTO users (id, username, email, age)
VALUES (1, '张三', 'zhangsan@example.com', 26)
ON DUPLICATE KEY UPDATE age = 26, email = 'zhangsan@example.com';

这种”upsert”（update or insert）模式在实际开发中非常常用，比写两步逻辑简洁得多。

17.8 小结

UPDATE 的核心就三点：SET 决定改成什么，WHERE 决定改哪些行，JOIN 让你能跨表参考数据来更新。最重要的一条铁律：永远先写 WHERE。养成这个习惯，能帮你避开 90% 的数据事故。下一章我们学怎么删数据。