truncate与delete的区别
引言
最近到了系统开发后期，需要对数据进行按时间备份。备份完成后对之前数据表的处理就只有删除了，突然查下资料，发现删除还是挺多的。显而易见都明白此刻应该用什么删除了。就不在此讨论解决方案了，只总结交流知识点。
本文主要面向数据库开发者、运维人员以及对 SQL 数据操作有深入理解需求的读者。通过阅读本文，你将能够清晰理解 TRUNCATE、DELETE 和 DROP 三种删除操作的核心差异，并能在实际场景中根据性能、事务、数据恢复等需求做出正确的选择。
TRUNCATE TABLE 命令概述
truncate table命令将快速删除数据表中的所有记录，但保留数据表结构。这种快速删除与delete from 数据表的删除全部数据表记录不一样，delete命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，而truncate命令删除的数据是不可以恢复的。
直观测试与对比
为了直观地对比 TRUNCATE 和 DELETE 在性能及自增 ID 行为上的差异，我们可以设计一个具体的测试。以下是一个完整的 SQL 测试脚本示例，包含建表、插入 100 万测试数据、执行 TRUNCATE/DELETE 操作、再插入数据并观察自增 ID 变化的全过程。
-- 1. 创建测试表，包含自增主键字段 CREATE TABLE test_truncate_delete ( id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- 2. 插入 100 万条测试数据（模拟一个中等规模的数据表） -- 这里使用存储过程或循环插入，以 MySQL 为例： DELIMITER $$ CREATE PROCEDURE insert_test_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 1000000 DO INSERT INTO test_truncate_delete (data) VALUES (CONCAT('Test data ', i)); SET i = i + 1; END WHILE; END$$ DELIMITER ; -- 执行存储过程，插入 100 万条数据 CALL insert_test_data(); -- 查看插入后的最大自增 ID SELECT MAX(id) AS max_id_before_delete FROM test_truncate_delete; -- 预期结果：max_id_before_delete = 1000000 -- 3. 测试 TRUNCATE 操作 -- 先备份表结构（可选），然后执行 TRUNCATE TRUNCATE TABLE test_truncate_delete; -- 查看表状态，确认数据已清空 SELECT COUNT(*) AS row_count_after_truncate FROM test_truncate_delete; -- 预期结果：row_count_after_truncate = 0 -- 再次插入一条数据，观察自增 ID INSERT INTO test_truncate_delete (data) VALUES ('After TRUNCATE'); SELECT LAST_INSERT_ID() AS id_after_truncate; -- 关键观察点：id_after_truncate 应为 1，因为 TRUNCATE 会重置自增计数器 -- 4. 重新填充数据，为 DELETE 测试做准备 -- 先删除存储过程（如果存在），然后重新插入 100 万数据 DROP PROCEDURE IF EXISTS insert_test_data; -- 重新创建并执行存储过程（同上，略） -- ... 重新插入 100 万条数据 ... -- 5. 测试 DELETE 操作 -- 执行不带 WHERE 子句的 DELETE，删除全部数据 DELETE FROM test_truncate_delete; -- 查看表状态，确认数据已清空 SELECT COUNT(*) AS row_count_after_delete FROM test_truncate_delete; -- 预期结果：row_count_after_delete = 0 -- 再次插入一条数据，观察自增 ID INSERT INTO test_truncate_delete (data) VALUES ('After DELETE'); SELECT LAST_INSERT_ID() AS id_after_delete; -- 关键观察点：id_after_delete 应为 1000001，因为 DELETE 不会重置自增计数器 -- 6. 清理测试环境（可选） DROP TABLE test_truncate_delete; DROP PROCEDURE IF EXISTS insert_test_data;关键步骤注释说明：
- 建表：创建包含
AUTO_INCREMENT主键的InnoDB表，这是观察自增 ID 行为的基础。 - 插入 100 万数据：通过存储过程批量插入，模拟真实业务数据量。插入后最大 ID 应为 1000000。
- TRUNCATE 测试：
TRUNCATE TABLE会瞬间清空表并重置自增计数器。之后插入的数据 ID 从 1 开始。 - DELETE 测试：
DELETE FROM（不带 WHERE）会逐行删除，速度较慢，且不会重置自增计数器。之后插入的数据 ID 会从之前的最大值（1000000）继续递增。 - 观察点：通过
SELECT LAST_INSERT_ID()和SELECT MAX(id)可以清晰看到两种操作对自增字段的不同影响。
通过这个测试，你可以直观地验证：
- TRUNCATE 速度极快，且会重置自增 ID。
- DELETE 速度较慢，但保留自增 ID 的当前最大值。
相同点与不同点
相同点
truncate和不带where子句的delete，以及drop都会删除表内的数据。
不同点
以下是 TRUNCATE、DELETE 和 DROP 三种操作的详细对比：
| 特性 | TRUNCATE | DELETE | DROP |
|---|---|---|---|
| 对表结构的影响 | 只删除数据，保留表结构（定义） | 只删除数据，保留表结构（定义） | 删除表结构（定义）及其依赖的约束、触发器、索引；存储过程/函数变为无效状态 |
| 事务与回滚 | DDL 操作，立即生效，数据不放入回滚段，不可回滚，不触发触发器 | DML 操作，事务提交后生效，数据放入回滚段，可回滚，会触发触发器 | DDL 操作，立即生效，数据不放入回滚段，不可回滚，不触发触发器 |
| 空间与高水位线 | 默认释放空间到 minextents 个 extent（除非使用 REUSE STORAGE），高水位线复位（回到最开始） | 不影响表所占用的 extent，高水位线保持原位置不动 | 释放表所占用的全部空间 |
| 速度 | 快（仅次于 DROP） | 慢（逐行删除） | 最快（直接删除表） |
| 安全性 | 需谨慎使用，无备份时数据不可恢复 | 相对安全，支持事务回滚 | 需极其谨慎，无备份时表结构和数据均不可恢复 |
使用建议
- 想删除部分数据行用delete，注意带上where子句。回滚段要足够大。
- 想删除表，当然用drop。
- 想保留表而将所有数据删除。如果和事务无关，用truncate即可。如果和事务有关，或者想触发trigger，还是用delete。
- 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。
实战场景选择指南
为了帮助你在不同场景下快速做出正确选择，以下决策树清晰地展示了在 TRUNCATE、DELETE 和 DROP 之间进行选择的逻辑流程：
flowchart TD Start[需要执行删除操作] --> Q1{需要删除什么？} Q1 -->|删除部分数据行| A1[使用 DELETE] A1 --> A1_Note[带上 WHERE 子句] Q1 -->|清空整个表| Q2{需要保留表结构吗？} Q2 -->|否| B1[使用 DROP] B1 --> B1_Note[表结构和数据均被删除] Q2 -->|是| Q3{需要事务支持或触发触发器吗？} Q3 -->|是| C1[使用 DELETE] C1 --> C1_Note[不带 WHERE，可回滚，触发触发器] Q3 -->|否| Q4{表数据量是否很大？} Q4 -->|是，追求速度| D1[使用 TRUNCATE] D1 --> D1_Note[速度快，重置自增ID，不可回滚] Q4 -->|否，或需保留自增ID| C2[使用 DELETE] C2 --> C2_Note[速度较慢，保留自增ID，可回滚] Q1 -->|删除整个表（结构+数据）| B1 %% 样式定义 classDef default fill:#f9f9f9,stroke:#333,stroke-width:1px classDef decision fill:#e1f5fe,stroke:#01579b classDef action fill:#c8e6c9,stroke:#2e7d32 classDef note fill:#fff3e0,stroke:#ef6c00 class Q1,Q2,Q3,Q4 decision class A1,B1,C1,C2,D1 action class A1_Note,B1_Note,C1_Note,C2_Note,D1_Note note决策树解读与关键场景说明：
- 删除部分数据行：唯一选择是
DELETE，必须使用WHERE子句指定条件。 - 清空整个表（保留结构）：
- 如果需要事务支持（例如，操作可能失败需要回滚）或者需要触发关联的触发器 → 选择
DELETE（不带WHERE）。 - 如果不需要事务和触发器，且数据量很大，追求极速清空 → 选择
TRUNCATE。 - 如果不需要事务和触发器，但希望保留自增 ID 的当前值（例如，不想让业务流水号重置） → 选择
DELETE（不带WHERE）。
- 如果需要事务支持（例如，操作可能失败需要回滚）或者需要触发关联的触发器 → 选择
- 删除整个表（包括结构）：直接使用
DROP。此操作不可逆，务必确认表已不再需要，且相关依赖（如视图、存储过程）已处理。 - 处理大表时的考量：
TRUNCATE在清空大表时速度最快，因为它不记录单行日志。但代价是操作不可回滚，且会重置自增计数器。如果数据安全性和事务完整性更重要，即使是大表也应使用DELETE（可能需要分批次执行以避免长事务锁表）。
将此指南与前面的「使用建议」和对比表格结合使用，你将能 confidently 为任何数据删除场景选择最合适的 SQL 命令。
语句实例
truncate table tax_yys