DELETE

导学

数据有进就有出。DELETE 是 SQL 中删除表中数据的核心语句。本节从最简单的单条删除开始，逐步讲解条件删除、批量删除、清空表以及删除中的常见陷阱。

定义

DELETE：DML（数据操作语言）语句，用于从表中删除满足条件的行。DELETE 可以带 WHERE 条件精确删除，也可以不带条件删除全表数据。

核心语法

-- 带条件删除（推荐，生产环境必须带 WHERE）
DELETE FROM 表名 WHERE 条件;

-- 删除全表数据（慎用）
DELETE FROM 表名;

-- 多表删除
DELETE t1 FROM 表1 t1 INNER JOIN 表2 t2 ON t1.id = t2.id WHERE t2.条件;

演示数据准备

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表中的完整数据如下：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

SQL 示例

场景一：按条件删除单行

当前数据状态：见上文 employees 表完整数据。

执行语句：

DELETE FROM employees WHERE emp_id = 2;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100

结果解读：WHERE emp_id = 2 精确匹配白歌这一行，删除后表中只剩大翔。DELETE 返回的受影响行数为 1。

场景二：按条件删除多行

当前数据状态：重新插入数据后：

INSERT INTO employees (emp_name, dept, score) VALUES
('白歌', '技术部', NULL);
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

执行语句：

DELETE FROM employees WHERE dept = '技术部';

操作后结果：

SELECT * FROM employees;

Empty set (0.00 sec)

结果解读：WHERE dept = '技术部' 匹配全部 2 行，全部删除。表中无数据。DELETE 返回的受影响行数为 2。

警告：生产环境中执行 DELETE 不带 WHERE 或 WHERE 条件过于宽泛，可能导致误删大量数据。建议先执行 SELECT 验证条件范围，再执行 DELETE。

场景三：DELETE 与 ORDER BY + LIMIT 配合（精确控制删除数量）

当前数据状态：重新插入数据：

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

执行语句：

DELETE FROM employees WHERE dept = '技术部' ORDER BY emp_id DESC LIMIT 1;

操作后结果：

SELECT * FROM employees;
emp_idemp_namedeptscore
1大翔技术部100

结果解读：ORDER BY emp_id DESC LIMIT 1 表示按 emp_id 降序排列后，只删除第 1 条（即 emp_id 最大的白歌）。这是控制删除数量的安全手段。

场景四：DELETE 与 JOIN 配合（多表删除）

当前数据状态：创建关联表：

CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    score DECIMAL(5,2)
);

INSERT INTO scores (emp_id, score) VALUES
(1, 90),
(2, 85);

employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

scores 表：

score_idemp_idscore
1190
2285

执行语句：

DELETE s FROM scores s
INNER JOIN employees e ON s.emp_id = e.emp_id
WHERE e.score IS NULL;

操作后结果：

SELECT * FROM scores;
score_idemp_idscore
1190

结果解读：DELETE s FROM ... 语法删除 scores 表中与 employees 表关联且 employees.score IS NULL 的行。白歌的 score 为 NULL，因此 scores 表中 emp_id = 2 的记录被删除。scores 表中只剩大翔的成绩。

场景五：DELETE vs TRUNCATE 的区别

当前数据状态：重新插入数据：

INSERT INTO employees (emp_name, dept, score) VALUES
('白歌', '技术部', NULL);
emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

执行语句：

-- 方式一：DELETE 清空全表
DELETE FROM employees;

-- 方式二：TRUNCATE 清空全表
TRUNCATE TABLE employees;

操作后结果：两种方式执行后表都为空，但行为差异很大：

特性DELETETRUNCATE
事务回滚✅ 可以回滚❌ 不能回滚（隐式提交）
触发器✅ 会触发 DELETE 触发器❌ 不触发触发器
自增计数器保留当前值重置为初始值
执行速度慢（逐行删除，记日志）快（直接释放数据页）
WHERE 条件✅ 支持❌ 不支持
返回值返回删除行数返回 0 行

结果解读：

  • 需要事务安全、触发器、或保留自增值 → 用 DELETE
  • 快速清空全表、不需要回滚 → 用 TRUNCATE
  • 生产环境清空表优先用 TRUNCATE（如果不需要回滚）

常见误区

误区正解
"DELETE 不带 WHERE 是清空表的标准做法"生产环境必须带 WHERE。清空全表用 TRUNCATE 更快，但不可回滚。
"DELETE 删除后自增 ID 会回退"不会。DELETE 不影响自增计数器，TRUNCATE 才会重置。
"DELETE 和 TRUNCATE 都可以回滚"DELETE 在事务内可回滚；TRUNCATE 是 DDL，隐式提交，不可回滚。
"DELETE 后表结构也被删除"不会。DELETE 只删数据行，表结构、索引、约束都保留。删表用 DROP TABLE。
"DELETE 多表时语法和 SELECT 一样"语法不同。DELETE t1 FROM t1 JOIN t2 ...，要显式指定删除哪个表。

面试考点

Q：DELETE 和 TRUNCATE 的区别？

  1. DELETE 是 DML，逐行删除，记日志，可回滚，会触发触发器，保留自增值；TRUNCATE 是 DDL，直接释放数据页，不记单行日志，不可回滚，不触发触发器，重置自增值。2. DELETE 支持 WHERE，TRUNCATE 不支持。

Q：误删数据怎么恢复？

  1. 如果还在事务内，直接 ROLLBACK；2. 如果已提交且有 binlog，可通过 mysqlbinlog 解析并反向执行；3. 如果有定期备份，从备份恢复 + binlog 增量恢复；4. 无备份无 binlog → 基本无法恢复。

Q：DELETE 后表空间会缩小吗？

DELETE 只标记数据行删除，不会立即释放表空间文件（.ibd）。InnoDB 的空间由后台 purge 线程回收，但文件大小不会自动缩小。如需释放空间，需执行 OPTIMIZE TABLE（会锁表）。

Q：为什么生产环境 DELETE 必须带 WHERE？

不带 WHERE 会删除全表数据，且 DELETE 是逐行执行，大数据量时极慢，可能导致长时间锁表。即使意图清空全表，也应先用 SELECT COUNT(*) 确认数据量，再决定用 DELETE（需回滚）还是 TRUNCATE（快速）。

小结

  • DELETE 是删除数据行的 DML 语句，支持 WHERE 精确控制删除范围
  • DELETE 可以配合 ORDER BY + LIMIT 控制删除数量
  • DELETE 支持多表删除语法（DELETE t1 FROM t1 JOIN t2 ...）
  • DELETE 可回滚、会触发触发器、保留自增值；TRUNCATE 更快但不可回滚
  • 生产环境执行 DELETE 前务必先用 SELECT 验证条件范围

下一章引子：学会了删除数据，接下来学习如何修改已有数据——UPDATE 语句。