整理SQL必会必知这段时间前面十几篇都在讲查询、连接、聚合这些“读”数据的技巧到了第18篇终于轮到“写”了——更新和删除数据。说实话这两个操作在初学者眼里就是两句话的事UPDATE改值DELETE删行。但真正动手之后才发现它是整个SQL里最容易出事故的地方。忘了写WHERE条件一条语句跑完整个表的数据说没就没生产环境里五分钟就能让所有人手忙脚乱。这篇文章专门聊聊更新和删除的完整套路从基础语法到防误操作机制从关联更新到分批处理最后附上我自己踩过的几个坑和自检清单希望能让正在学SQL的你少走点弯路。1. 更新和删除的基础语法与应用场景1.1 UPDATE 的标准写法与常见扩展先看最基本的UPDATE语句它的通用结构是UPDATE 表名 SET 列名1 新值1, 列名2 新值2, ... WHERE 过滤条件;这个结构非常简单但里面有几个细节容易被忽略。SET子句可以同时修改多个列列和列之间用逗号分隔这一点跟INSERT的VALUES不太一样。VALUES是按顺序给整行危险但UPDATE的SET是按名字指定列顺序无所谓。如果你只写了一个列名那其他列的值会原样保留不会自动清空。这里需要特别强调一句UPDATE语句里的WHERE和SELECT里的WHERE在逻辑上完全一致但后果完全不对等。做查询时写错WHERE最多就是把结果集查大了顶多眼睛看得累做更新时写错WHERE代价就是大量行被改写。所以我对UPDATE持有一种“先查后改”的习惯先把同样的WHERE条件放进SELECT里跑一遍确认选中的行数和内容符合预期再把它换成UPDATE去执行。这个习惯在下面的章节里还会反复提到。UPDATE的SET子句还有一个容易让人忽视的能力它可以引用当前行的其他字段参与计算。比如一个商品表里要统一把单价上调10%可以直接写成UPDATE products SET price price * 1.1 WHERE category_id 5;这里的price出现在等号右边代表的是每行更新前的旧值数据库会自动逐行计算而不是所有行都用同一个固定值。这个特性在库存扣减、计数器累加、状态翻转这些场景里都很有用。要注意的是如果同一行被某个更新后的值再引用执行顺序也可能会影响结果虽然大多数情况下没有歧义但是严谨的时候最好避免在一条UPDATE里对同一列做多次的自我引用。另一个容易踩的小坑是SET里用了另一个已被更新的列。例如UPDATE orders SET total_amount total_amount - discount, final_amount total_amount;如果数据库按照子句从左到右的顺序评估final_amount得到的是total_amount更新前的值还是更新后的值不同数据库的处理方式可能不一样某些方言会取旧值某些会取新值。既然存在这种方言差异我的建议是不要写这种“前后依赖”的赋值尽量把计算拆开或者直接用表达式UPDATE orders SET total_amount total_amount - discount, final_amount total_amount - discount;这样每一步的结果都是可预期的代码可读性也好得多。1.2 DELETE 与 TRUNCATE 的分工删除数据的标准语句是DELETE FROM 表名 WHERE 过滤条件;同样这里的WHERE是唯一的保险。如果漏掉它整张表的所有行都会被标记为删除这在数据量大的表上可能会触发大量日志写入和锁竞争甚至比“全表清空”还要慢。如果想要真的是“把所有行删掉并且立刻释放存储空间”可以考虑用TRUNCATE TABLE它跟DELETE有多大区别呢这里整理一个对比比较点DELETETRUNCATE本质逐行删除写入事务日志直接释放数据页几乎不逐行记日志是否可带WHERE可以精确删除部分行不可以只能全表清空是否可回滚事务内可以回滚多数数据库下不可回滚需谨慎自增主键不一定复位取决于实现通常会复位性能数据量大时慢可能锁大量行非常快触发器会触发行级触发器通常不触发行级触发器从这个对比能看出DELETE和TRUNCATE不是简单的等价位替换。如果你的目标是清理测试数据、重置临时表TRUNCATE往往更合适如果你要精确删除满足某些条件的数据或者希望操作能够被事务回滚保护那就必须使用DELETE。还有一点很多初学者会误以为TRUNCATE和DROP是一回事其实不是DROP是把表结构连同数据一起销毁TRUNCATE只是清空数据表结构、索引、列定义都还在。这三者之间最好先分清否则误用“删除”的操作真的很危险。2. 安全第一这几种保护机制必须在动手前到位2.1 事务边界是后悔药在支持事务的数据库里比如MySQL的InnoDB、PostgreSQL执行UPDATE和DELETE时最好先开启一个事务然后执行操作确认无误后再提交。用SQL写大概是这种感觉START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE account_no A001; DELETE FROM temp_orders WHERE created_at 2024-01-01; -- 检查结果 SELECT COUNT(*) FROM accounts WHERE account_no A001; SELECT COUNT(*) FROM temp_orders WHERE created_at 2024-01-01; COMMIT;如果检查时发现数据不对直接执行ROLLBACK那么刚才的更新和删除都会像没发生过一样。这个机制就是操作前的“后悔药”。可能有人觉得事务只有在金融转账这种场景下才用得上其实不是。你在任何一张线上表上做批量更新都有可能在几秒后发现WHERE条件写错了。只要没有提交就还有机会恢复。我见过很多次线上事故往往是操作者直接在命令行里执行一条UPDATE敲完回车才意识到漏了关键条件但语句已经开始跑数据库事务自动提交了数据瞬间被改得乱七八糟。所以基础习惯应该是在动手之前先START TRANSACTION;把“自动提交”关掉给自己留一个纠错的窗口。有些数据库客户端会默认把每条SQL放在一个隐式事务里自动提交这意味着即使你手动写了START TRANSACTION也要确认客户端没有开启“每条语句自动提交”的选项。实践里最稳妥的做法是在执行前设置会话变量关闭自动提交比如MySQL里的SET autocommit 0;。当然不同数据库的语法略有差别但思路是一致的。2.2 先SELECT验证目标行再UPDATE或DELETE事务虽然能回滚但回滚的前提是你在操作之后、提交之前发现问题。如果你根本不知道哪些行会被影响那即使有了后悔药也可能到时间来不及。所以更可靠的步骤是“先查后改”。具体操作模式是-- 第一步用相同的条件做SELECT SELECT id, name, status FROM users WHERE last_login 2023-01-01 AND status active; -- 第二步检查SELECT结果确认这确实是想更新的行 -- 第三步改写为UPDATE UPDATE users SET status inactive WHERE last_login 2023-01-01 AND status active;这个“改写”的过程非常简单把SELECT字段列表替换成SET语句其余WHERE条件原封不动拿过来就好。有条件的话还可以把SELECT出来的主键列表导出成一个临时表然后UPDATE通过WHERE id IN (SELECT id FROM temp_check)来锁定范围这样就不用在两个步骤之间反复比对条件是否一致。这个技巧在数据量不大、但条件很复杂的时候特别好用。2.3 备份策略不要只依赖回滚事务回滚和SELECT验证都是操作层面的保险但有一种情况它们都无能为力你执行了UPDATE之后又执行了COMMIT然后第二天才发现逻辑判断本身就是错的比如把“价格小于100”写成了“价格大于100”。这时候数据已经被永久覆盖回滚机制已经失效只能靠备份来恢复。所以真正重要的更新删除操作之前必须要有备份意识。不是说每次都要把整个库导出成文件而是可以针对要修改的表做一次轻量备份。最直接的备份就是在同一数据库里创建一个备份表例如CREATE TABLE users_bak_20250101 AS SELECT * FROM users;这条语句会按当前的数据快照新建一张备份表。如果后面出了意外你可以从这张备份表恢复UPDATE users u JOIN users_bak_20250101 b ON u.id b.id SET u.name b.name, u.status b.status;也可以直接TRUNCATE users再从备份表把数据重新INSERT回去。这种方法虽然笨但在很多场景下比“到处找binlog日志回滚”要快得多尤其是当问题出现得很晚、操作日志已经被覆盖的时候。我自己在测试环境里也养成了这种习惯凡是跑影响行数超过一千的UPDATE前都会先建一张带日期的备份表操作完成观察个把小时后没事了再删掉。3. WHERE子句才是整批操作的真实主人3.1 没有WHERE的全表操作如何发生更新和删除语句最经典的翻车现场就是漏写WHERE。有人可能会觉得那么多SQL教程都在强调这一点为什么还有人会犯其实很多时候不是故意漏写而是因为人在修改语句时把条件删掉了。比如你原本是想更新某个用户复制了一条语句UPDATE users SET email newexample.com WHERE id 100;为了改成批量更新你删除了WHERE id 100但可能只删了一半或者根本忘记了还有下一批人要更新直接执行了UPDATE users SET email newexample.com;这条语句会把所有用户的邮箱统统改成同一个值。如果在生产库上几秒内就能制造一场大型混乱。更隐蔽的情况是WHERE子句虽然在但因为运算符优先级、NULL值参与比较等原因实际筛选范围比你预想的大得多。例如DELETE FROM orders WHERE status cancelled OR amount 0;如果原意是“删除已取消且金额小于0的订单”那这里应该用AND而不是OR但写成OR就会把所有取消的订单都删除范围瞬间扩大。所以WHERE子句的写法绝对值得认真对待不能凭感觉。另外还要注意NULL的陷阱。SQL里用WHERE column x并不能匹配NULL因为NULL与任何值的比较结果都是UNKNOWN。如果你真的想“把不等于x的所有行包括NULL都更新”就要写成UPDATE table_name SET remark 非x WHERE column IS NULL OR column x;这种细节会在你处理大量数据时突然冒出来成为“坑”。因此执行前用同样的WHERE条件跑一次SELECT统计行数是避免范围泛滥的最廉价方案。3.2 复杂条件的构建示例实际项目中的更新删除条件远不止一个等值判断经常要混合多个条件。这里我在网上看到一个比较完整的例子清理超过90天未登录、且在VIP到期用户表中的不活跃账号可以用这样的DELETEDELETE FROM users WHERE last_login DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND user_type guest AND status locked AND is_test_user 0;这种写法的好处是逻辑清晰每个条件都是一行可读性强。特别提醒一下日期条件不要写成WHERE DATEDIFF(CURDATE(), last_login) 90这样的函数式写法虽然也能得出结果但索引很可能失效数据量大时性能会变得很差。尽量把函数放在常量一侧也就是last_login CURDATE() - INTERVAL 90 DAY这样索引更容易被用上。使用子查询来做条件过滤也属于常见操作。例如把订单金额超过一万的用户全部标记为重点客户UPDATE users SET is_important 1 WHERE id IN ( SELECT user_id FROM orders WHERE total_amount 10000 AND created_at 2024-01-01 );这种写法很直观但要注意在MySQL的老版本里不能直接在同一张表的UPDATE里同时从该表读取数据做子查询会报“You cant specify target table for update in FROM clause”的错误。解决办法是把子查询再包一层临时表比如UPDATE users SET is_important 1 WHERE id IN ( SELECT user_id FROM ( SELECT DISTINCT user_id FROM orders WHERE total_amount 10000 ) AS tmp );这个语法差异很容易踩后面在踩坑复盘里还会再提一次。3.3 更新删除时使用子查询的注意点除了上面说的同表子查询限制子查询里还有几个容易引起性能问题的点第一条尽量避免IN后面跟一个结果集特别大的子查询。虽然数据库优化器很多情况下会智能转换为半连接但如果你在子查询里做了很重的扫描还是会拖慢整个UPDATE/DELETE。更好的做法是先把目标主键集合导入一张临时中间表再通过UPDATE JOIN的方式关联更新。第二条关联更新时保证关联键上有索引。比如UPDATE orders o JOIN users u ON o.user_id u.id SET o.need_verify 1 WHERE u.level silver;如果users表的主键是id那么ON条件里的o.user_id对应的索引在orders表上就必须有否则每次匹配都要全表扫一遍orders性能会很差。所以在执行这类语句前最好用EXPLAIN看一眼执行计划确认关联有没有用到索引。第三条DELETE里有子查询时也存在同样的索引问题。比如DELETE FROM audit_log WHERE id IN ( SELECT id FROM audit_log WHERE created_at 2023-06-01 ORDER BY id LIMIT 10000 );有些数据库不支持在DELETE目标表的子查询里加ORDER BY LIMIT或者执行起来非常慢。这时候也要用临时表过渡。总之子查询不是不能用但要边写边想它会不会成为全表扫描的根源。4. 大批量更新删除的性能问题与分批策略4.1 全表更新为什么可能拖垮数据库有些人认为数据库操作就是一条语句的事大不大批无所谓。实际上一条UPDATE或DELETE触发的操作可能比你想象的多得多。比如在一个一千万行的表上执行全表更新即使你的SET条件很简单数据库也要逐行加锁、修改数据页、写入undo日志和redo日志并且因为MOVI等并发控制机制它还需要维护版本链。这些操作都会消耗大量CPU和I/O严重时会造成连接堆积、主从延迟飙升。更麻烦的是锁的持有时间。一条DELETE语句如果删除了几十万行那么这些行上的锁会一直保持到事务结束。期间其他事务想要读取或修改这些行就会被阻塞等待大批量操作的时间越长阻塞面就越大。所以“一条SQL删完”听起来很爽但往往是最不理智的方案。4.2 分批删除或更新的几种写法比较成熟的做法是把大批量操作拆成多个小批次每批只处理固定数量的行批与批之间间隔一点时间或者加个COMMIT。以删除过期日志为例DELETE FROM api_log WHERE created_at 2024-01-01 LIMIT 5000;如果数据库支持DELETE配合LIMIT就可以反复执行这条语句直到影响行数为0。MySQL的MyISAM和InnoDB都支持这种写法但要注意LIMIT在DELETE里的含义和SELECT不完全一样它没有ORDER BY的时候删除哪些行是不确定的不过用来清理过期数据通常不太要求顺序。如果依赖这个特性建议加上一个主键范围的过滤比如DELETE FROM api_log WHERE id BETWEEN 100000 AND 105000 AND created_at 2024-01-01;把范围切成固定大小的区间再循环删除每一批删除5000行可以让锁的影响范围可控并且每批次之间还能做一次短暂停给主从同步留出喘息时间。更新的分批也可以用类似思路但不是直接LIMIT而是借助主键范围或临时表分批。一个常见的做法是先选出要更新的主键列表然后每处理5000个主键就执行一次UPDATEUPDATE users SET status active WHERE id IN ( SELECT id FROM ( SELECT id FROM users WHERE status pending LIMIT 0, 5000 ) AS tmp );不过这个写法在老版本MySQL里可能有限制所以更稳妥的方案是在应用层写循环先取一批主键拼到UPDATE语句的IN里执行完更新再取下一批。在存储过程里也能实现但逻辑会稍微复杂一点。分批的本意是控制单次操作的影响面让数据库不至于一次性被压垮。4.3 索引对更新删除的影响与小心得索引对SELECT的加速作用大家都懂但对UPDATE和DELETE的加速作用也同等重要。每条UPDATE的第一步就是利用WHERE条件找到需要更新的行。如果WHERE条件上的列没有索引数据库就必须做全表扫描把所有行都读一遍才能筛出目标。这时候即使你只更新1行也可能扫完整个表。但索引也不是越多越好。因为每次更新时如果更新的列本身是索引列数据库除了修改数据行还要同步更新对应的索引结构这会增加额外开销。所以操作索引策略WHERE条件列建立适当索引加速定位SET更新的非索引列对索引维护无负担随意更新SET更新的索引列会引发索引重建尽量避免高频更新索引列大批量删除避免在删除条件上缺少索引否则逐行清脏数据我在实际项目中遇到过DELETE语句因为条件列没索引而把整个从库卡死的情况。那条DELETE要清理几万条过期记录但因为created_at上没有索引每次执行都要扫描整张几千万行的表执行一次要十几分钟期间主从同步落后业务查询全部超时。后来在created_at上加了索引同样的清理任务不到一秒就完成了。这个反例让我记住了写大量更新删除之前先看执行计划。5. 处理关联表时的级联与外键约束5.1 外键把DELETE挡住了怎么办如果表之间定义了外键约束直接删除被引用的父表数据时常常会遇到外键冲突数据库会拒绝执行DELETE。举一个最简单的例子一个班级表classes一个学生表students学生表里的class_id外键指向classes.id。如果某个班级里还有学生直接DELETE FROM classes WHERE id 10;大概率会报类似“Cannot delete or update a parent row: a foreign key constraint fails”的错误。这个报错本身是保护机制避免你留下一堆孤儿数据。但如果你确实想删除这个班级并且也打算把它的学生一并删除你要先删除子表数据再删除父表数据DELETE FROM students WHERE class_id 10; DELETE FROM classes WHERE id 10;或者把外键设置为ON DELETE CASCADE让数据库自动处理级联删除。在实际业务里我不建议随意使用级联删除因为它会让删除的影响范围变得不可见特别是当外键链条比较长时一条DELETE可能会在背后删除许多张表的数据。更稳妥的做法是在业务代码里明确控制删除顺序或者在存储过程里手动处理。5.2 级联删除ON DELETE CASCADE的适用场景不过有些场景下CASCADE是合理的比如订单明细和订单主表的关系订单删除后明细没有存在的意义级联删除可以简化代码也保证数据一致性。建立外键时CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT NOT NULL, product_name VARCHAR(100), CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE );之后你只要DELETE FROM orders WHERE id 500;订单明细会自动一起删除。但这里有个需要注意的点如果外键链条上有多个层级比如订单明细下还有子表则用CASCADE时要记得它只对声明了CASCADE的那一层有效。如果子表外键没有设置CASCADE删除订单时仍然会被中间表的外键挡住。所以在删除前最好先看数据字典或表结构搞清楚到底哪些表有引用关系避免“删了主表留下大片孤儿数据”的情况。5.3 自增主键在删除后是否复位的坑很多人在完成大批量删除后会关心一件事自增主键会从几开始不小心把整表数据删完之后新插入的行主键是从1开始还是继续从原来的最大值加1这个问题的答案取决于数据库和引擎。MySQL的InnoDB在删除部分行后自增计数器不会重置新插入的主键会从当前最大值1继续。如果TRUNCATE了整张表自增通常会重置为1。PostgreSQL在删除后也不会重置序列除非你手动执行setval。而SQL Server里的TRUNCATE会重置种子但DELETE不会。对这个差异我的建议是不要把自增主键的有效性当作业务逻辑的一部分。如果业务上确实需要主键从某个值重新开始那就明确做一次重置操作。例如MySQL里可以这样显式重置ALTER TABLE users AUTO_INCREMENT 1;但在生产库上执行这种操作要格外小心确保旧数据没有引用关系。总的来说自增主键仅仅是一个唯一标识符不应该依赖它来表示“当前数据的数量”或“最后一次删除的规模”。6. 实际项目中的高发错误复盘与自检清单6.1 三个典型事故复盘整理这部分时我想到了几个非常有代表性的“老师也教不会只有踩过才会有记性”的错误。第一个事故所有人都知道忘写WHERE的后果但有个同学在清理测试数据时写了一条DELETE语句中间因为临时接了个电话回来忘了自己还没执行完直接在多个数据库连接里来回切换最后在错误的连接上执行了只有FROM没有WHERE的DELETE把生产表清了。虽然最后通过备份恢复到两小时前的数据但丢失了两小时的新增内容。这次教训说明两点永远不要在一个不确定的会话里执行危险语句用事务包住操作先看影响行数再提交。第二个事故关联更新时用错了JOIN方向。想要给所有有订单的用户标记为有效写出来的却是UPDATE users u LEFT JOIN orders o ON u.id o.user_id SET u.valid 1 WHERE o.user_id IS NULL;看似逻辑清楚实际上把没有订单的用户全部标记成了有效方向反了。这种逻辑错误比漏WHERE更难发现因为操作本身没有报错影响行数也对但结果完全错误。要避免这种问题最好先执行等价的SELECT确认结果集是“有订单的用户”还是“没订单的用户”。第三个事故删除前没考虑外键关联。某同学清空一个“商品标签关联表”时只删了标签主表结果关联表里的旧标签数据全部变成孤儿记录后续报表统计时全部为NULL。后来不得不写脚本去修复。这类问题的根源是不清楚表关系就动手。6.2 更新删除前的5分钟自检最后我把自己平时在上线脚本前走一遍的检查项整理成清单拿来即用我的UPDATE/DELETE语句是否都带有WHERE子句WHERE条件的表达式是否和SELECT验证时的完全一致如果条件里包含NULL比较、OR逻辑是否明确知道会匹配哪些行是否开启了事务当前会话是不是自动提交模式如果出错我能及时ROLLBACK吗是否已经备份目标表备份表命名是否有日期方便找回是否先用SELECT统计了影响行数影响行数与预期相差大不大是否有外键关联删除父表数据时子表数据如何处理级联还是先删子表大批量操作有没有分批方案执行时间、锁的影响面是否能接受执行完成后是否用监控或复查SQL确认了预期数据已改对这些检查看起来繁琐但真正执行一次冷静的操作也就多花几分钟。很多线上事故就是省这几分钟省出来的。我自己现在养成的最深习惯是正式执行UPDATE和DELETE之前暂停两秒认定自己“下一秒有可能会后悔”然后顺手把事务打开。这个心态改变真的帮我躲过了至少三次事故。SQL里更新和删除数据说到底是两把刀用好了能高效维护数据用不好就是数据灾难。希望这篇整理能让你在动手之前多想一层我要删除哪些行、影响哪些关联数据、出错之后怎么恢复。把这些想明白再执行也不迟。