我印象最深的一次线上事故，是有人准备在 MySQL 里删一条测试数据，结果 DELETE 语句忘了带 WHERE，把一张几万行的订单表直接清空。MySQL 的 DML（Data Manipulation Language，数据操纵语言）三兄弟——INSERT、UPDATE、DELETE——看着语法简单，真到实战里，一个疏忽就能让数据遭殃。这套学习笔记写到第五章，正好就轮到这个主题：DML 语言里的增、删、改表中数据。
这篇内容我打算把 DML 三大操作的语法、边界情况、以及和事务机制的关系一并讲透。适合刚学完建库建表、准备正式操作表数据的初学者，也适合已经写了一阵子 SQL、想回头补齐细节的开发者。DML 看起来只有三条语句，但每一条背后都牵扯着索引、锁、日志、事务这些数据库核心机制，把它们串起来理解，写出来的 SQL 才不只是“能跑”，而是“跑得稳”。
1. DML 到底管哪三件事，为什么它和 DDL、DQL 必须分开记
1.1 一套 SQL 体系里，三类语句各管一摊
MySQL 的 SQL 语句按功能可以粗略分成三类：DDL（Data Definition Language，数据定义语言）负责定义结构，比如 CREATE TABLE 建表、ALTER TABLE 改表结构、DROP TABLE 删表；DML（Data Manipulation Language，数据操纵语言）负责操作表里的数据内容，也就是 INSERT、UPDATE、DELETE；DQL（Data 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, '张三', 'zhangsan@example.com', NOW()); -- 指定列插入，只给部分列赋值，其余列用默认值或 NULL INSERT INTO user (id, name) VALUES (2, '李四');为什么建议业务代码里尽量用指定列插入？因为表结构经常演进。今天你按完整列顺序写，明天别人 ALTER TABLE 加了一列，你的 INSERT 就会直接报错或者数据错位。指定列插入相当于给你和表结构之间加了一层“契约声明”，只要两边列名对应上，后续加列不会炸。
第三种姿势是多行插入，一次语句插入多条记录：
INSERT INTO user (id, name, email) VALUES (3, '王五', 'wangwu@example.com'), (4, '赵六', 'zhaoliu@example.com'), (5, '孙七', 'sunqi@example.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 是 18，created_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, '周八', 'zhouba@example.com') ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);MySQL 8.0.20 之后，VALUES() 语法被标记为不推荐，官方建议改用行别名写法：
INSERT INTO user (id, name, email) VALUES (10, '周八', 'zhouba@example.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 1=1明确表达意图，至少代码评审时一眼能看出你是故意的。
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 can't 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 是直接把房子拆了，结构都没了。
用表格对比一下：
| 对比项 | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| 作用对象 | 表数据（可加 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 语句都会自动提交（AUTOCOMMIT=1），相当于每句都自成一个事务。想“后悔”，就得显式开启事务：
-- 方式一 START TRANSACTION; UPDATE user SET age = age + 1 WHERE id = 1; ROLLBACK; -- 方式二 BEGIN; DELETE FROM user WHERE id = 2; COMMIT; -- 方式三：关闭自动提交 SET AUTOCOMMIT = 0;三种方式最终效果差别不大，但要注意：SET AUTOCOMMIT=0 会影响当前会话所有后续语句，容易导致你忘了提交，留下一个长期不结束的事务，拖累锁和日志，不建议日常使用。显式的 START TRANSACTION ... COMMIT/ROLLBACK 是最清晰、最可控的做法。
5.2 四个最容易踩的坑
第一，DDL 会隐式提交。事务里执行了 CREATE TABLE 或 ALTER TABLE，MySQL 会把当前事务先 COMMIT 掉。所以“先开事务，然后 DROP TABLE，再想 ROLLBACK”是救不回来的。
第二，语言接口里的自动提交。如果你在 JDBC/MyBatis 里设置了 autoCommit=false，但中间有代码抛异常没捕获，连接可能带着未提交的事务回到连接池，下一个人用这个连接就会继承脏事务状态。所以在 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 这个章节，说到底不是背语法，而是建立对“数据变更”的敬畏心——写进去之前想清楚，改之前看明白，删之前留后路。把这套习惯练成肌肉记忆，你写出来的每一条增删改语句，都会比大多数人要稳。
