我印象最深的一次线上事故是有人准备在 MySQL 里删一条测试数据结果 DELETE 语句忘了带 WHERE把一张几万行的订单表直接清空。MySQL 的 DMLData Manipulation Language数据操纵语言三兄弟——INSERT、UPDATE、DELETE——看着语法简单真到实战里一个疏忽就能让数据遭殃。这套学习笔记写到第五章正好就轮到这个主题DML 语言里的增、删、改表中数据。这篇内容我打算把 DML 三大操作的语法、边界情况、以及和事务机制的关系一并讲透。适合刚学完建库建表、准备正式操作表数据的初学者也适合已经写了一阵子 SQL、想回头补齐细节的开发者。DML 看起来只有三条语句但每一条背后都牵扯着索引、锁、日志、事务这些数据库核心机制把它们串起来理解写出来的 SQL 才不只是“能跑”而是“跑得稳”。1. DML 到底管哪三件事为什么它和 DDL、DQL 必须分开记1.1 一套 SQL 体系里三类语句各管一摊MySQL 的 SQL 语句按功能可以粗略分成三类DDLData Definition Language数据定义语言负责定义结构比如 CREATE TABLE 建表、ALTER TABLE 改表结构、DROP TABLE 删表DMLData Manipulation Language数据操纵语言负责操作表里的数据内容也就是 INSERT、UPDATE、DELETEDQLData Query Language数据查询语言负责查询数据SELECT 是代表。有些教材把 SELECT 并进 DML但 MySQL 学习体系里一般单独拎出来因为查询的逻辑远比写入复杂。用一个生活化类比一张表就像一栋房子。DDL 决定房子砌几面墙、开几扇窗是结构工程DML 是往房间里搬家具、换家具、扔家具是内容管理DQL 则是巡房查看里面住了什么、家具怎么摆放。建完表之后日常工作打交道最多的其实是 DML。这也是 MySQL 面试题里绕不开的考点——很多人能背出 SELECT 各种复杂查询但被问到“删除一张表里的重复数据保留最小 id”这种 DML 实战题反而会卡壳。1.2 写入型语句的特殊地位不可逆、有日志、受事务约束DML 三条语句和 SELECT 有本质区别它们会改变数据库的内容所以数据库必须为它们记录日志、加锁、产生事务。这也是很多初学者最容易忽略的点——你发出的每个 INSERT、UPDATE、DELETE都不只是“执行一条命令”而是触发了一系列连锁动作记录 binlog二进制日志用于主从复制和数据恢复。写 undo log用于事务回滚和 MVCC多版本并发控制。写 redo log保证崩溃之后数据能恢复。对涉及的行加锁避免并发操作把数据改乱。理解了这一层你就能反过来想明白很多现象为什么大表 UPDATE 会锁等待超时为什么长事务会导致日志膨胀为什么一条 DML 报错之后之前的操作还能 ROLLBACK所以学这一章时不要只背语法试着把 DML 和事务章节连起来看。哪怕还在自己电脑的测试库上操作也最好养成“写入必开事务、操作必看影响行数”的习惯因为这套肌肉记忆迟早要在生产环境里救你一次。注意DDL 语句在 MySQL 中通常会自动触发隐式提交一旦执行很难回滚DML 语句则不同它受事务控制只要没 COMMIT理论上可以 ROLLBACK。这也是 DML 数据相对“可挽救”的根本原因。2. INSERT 插入数据从最基础语法到边界情况拆解2.1 先学会三种最基本的插入姿势INSERT 的职责是往表里加数据最基础的写法有两种完整列插入和指定列插入。-- 完整列插入values 的数量和顺序必须和表的列完全一致 INSERT INTO user VALUES (1, 张三, zhangsanexample.com, NOW()); -- 指定列插入只给部分列赋值其余列用默认值或 NULL INSERT INTO user (id, name) VALUES (2, 李四);为什么建议业务代码里尽量用指定列插入因为表结构经常演进。今天你按完整列顺序写明天别人 ALTER TABLE 加了一列你的 INSERT 就会直接报错或者数据错位。指定列插入相当于给你和表结构之间加了一层“契约声明”只要两边列名对应上后续加列不会炸。第三种姿势是多行插入一次语句插入多条记录INSERT INTO user (id, name, email) VALUES (3, 王五, wangwuexample.com), (4, 赵六, zhaoliuexample.com), (5, 孙七, sunqiexample.com);多行插入在性能上有明显优势。MySQL 执行 INSERT 时每一条语句都有网络交互、日志写入、语句解析的开销把这些开销摊到多行上比循环执行单条 INSERT 少得多。实测在几千行的批量导入场景下一次 100 行批量插入通常比逐条插入快一个数量级。2.2 默认值、NULL 和自增主键这三样最容易混淆指定列插入时没写到的列会走两种值显式定义的 DEFAULT 默认值或者 NULL。CREATE TABLE user ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT DEFAULT 18, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );对上表执行INSERT INTO user (name) VALUES (测试)结果里 id 会自动生成age 是 18created_at 是当前时间。这个过程中的细节值得逐一说明id 是自增主键可以手写指定值也可以省略。手写时要注意别和已有值冲突否则报 1062 Duplicate entry 错误。age 列即使声明了 DEFAULT 18如果插入时显式给 NULL那结果就是 NULL不是 18。NULL 和 DEFAULT 是两回事。created_at 用 DEFAULT CURRENT_TIMESTAMP插入时自动写入当前时间省得应用层手动塞时间。自增主键的“回落”问题也值得提醒如果你删除了 id 最大的几条记录再插入新数据自增序列并不会回收已删除的编号。换句话说 DELETE 之后自增 ID 继续往上走。这对业务通常无害但如果有人觉得“我删了几条新数据应该从删掉的位置接上”那就理解偏了。另一个常用函数是 LAST_INSERT_ID()它返回当前连接上最后一次 INSERT 产生的自增 ID。注意“当前连接”这四个字——同一个连接上连续执行多次插入每次都会更新这个值但换一个连接去查什么都拿不到。所以在 Java 里通过 JDBC 获取自增主键通常要先拿到同一个 Connection 再查或者用 JDBC 的 RETURN_GENERATED_KEYS 机制。2.3 用 INSERT INTO SELECT 批量搬运数据INSERT 不仅能接 VALUES还能接 SELECT 的结果把一张表的数据直接灌进另一张表。这个语法几乎每个月都会用比如做报表临时表、表结构升级时的数据迁移、或者从历史表里捞归档数据。-- 把 user_2024 表里满足条件的数据搬进 user 表 INSERT INTO user (id, name, email, created_at) SELECT id, name, email, created_at FROM user_2024 WHERE created_at 2024-01-01;这里有几个必须注意的坑列的类型和长度要对得上尤其是字符集的隐式转换容易导致乱码或截断。建议在连接串里明确指定字符集比如使用 utf8mb4。如果目标表有唯一索引搬数据时遇到重复键会直接报错中止。此时可以用 INSERT IGNORE 跳过冲突行或者用后面要讲的 ON DUPLICATE KEY UPDATE 做合并。大批量插入前建议先对 SELECT 做 COUNT(*)确认要搬多少行避免一把梭把事务搞太大。2.4 插入冲突怎么办IGNORE、REPLACE 和 ON DUPLICATE KEY UPDATE目标表有主键或唯一索引时INSERT 可能撞上重复键。MySQL 给了几种处理策略实际选型要看语义策略行为适用场景INSERT直接报错1062数据必须严格唯一冲突应该暴露出来INSERT IGNORE跳过冲突行不报错清洗数据希望“能插进去就插插不进去拉倒”REPLACE先删旧行再插新行完全用新数据替代旧数据ON DUPLICATE KEY UPDATE冲突时执行指定的 UPDATE同步类场景想保留已有行的某些字段ON DUPLICATE KEY UPDATE 是同步类项目里用得最多的写法比如每天从上游接口拉数据主键相同就更新不同就插入INSERT INTO user (id, name, email) VALUES (10, 周八, zhoubaexample.com) ON DUPLICATE KEY UPDATE name VALUES(name), email VALUES(email);MySQL 8.0.20 之后VALUES() 语法被标记为不推荐官方建议改用行别名写法INSERT INTO user (id, name, email) VALUES (10, 周八, zhoubaexample.com) AS new ON DUPLICATE KEY UPDATE name new.name, email new.email;这个迁移值得提一嘴因为很多人还在老资料里抄 VALUES() 用法虽然现在还能跑但升级到新版本后迟早会踩到VALUES()is deprecated 的告警。REPLACE 也要慎用它本质是 DELETE INSERT会导致自增 ID 变化、触发两次日志写入对性能和数据一致性都有额外影响。3. UPDATE 更新数据动手之前先问自己三件事3.1 第一问WHERE 条件真的写对了吗UPDATE 的语法很简单指定表、指定 SET 的字段和值、指定 WHERE 过滤条件。UPDATE user SET age age 1 WHERE name 张三;但简单归简单线上事故排行榜里UPDATE 忘带 WHERE 常年霸榜。没有 WHERE 的 UPDATE 会更新整张表的所有行。所以很多生产环境会开启 MySQL 的 safe-update 模式SQL_SAFE_UPDATES该模式下 UPDATE 和 DELETE 必须带上带索引的 WHERE 条件否则报 1175 错误SET SQL_SAFE_UPDATES 1;这个模式我个人建议开发环境就常开强制自己养成写 WHERE 的习惯。如果你确实需要更新全表可以用WHERE 11明确表达意图至少代码评审时一眼能看出你是故意的。WHERE 的坑不止“有没有”还有“对不对”。最常见的是 NULL 判断WHERE name NULL永远不会成立NULL 要用IS NULL或IS NOT NULL判断。其次是多条件的优先级AND 和 OR 混用时最好加括号否则逻辑和直觉对不上。3.2 第二问这次更新会影响多少行可不可控业务代码里做 UPDATE通常要拿到影响行数affected rows。影响行数为 0 有两种可能一是 WHERE 没匹配到任何记录二是匹配到了但原值和新值相同MySQL 在默认行为下不视为“变更”影响行数记 0。为了区分这两种情况我建议的操作流程是更新前先跑一条 SELECT COUNT(*)确认目标行数。更新后重新 SELECT 抽查几条看数据是否符合预期。如果期望值和实际影响行数对不上优先怀疑数据本来就不满足条件。批量更新还有个重要经验不要一条 UPDATE 更新十万行以上。长事务会把大量行锁住其他会话的读写全被堵住轻则慢查询重则锁等待超时1205 错误。正确做法是把大批量切成小批次比如一次更新 1000 行循环处理批次之间留出间隙让其他事务有机会执行。-- 分批更新的示意每次处理 id 小于当前游标的 1000 行 UPDATE big_table SET status 1 WHERE id 1000 AND status 0;3.3 第三问如果是多表关联更新JOIN 的语义清楚吗MySQL 的 UPDATE 支持多表关联常用在同步冗余字段、批量改状态、按照另一张表的计算结果更新等场景-- 根据订单表统计结果回填用户表的消费总额 UPDATE user u JOIN ( SELECT user_id, SUM(amount) AS total FROM orders WHERE status PAID GROUP BY user_id ) o ON o.user_id u.id SET u.total_spent o.total;多表更新的三个注意点关联的列要有索引否则会产生大量的全表扫描更新速度会慢到让你怀疑人生。先跑一条等价的 SELECT 确认关联结果再改成 UPDATE。比如上面的语句先SELECT u.id, u.total_spent, o.total FROM ...看一眼再动手。被更新的表如果同时出现在子查询里MySQL 会报 1093 错误You cant specify target table for update in FROM clause。典型例子是想“删除重复记录时保留最小 id”这种 SQL需要套一层派生表绕过限制DELETE FROM user WHERE id NOT IN ( SELECT id FROM ( SELECT MIN(id) AS id FROM user GROUP BY email ) tmp );3.4 UPDATE 的另一个隐藏点时间戳的自动更新建表时如果给字段设了ON UPDATE CURRENT_TIMESTAMP那每次 UPDATE 只要行被匹配到这个字段就会自动更新为当前时间。看起来方便但在某些场景里是个坑比如你在同步数据明明更新的是 name 字段updated_at 却悄悄变了下游按 updated_at 做增量同步时就会多拉一批本该改的数据。所以建表时要想清楚updated_at 到底是“物理变更时间”还是“业务变更时间”。如果只是想知道行有没有被动过用自动更新没问题如果要精确记录业务字段的变更节点最好在应用层显式赋值。4. DELETE 删除与 TRUNCATE 清空都是删差别很大4.1 DELETE 的语法和删除逻辑DELETE 语法也很直接DELETE FROM user WHERE id 100;DELETE 是逐行删除受事务控制可以配合 ROLLBACK 回滚。删除时每行还会触发触发器如果存在、记录 binlog、维护索引所以删除几万行并不会比更新快多少。DELETE 也支持 ORDER BY 和 LIMIT 子句这在分批删除时非常有用DELETE FROM user_log WHERE created_at 2024-01-01 ORDER BY id LIMIT 1000;另外 DELETE 还有两个容易被忽略的点删除顺序问题。如果被删的表被其他表的外键引用直接删可能报 1451 外键约束错误需要先删子表引用记录再删父表记录。自增 ID 不回收。前面说过DELETE 之后自增序列继续递增这属于正常现象。4.2 DELETE、TRUNCATE、DROP 三兄弟的定位差异很多新手分不清这三个我经常用一个“房子”类比DELETE 是把家具扔出去房子还在可以反悔回滚TRUNCATE 是把屋里清空房架子还在但过程更快、不可按行回滚DROP 是直接把房子拆了结构都没了。用表格对比一下对比项DELETETRUNCATEDROP作用对象表数据可加 WHERE 删部分行表数据整表清空表结构 数据全部删除是否可回滚事务内可回滚通常隐式提交风险高通常不可恢复需依赖备份是否重置自增 ID否是表都没了无所谓速度慢逐行处理快直接释放存储页快直接删除定义是否支持 WHERE是否否触发器会触发不触发不触发重要提醒TRUNCATE 在 MySQL 中按 DDL 类操作处理执行往往会隐式提交不会像 DELETE 那样一行行过。如果要求“可回滚的删除”务必用 DELETE别用 TRUNCATE。4.3 大表删除的实操策略生产环境里“DELETE 一张大表里的数据”是高风险动作。即使只删一部分也可能因为 WHERE 条件没走索引导致全表扫描加大量行锁直接把数据库拖垮。我的实操经验是分三步先确认 WHERE 条件对应列有索引。没有索引就先处理查询效率问题否则删除动作会变成灾难。分批删除每次删几千行循环执行最好在低峰期。删除前先跑 SELECT COUNT(*) 确认影响行数删除后对照行数验证结果。如果是要“清理整张表且不再需要里面的数据”TRUNCATE 通常比 DELETE 更合适速度快、占用空间直接释放。但跑 TRUNCATE 之前务必备份并且明确它在主从复制链条中的行为——有些场景下 TRUNCATE 在大事务和复制方面的表现和 DELETE 不一样需要先查阅当前版本的官方文档确认。5. 事务和 DML 的绑定关系为什么写入能“后悔”5.1 手动开事务的三种姿势默认情况下MySQL 每个 DML 语句都会自动提交AUTOCOMMIT1相当于每句都自成一个事务。想“后悔”就得显式开启事务-- 方式一 START TRANSACTION; UPDATE user SET age age 1 WHERE id 1; ROLLBACK; -- 方式二 BEGIN; DELETE FROM user WHERE id 2; COMMIT; -- 方式三关闭自动提交 SET AUTOCOMMIT 0;三种方式最终效果差别不大但要注意SET AUTOCOMMIT0 会影响当前会话所有后续语句容易导致你忘了提交留下一个长期不结束的事务拖累锁和日志不建议日常使用。显式的 START TRANSACTION ... COMMIT/ROLLBACK 是最清晰、最可控的做法。5.2 四个最容易踩的坑第一DDL 会隐式提交。事务里执行了 CREATE TABLE 或 ALTER TABLEMySQL 会把当前事务先 COMMIT 掉。所以“先开事务然后 DROP TABLE再想 ROLLBACK”是救不回来的。第二语言接口里的自动提交。如果你在 JDBC/MyBatis 里设置了 autoCommitfalse但中间有代码抛异常没捕获连接可能带着未提交的事务回到连接池下一个人用这个连接就会继承脏事务状态。所以在 Java 里正确姿势是 try/finally 中明确 COMMIT 或 ROLLBACK或者依赖 Spring 的 Transactional 边界管理。第三锁的粒度。InnoDB 默认走行锁但如果你 UPDATE 时 WHERE 条件没有索引MySQL 就会升级为锁定扫描范围内的所有行。后果就是并发一上来其他事务全部排队等待。第四隔离级别的误读。默认的 REPEATABLE READ 下两个事务同时改同一条记录后提交的会覆盖先提交的如果业务上需要“先到先得”得靠 SELECT ... FOR UPDATE 显式加锁或者版本号字段做乐观锁。5.3 一个完整的事务回滚演示下面这个例子是教学时常用的演示脚本建议直接在本地测试库跑一遍感受事务的效果CREATE TABLE demo_account ( id INT PRIMARY KEY, balance DECIMAL(10,2) NOT NULL ); INSERT INTO demo_account VALUES (1, 100.00); START TRANSACTION; UPDATE demo_account SET balance balance - 50 WHERE id 1; SELECT * FROM demo_account; -- 当前连接能看到 50 ROLLBACK; SELECT * FROM demo_account; -- 数据恢复 100因为整个事务已回滚跑完你就明白两件事一是事务内部的修改对当前连接立即可见对其它连接未必可见二是 ROLLBACK 之后所有未提交的改动全部撤销。这也是为什么我说 DML 是学习 MySQL 事务处理的最佳入口。6. 我的实操经验高频错误复盘与安全操作习惯6.1 三大高频事故复盘写了这么多年 SQL也帮人排查过不少线上问题DML 相关的事故基本集中在三类事故一UPDATE/DELETE 忘带 WHERE 或 WHERE 写错。最典型的例子是把WHERE id 100写成WHERE id 100 OR 1 1或者批处理脚本里拼接条件时空字符串被当成无条件下发。防法是让代码在执行前打印完整 SQL肉眼过一遍。事故二批量操作无限制锁超时。有人写循环 UPDATE一次更新 50 万行。结果不仅自己慢还把业务链路里其他查询全部堵住最后整个库报 1205 Lock wait timeout exceeded。防法是控制每次操作的行数配合“WHERE 主键范围 LIMIT”分批执行。事故三先删后插的顺序错误。比如先 DELETE 再 INSERT中间应用崩了数据就少了。这种情况应把“先删后插”改成事务内的“先查重再插入”或者用 REPLACE/ON DUPLICATE KEY UPDATE 这种原子操作。6.2 用 EXPLAIN 和 SHOW PROCESSLIST 给 DML 做体检DML 语句慢不要只盯着语句本身看。先用 EXPLAIN 看执行计划重点看用到什么索引、估计扫描多少行EXPLAIN UPDATE big_table SET status 1 WHERE user_id 12345;如果 type 是 ALL全表扫描或者 key 为 NULL就说明 WHERE 条件没走索引。这种情况先加索引再执行 UPDATE速度往往差出几十倍。如果线上已经出现锁等待可以用SHOW PROCESSLIST看当前有哪些连接卡在 Waiting for lock再用information_schema.innodb_trx表查长时间未提交的事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx;这条命令能帮你快速定位是不是有个忘了 COMMIT 的事务占了锁。6.3 我给自己立的 DML 操作铁律最后分享几个一直遵守的操作习惯都是从教训里沉淀出来的生产环境执行 UPDATE/DELETE 前先在事务里跑一条等价的 SELECT确认影响范围。任何批量 DML 都写成“小步快跑”单次影响行数控制在几千行内循环之间睡个几百毫秒。执行完立刻看 affected rows和预期对不上就先查数据别急着提交。清理数据优先用 DELETE 而不是 TRUNCATE除非明确知道 TRUNCATE 的后果并做了备份。变更前备份目标表数据。最简单的备份就是CREATE TABLE user_bak_20240101 AS SELECT * FROM user WHERE 要变动的条件便宜且有效。这些规则看起来繁琐但真遇到一次线上事故省下的时间远比这些操作成本多。DML 这个章节说到底不是背语法而是建立对“数据变更”的敬畏心——写进去之前想清楚改之前看明白删之前留后路。把这套习惯练成肌肉记忆你写出来的每一条增删改语句都会比大多数人要稳。