第 3 章 数据操作DML详解

📖 本篇简介：DML（Data Manipulation Language，数据操作语言）是开发者日常写得最多的一类 SQL，核心就是 INSERT、UPDATE、DELETE 三条语句。本篇在上一章建好表的基础上，带你把数据真正"装进去、改对、删干净"，并讲清楚批量插入、安全删除、TRUNCATE 与 DELETE 的本质区别这些实战高频点。学完你能独立完成业务系统里的写操作，避免"删错库、改全表"这类生产事故。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

目录

• 1 DML 概述与环境准备
• 2 INSERT 单行插入
• 3 INSERT 批量插入与高级写法
• 4 UPDATE 更新数据
• 5 DELETE 删除数据与安全删除
• 6 TRUNCATE vs DELETE 深度对比
• 7 DML 常用速查表
• 8 常见坑与注意事项
• 9 练习题

1 DML 概述与环境准备

DML 是相对于 DDL（Data Definition Language，上一章的 CREATE/ALTER）而言的。DDL 改的是"表结构"，DML 改的是"表里的数据行"。MySQL 8.0 中最常用的三条 DML 语句：

| 语句 | 作用 | 是否可回滚（InnoDB） | 典型返回值 |

|---|---|---|---|

| INSERT | 插入新行 | 是 | 受影响行数 |

| UPDATE | 更新已有行 | 是 | 受影响行数（匹配行数） |

| DELETE | 删除行 | 是 | 受影响行数 |

注意：上表说的"可回滚"是对 InnoDB 存储引擎而言的。MyISAM 不支持事务，写操作一旦执行就无法撤销。生产环境 99% 用 InnoDB，本篇示例都基于 InnoDB。

在动手之前，先准备一张贯穿全篇的示例表 students。如果你上一章已经建过库，可以直接用；没有的话执行下面这段：

-- 本示例基于 MySQL 8.0，字符集 utf8mb4，引擎 InnoDB
DROP TABLE IF EXISTS students;

CREATE TABLE students (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学号',
    name        VARCHAR(50)  NOT NULL                COMMENT '姓名',
    age         TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
    gender      ENUM('M','F') DEFAULT NULL          COMMENT '性别',
    city        VARCHAR(50)  DEFAULT '未知'          COMMENT '所在城市',
    score       DECIMAL(5,2) DEFAULT 0.00            COMMENT '考试分数',
    enroll_date  DATE         DEFAULT (CURDATE())    COMMENT '入学日期',
    created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

字段说明：id 是自增主键；age 用 TINYINT UNSIGNED 是因为年龄不可能是负数；gender 用枚举限制取值；enroll_date 用 DEFAULT (CURDATE()) 表示默认今天入学。这种"表先准备好"的习惯，能让 DML 示例一目了然。

2 INSERT 单行插入

2.1 最基础的写法

最朴素的 INSERT 是"指定列名 + 对应值"：

-- 插入一个学生：显式列出要赋值的列
INSERT INTO students (name, age, gender, city, score)
VALUES ('张三', 20, 'M', '北京', 88.50);

执行成功后，id 会自增为 1，enroll_date 取默认值今天，created_at 取默认当前时间。这种写法的优点是：就算以后表加了新列，这条 SQL 也不会失效——因为它只关心自己显式列出的列。

2.2 省略列名的写法

也可以不写列名，但必须按表定义的列顺序给全所有值：

-- 注意：必须给全 8 个值，顺序与 CREATE TABLE 完全一致
INSERT INTO students
VALUES (NULL, '李四', 19, 'F', '上海', 92.00, '2025-09-01', NOW());

id 这里传 NULL，MySQL 看到自增列收到 NULL，会自动分配下一个值。这种写法看起来简洁，但强烈不推荐：一旦上一章的 ALTER TABLE 加了一列、或者调整了列顺序，这条 SQL 立刻报错甚至写错数据。

2.3 插入时的常见约束行为

MySQL 在插入时会检查主键唯一、非空、枚举合法、外键存在等约束。比如：

-- 错误示范：gender 写成了 '男'，枚举只允许 'M'/'F'
INSERT INTO students (name, gender) VALUES ('王五', '男');
-- 在严格 SQL_MODE 下会报错：Data truncated for column 'gender'

MySQL 8.0 默认开启严格模式（STRICT_TRANS_TABLES），上面这种"值不合法"会直接报错，而不是像 5.7 早期那样悄悄截断存成空串。这对开发者是好事——问题越早暴露越好。

2.4 获取自增 ID

业务代码里经常需要"插入后立刻拿到自增主键 ID"。MySQL 提供两个办法：

-- 方法一：连接内执行 SELECT LAST_INSERT_ID()，拿到本次连接最后一次自增 ID
INSERT INTO students (name) VALUES ('赵六');
SELECT LAST_INSERT_ID();   -- 返回刚才插入的 id

-- 方法二：应用层（如 Python 的 pymysql）通过 cursor.lastrowid 取
# cur.execute("INSERT INTO students (name) VALUES ('赵六')")
# new_id = cur.lastrowid

重要：LAST_INSERT_ID() 是按连接隔离的，多个并发连接各自拿到各自的 ID，不会互相干扰。千万别用 SELECT MAX(id) 去取——并发场景下一定会拿到别人刚插入的 ID。

3 INSERT 批量插入与高级写法

3.1 一次插入多行

单行 INSERT 在 Java/Python 应用里会让网络往返次数爆炸。正确做法是用"多值列表"一次塞多行：

-- 批量插入 3 行，列名只写一次，后面跟多组 VALUES
INSERT INTO students (name, age, gender, city, score)
VALUES
    ('孙七', 21, 'M', '广州', 76.50),
    ('周八', 22, 'F', '深圳', 95.00),
    ('吴九', 20, 'M', '杭州', 81.20);

这种写法一次网络请求就完成了 3 行插入，性能是单行循环的数倍到数十倍。生产环境导入数据时，强烈建议每批 500~1000 行，太小了网络开销大，太大了 max_allowed_packet 可能报错。

3.2 INSERT ... SELECT：从别的表搬数据

如果要把查询结果直接插入另一张表，用 INSERT ... SELECT：

-- 假设已经有一张 graduate 毕业生表，把成绩 90 分以上的学生复制到优秀生表
INSERT INTO excellent_students (name, city, score)
SELECT name, city, score
FROM students
WHERE score >= 90.00;

这种写法在数据迁移、报表汇总时非常常用。它是原子的——要么全部插入，要么全部不插入（在事务里）。

3.3 INSERT ... SET：可读性更好的写法

MySQL 扩展了一种 SET 语法，列名和值成对出现，可读性比 VALUES 更好：

-- SET 写法：列=值 一一对应，适合列数多的场景
INSERT INTO students
SET name='郑十', age=23, gender='F', city='成都', score=88.00;

这种写法在拼接动态 SQL 时不容易搞错位置，但不支持批量，一次只能插一行。

3.4 主键冲突的两种处理

业务系统经常遇到"这条数据存在就更新，不存在就插入"的需求（俗称 upsert）。MySQL 8.0 提供两种方案：

-- 方案一：INSERT ... ON DUPLICATE KEY UPDATE（主键或唯一键冲突时触发 UPDATE）
INSERT INTO students (id, name, score)
VALUES (1, '张三改', 99.00)
ON DUPLICATE KEY UPDATE
    name  = VALUES(name),     -- VALUES() 引用本次想插入的新值
    score = VALUES(score);

-- 方案二：REPLACE INTO（先 DELETE 再 INSERT，主键会变！）
REPLACE INTO students (id, name, score) VALUES (1, '张三改', 99.00);

两种方案的区别非常关键：

| 特性 | ON DUPLICATE KEY UPDATE | REPLACE INTO |

|---|---|---|

| 冲突时行为 | 原地 UPDATE | 先 DELETE 再 INSERT |

| 主键是否变化 | 不变 | 可能变化（若 id 自增会跳号） |

| 触发器 | 只触发 UPDATE 触发器 | 先 DELETE 触发器再 INSERT 触发器 |

| 性能 | 一次写入 | 两次写入（删 + 插） |

| 推荐度 | 业务 upsert 首选 | 少用 |

4 UPDATE 更新数据

4.1 基础 UPDATE

UPDATE 的语法是"指定表 + SET 列赋值 + WHERE 条件"：

-- 把 id=1 的学生成绩改成 95 分
UPDATE students
SET score = 95.00
WHERE id = 1;

WHERE 是灵魂。下面这两种写法差异巨大：

-- 写法 A：只更新 id=1 那一行（生产推荐）
UPDATE students SET score = 100 WHERE id = 1;

-- 写法 B：忘了 WHERE，整张表所有行都被改成 100！
UPDATE students SET score = 100;

在 InnoDB + 事务里，写法 B 可以通过 ROLLBACK 救回来，但如果已经 COMMIT，那就是真事故。后文第 5 节会专门讲"安全删除/更新"。

4.2 同时更新多列

一次 UPDATE 可以改多列，列之间用逗号分隔：

-- 同时改城市和年龄
UPDATE students
SET city = '北京改',
    age  = age + 1,           -- 表达式：在原值基础上 +1
    score = 88.80
WHERE id = 1;

注意 SET age = age + 1 这种"在原值基础上计算"的写法非常常见，比如账户余额 SET balance = balance - 100。MySQL 是按列从左到右赋值的，同一行内引用其他列的旧值是安全的。

4.3 UPDATE 与多表连接

MySQL 支持"基于另一张表的内容更新本表"，语法是 UPDATE ... JOIN ... SET：

-- 需求：把 students 表中，所在城市为北京的学生，成绩统一 +5 分（假设城市字典表 city_dict 有评级字段）
UPDATE students s
JOIN city_dict c ON s.city = c.city_name
SET s.score = s.score + 5
WHERE c.level = '一线';

这种"多表 UPDATE"在做数据清洗、批量回填时非常有用，但JOIN 条件写错会导致更新行数爆炸，执行前一定先 SELECT 看看会匹配多少行。

4.4 UPDATE 的返回值

MySQL 客户端执行 UPDATE 后会返回类似：

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

• Rows matched：满足 WHERE 条件的行数；
• Changed：实际值发生变化的行数。

如果把 score 从 90 改成 90（值没变），Rows matched 还是 2，但 Changed 可能是 0。这是因为连接参数 CLIENT_FOUND_ROWS 的默认行为——很多 ORM 框架会用 matched 而不是 changed 判断是否更新成功。

5 DELETE 删除数据与安全删除

5.1 基础 DELETE

DELETE 的语法很简单：

-- 删除 id=1 的那一行
DELETE FROM students WHERE id = 1;

-- 删除所有北京的学生
DELETE FROM students WHERE city = '北京';

和 UPDATE 一样，WHERE 是灵魂。不带 WHERE 的 DELETE FROM students 会把整张表清空。

5.2 安全删除的三条军规

生产环境执行 DELETE / UPDATE 前，养成以下习惯能挡掉 90% 的事故：

军规一：先 SELECT 再 DELETE。

-- 第一步：先看清楚要删哪些行
SELECT * FROM students WHERE city = '北京' AND enroll_date < '2023-01-01';

-- 第二步：确认行数合理后，再执行删除
DELETE FROM students WHERE city = '北京' AND enroll_date < '2023-01-01';

军规二：小批量删除，避免长事务锁表。

-- 一次只删 1000 行，循环执行直到删完
DELETE FROM students
WHERE city = '北京'
LIMIT 1000;
-- 应用层循环执行，直到 row_count = 0

一次删几十万行会导致 undo log 暴涨、锁等待、主从延迟。分批删是工业界标准做法。

军规三：事务包裹，先 BEGIN 再删，确认无误再 COMMIT。

-- 显式开事务
START TRANSACTION;

DELETE FROM students WHERE city = '北京';

-- 检查 row_count 符合预期后再 COMMIT；不对就 ROLLBACK
COMMIT;   -- 或者 ROLLBACK;

MySQL 默认自动提交（autocommit=1），每条语句立即生效。手动开事务给了你"反悔"的机会。

5.3 物理删除 vs 逻辑删除

业务系统里"删用户"这种需求，99% 不应该真的 DELETE，而是逻辑删除——加一个 is_deleted 字段：

-- 加一个逻辑删除字段
ALTER TABLE students
ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=未删除,1=已删除';

-- 业务"删除"实际上是 UPDATE
UPDATE students SET is_deleted = 1 WHERE id = 1;

-- 所有查询都要带这个条件
SELECT * FROM students WHERE id = 1 AND is_deleted = 0;

逻辑删除的好处：数据可恢复、可审计、可做数据分析。坏处：表会越来越大，需要定期归档。

5.4 DELETE 与自增 ID 的行为

DELETE 删行后，自增计数器不会回退：

-- 假设现在 id 已经到了 5
DELETE FROM students WHERE id = 5;   -- 删掉最后一行

-- 再插入新行，id 会是 6 而不是 5
INSERT INTO students (name) VALUES ('新同学');  -- id = 6

这是 InnoDB 的设计——自增 ID 只保证递增，不保证连续。如果你一定要让计数器归零，用 TRUNCATE TABLE（下一节讲）或者 ALTER TABLE students AUTO_INCREMENT = 1。

6 TRUNCATE vs DELETE 深度对比

6.1 一句话区别

• DELETE：DML，逐行删除，可带 WHERE，可回滚；
• TRUNCATE：DDL，一次性清空整张表，不可回滚（InnoDB 8.0 中其实可以在事务内回滚，但行为仍接近 DDL），自增计数器归零。

6.2 全维度对比

| 对比项 | DELETE FROM t | TRUNCATE TABLE t | DROP TABLE t |

|---|---|---|---|

| 类别 | DML | DDL | DDL |

| 是否带 WHERE | 可以 | 不可以 | 不可以 |

| 删除行数 | 逐行，可计数 | 整表清空 | 整表删除（表结构都没了） |

| 自增计数器 | 不变 | 归零 | —— |

| 触发器 | 触发 DELETE 触发器 | 不触发 | 不触发 |

| 回滚（InnoDB） | 可回滚 | 8.0 中可回滚但慎用 | 不可回滚 |

| 速度 | 慢（大表） | 极快 | 极快 |

| 空间 | 不立即释放（InnoDB） | 释放 | 释放 |

| 锁 | 行锁 | 元数据锁 | 元数据锁 |

6.3 什么时候用哪个

-- 场景一：清理"已毕业的 2020 级学生" → 用 DELETE，带 WHERE
DELETE FROM students WHERE enroll_date < '2021-01-01';

-- 场景二：测试环境清空整张表准备重新灌数据 → 用 TRUNCATE
TRUNCATE TABLE students;

-- 场景三：表彻底不要了 → 用 DROP
DROP TABLE IF EXISTS students;

6.4 TRUNCATE 的"坑"

坑一：如果有外键引用这张表，TRUNCATE 会报错。必须先 DROP 掉外键或者把引用表也 truncate 掉。

-- 如果 scores 表的 student_id 外键引用了 students.id
-- TRUNCATE students 会报错：Cannot truncate a table referenced in a foreign key constraint
-- 解决：先关外键检查（不推荐），或者先 DELETE 子表
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE students;
SET FOREIGN_KEY_CHECKS = 1;

坑二：TRUNCATE 是 DDL，MySQL 执行 DDL 时会隐式提交前面未提交的事务。也就是说，你 BEGIN 之后 TRUNCATE，之前的修改已经被自动提交了，想回滚也回滚不了。

7 DML 常用速查表

| 需求 | 推荐 SQL | 备注 |

|---|---|---|

| 插入单行 | INSERT INTO t (c1,c2) VALUES (v1,v2); | 显式列名更安全 |

| 批量插入 | INSERT INTO t (c1,c2) VALUES (..),(..),(..); | 每批 500~1000 行 |

| 从查询插入 | INSERT INTO t SELECT ... FROM t2; | 字段顺序要对齐 |

| 拿到自增 ID | SELECT LAST_INSERT_ID(); | 按连接隔离 |

| upsert | INSERT ... ON DUPLICATE KEY UPDATE c=VALUES(c); | 主键/唯一键冲突时 |

| 更新单行 | UPDATE t SET c=v WHERE id=?; | 永远带 WHERE |

| 在原值上累加 | SET balance = balance - 100 | 注意顺序 |

| 删单行 | DELETE FROM t WHERE id=?; | 先 SELECT 确认 |

| 分批删 | DELETE FROM t WHERE ... LIMIT 1000; | 应用层循环 |

| 清空整表 | TRUNCATE TABLE t; | 自增归零、不可回滚 |

| 逻辑删除 | UPDATE t SET is_deleted=1 WHERE id=?; | 业务推荐 |

8 常见坑与注意事项

坑 1：忘写 WHERE 把全表改了/删了。

这是最高频的生产事故。养成"SELECT 先行、事务包裹、小批量"三件套。DBA 通常会给生产账号默认关闭不带 WHERE 的 UPDATE/DELETE（通过 sql_safe_updates=1）。

坑 2：`INSERT` 省略列名导致表结构变更后报错。

显式列出列名是最稳健的写法，代码 review 时也要看这一点。

坑 3：用 `SELECT MAX(id)` 取新插入的 ID。

并发场景下会拿到别人的 ID。正确做法是 LAST_INSERT_ID() 或 ORM 的 lastrowid。

坑 4：`REPLACE INTO` 导致主键变化。

它本质是 DELETE + INSERT，自增 ID 会跳号，外键引用会断。业务 upsert 一律用 ON DUPLICATE KEY UPDATE。

坑 5：大表一次性 `DELETE` 导致主从延迟。

一次删几十万行，binlog 同步到从库会延迟几十秒甚至几分钟。分批 + sleep 是标准做法。

坑 6：`TRUNCATE` 在外键引用下报错。

先处理子表，或者临时关外键检查（慎用）。

坑 7：枚举值存了不在范围内的字符串。

MySQL 8.0 严格模式会直接报错，5.7 早期会偷偷存空串。升级到 8.0 后旧数据要清理。

坑 8：批量插入超过 `max_allowed_packet`。

默认 64M 或 4M，单批太大直接报错。把 batch_size 控制在合理范围。

9 练习题

练习 1：批量插入

题目：向 students 表一次插入 5 个学生，姓名分别是"A""B""C""D""E"，年龄都是 20，其他字段用默认值。

<details>

<summary>参考答案</summary>

INSERT INTO students (name, age)
VALUES ('A', 20),
       ('B', 20),
       ('C', 20),
       ('D', 20),
       ('E', 20);

</details>

练习 2：安全更新

题目：把"北京"的学生成绩全部加 5 分，但不要超过 100 分。

<details>

<summary>参考答案</summary>

UPDATE students
SET score = LEAST(score + 5, 100)   -- LEAST 取两个值里较小的，保证不超 100
WHERE city = '北京';

</details>

练习 3：upsert

题目：如果 id=100 不存在就插入（name='测试'，score=60），存在就把 score 改成 60。

<details>

<summary>参考答案</summary>

INSERT INTO students (id, name, score)
VALUES (100, '测试', 60)
ON DUPLICATE KEY UPDATE score = 60;

</details>

练习 4：清空数据但保留表结构

题目：测试环境想把 students 表清空，并且自增 ID 从 1 重新开始，应该用哪条 SQL？

<details>

<summary>参考答案</summary>

TRUNCATE TABLE students;
-- DELETE FROM students 不会让自增 ID 归零；
-- DROP TABLE 会把表结构也删掉，不符合"保留表结构"。

</details>

练习 5：逻辑删除 vs 物理删除

题目：为什么生产环境"删除用户"通常用逻辑删除而不是物理 DELETE？请说出至少 3 个理由。

<details>

<summary>参考答案</summary>

1. 可恢复：误删后能通过 is_deleted=0 恢复，物理删除无法恢复。

2. 可审计：合规要求（金融、医疗）必须保留历史数据，逻辑删除能追溯。

3. 外键关联：用户表被订单表引用，物理删除会破坏外键约束；逻辑删除不影响。

4. 数据分析：用户的历史行为、转化漏斗分析需要完整数据，物理删除会丢样本。

</details>

下一篇预告：第 4 章 SELECT基础与WHERE过滤