MySQL UPDATE 条件升级导致的事故

UPDATE 语句看起来简单，但稍不注意就可能把整张表给毁了。这篇说说 UPDATE 的常见事故和正确写法。

经典事故：忘了加 WHERE

-- 本意：给 id=1 的用户加 100 积分UPDATEuserSETpoints=points+100WHEREid=1;-- 事故：忘了加 WHERE！UPDATEuserSETpoints=points+100;-- 全表所有用户都加 100 积分！

结果：积分全部加 100，如果用户量 100万，后果不堪设想。

怎么避免？

1. 先写 SELECT 验证

-- 先查一下，确认数据范围SELECT*FROMuserWHEREid=1;-- 确认只有一条SELECTCOUNT(*)FROMuserWHEREpoints>0;-- 确认范围-- 再执行 UPDATEUPDATEuserSETpoints=points+100WHEREid=1;

2. 用事务包裹

-- 开启事务，先看结果，确认没问题再提交STARTTRANSACTION;UPDATEuserSETpoints=points+100WHEREid=1;-- 检查一下SELECT*FROMuserWHEREid=1;-- 确认无误，提交COMMIT;-- 或者回滚-- ROLLBACK;

3. 用 LIMIT 限制

-- 加 LIMIT 限制，即使忘加 WHERE 也只影响一条UPDATEuserSETpoints=points+100WHEREid=1LIMIT1;

4. 加上 ORDER BY + LIMIT

-- 更新排序后的前几条UPDATEuserSETpoints=points+100WHEREstatus='vip'ORDERBYcreated_atDESCLIMIT100;

常见错误写法

1. 多表 UPDATE 忘了关联条件

-- 本意：给 Tom 的订单加 100UPDATEordero,useruSETo.amount=o.amount+100WHEREu.name='Tom'ANDo.user_id=u.id;-- 事故：没有关联条件，order 表全部更新！UPDATEordero,useruSETo.amount=o.amount+100WHEREu.name='Tom';-- 所有订单都加 100！

正确写法：

-- 方式1：用 JOINUPDATEorderoINNERJOINuseruONo.user_id=u.idSETo.amount=o.amount+100WHEREu.name='Tom';-- 方式2：用子查询UPDATEorderSETamount=amount+100WHEREuser_id=(SELECTidFROMuserWHEREname='Tom');

2. 字符串更新漏了引号

-- 正确：字符串加引号UPDATEuserSETname='Tom'WHEREid=1;-- 错误：没加引号，数据被截断UPDATEuserSETname=TomWHEREid=1;-- 报错或数据错误

3. 负数没处理

-- 本意：扣 100 积分，最低扣到 0UPDATEuserSETpoints=points-100WHEREid=1;-- 事故：如果原来只有 50 积分，结果变成 -50！-- 正确：IFNULL + GREATESTUPDATEuserSETpoints=GREATEST(points-100,0)WHEREid=1;

4. 浮点数精度问题

-- 金额计算用 DECIMAL，别用 FLOATUPDATEaccountSETbalance=balance-0.1WHEREid=1;-- FLOAT 有精度问题UPDATEaccountSETbalance=balance-DECIMAL('0.1')WHEREid=1;-- DECIMAL

批量 UPDATE 的正确姿势

1. 分批更新

-- 更新 10000 条数据，每批 500 条UPDATEuserSETpoints=points+10WHEREstatus='vip'LIMIT500;-- 执行 20 次

2. 用主键 IN

-- 已知 ID 列表，用 INUPDATEuserSETpoints=points+10WHEREidIN(1,2,3,4,5);

3. 临时表 + JOIN

-- 创建临时表存要更新的 IDCREATETEMPORARYTABLEtmp_ids(idBIGINT);INSERTINTOtmp_idsVALUES(1),(2),(3);-- JOIN 更新UPDATEuseruINNERJOINtmp_ids tONu.id=t.idSETu.points=u.points+10;DROPTEMPORARYTABLEtmp_ids;

怎么恢复误更新？

1. 有备份

-- 从备份恢复mysql-u root-pdatabase<backup.sql

2. 用 binlog 恢复

-- 查看 binlog，找误更新的时间点SHOWBINLOG EVENTSIN'mysql-bin.000001'FROM123456;-- 用 mysqlbinlog 解析mysqlbinlog--start-datetime='2024-01-15 10:00:00' /var/lib/mysql/mysql-bin.000001

3. 反向补偿

-- 如果误加了 100 积分，扣回来UPDATEuserSETpoints=points-100WHEREid=1;-- 补偿-- 如果误删了数据，从其他表恢复或从备份恢复

最佳实践

UPDATE 操作的黄金法则：先 SELECT，后 UPDATE；加 LIMIT，不忘 WHERE。

相关阅读：

• [MySQL 事务隔离级别详解]
• • [MySQL 锁机制完全指南]
• • [MySQL 批量更新最佳实践]