SQL 中 Drop、Delete 与 Truncate 的区别

学JAVA的派大星
987 阅读8分钟

前言

在数据库操作中，我们通常通过 SQL 语句与数据库进行交互。虽然市面上有许多数据库图形化工具可以辅助操作，但归根结底，这些工具的本质是将用户的操作转化为 SQL 语句与数据库通信。因此，优秀的数据库管理工具无论多么智能，其核心仍然是 SQL。

目前市面上有许多优秀的数据库图形化工具，按国内外产品来区分，主要有以下几款：

国外产品：

  • DataGrip：由 JetBrains 公司开发，是开发者中广为人知的数据库管理工具，功能强大且支持多种数据库。
  • DBeaver：一款开源的数据库管理工具，虽然相对 DataGrip 知名度稍低，但在开发者社区中也广受欢迎，特别是在支持多种数据库方面表现出色。

国内产品：

  • Navicat：作为拥有 20 多年历史的老牌数据库管理工具，Navicat 在国内市场享有很高的知名度，支持多种数据库并且功能丰富。
  • Chat2DB：由前阿里巴巴员工、EasyExcel 作者创立的一款新兴产品，虽然相对年轻，但其与 AI 的深度结合极大地提高了操作效率，正在迅速成长中。

尽管有许多优秀的数据库客户端工具，我们作为开发者依然需要熟练掌握 SQL 语句的知识，才能更好地理解数据库的运行机制。因此，今天我们来深入探讨一下 DROP、DELETE 和 TRUNCATE 这三种操作的区别。

SQL 类型

首先，我们从 SQL 的类型来区分 DROP、DELETE 和 TRUNCATE 这三种操作：

  1. DROP

    1. SQL 类型：DROP 是一种 DDL （ Data Definition Language ，数据定义语言） 操作。
    2. 语法格式： DROP TABLE table_name;
    3. 作用：用于删除数据库中的整个表、视图、索引或其他数据库对象。一旦执行 DROP 操作，删除的对象将无法恢复，表结构和数据都会被完全移除。

  2. DELETE

    1. SQL 类型：DELETE 是一种 DML （ Data Manipulation Language ，数据操作语言） 操作。
    2. 语法格式： DELETE FROM table_name WHERE condition;
    3. 作用：用于删除表中的特定记录（行），但表结构和索引保留。DELETE 可以通过 WHERE 子句指定删除的条件，删除的记录也可以通过事务回滚（如果数据库支持事务）恢复。

  3. TRUNCATE

    1. SQL 类型：TRUNCATE 也是一种 DDL 操作，尽管它主要用于数据操作。
    2. 语法格式： TRUNCATE TABLE table_name;
    3. 作用：用于快速删除表中的所有记录，保留表结构和索引。与 DELETE 不同，TRUNCATE 操作无法通过 WHERE 子句筛选记录，也不支持事务回滚。TRUNCATE 通常比 DELETE 更快，因为它不会逐行删除记录，而是直接释放表中的数据空间。

不想看文字的朋友，也可以直接看如下关于 DROP、DELETE 和 TRUNCATE 操作的对比表格：

操作SQL 类型作用是否保留表结构支持事务回滚执行速度使用场景
DROPDDL（数据定义语言）删除整个表或其他数据库对象否否最快完全移除表及其数据，不再需要时使用
DELETEDML（数据操作语言）删除表中的特定记录是是慢（逐行删除）删除部分或全部记录，保留表结构时使用
TRUNCATEDDL（数据定义语言）快速删除表中的所有记录是否较快清空表数据但保留表结构时使用

性能对比

  1. DROP ——> 最快

原理：

DROP ****操作会从数据库的元数据中彻底移除整个表的定义，包括表结构、数据、索引以及触发器等所有信息。不需要逐行处理，因此速度非常快。适用于不再需要该表时的彻底删除操作。

执行 DROP 时，数据库会对表进行锁定，并确保所有相关的外键约束得到处理或解除。若存在依赖于该表的其他对象（如视图或存储过程），则需要先解决这些依赖问题，否则数据库将抛出错误。DROP 操作是不可逆的，意味着表及其所有关联数据将被永久删除。

  1. DELETE ——> 最慢

原理：

DELETE 操作会在目标表中逐行扫描，并依据 WHERE 子句中的条件来决定哪些行应被删除；在删除每行数据时，数据库会记录这些操作以便支持事务回滚。此外，如果表上配置了触发器（如 BEFORE DELETE 或 AFTER DELETE 触发器），则在删除操作发生时会触发这些触发器。

这种逐行处理方式，尤其是在有复杂 WHERE 条件时，需要检查每一行数据是否满足条件，使得 DELETE 成为三者中最慢的操作。如果表中有很多记录或条件语句较复杂，性能会进一步受到影响。但好处是，DELETE 操作提供了更高的灵活性，允许通过条件语句精确删除特定数据。

  1. TRUNCATE ——> 较快

TRUNCATE 操作通过直接释放表中的数据空间来删除所有记录，不用逐行处理数据，且不会触发行级别的触发器，因此比 DELETE 快得多。同时表的结构和索引仍然保留，会比**DROP** ****操作稍慢。

另外，由于它是 DDL 操作，会自动提交事务，不能回滚，执行 TRUNCATE 后，表中的计数器（如 AUTO_INCREMENT）通常也会被重置。

实战演练

上面说了这么多，其实也起不到什么作用，正所谓，"光说不练假把式"，我们下面就进入实战。

在进入正式测试前，我们还得准备一些测试表以及数据，SQL 如下(由于本人较懒，所以以下 SQL 均由 Chat2DB 生成)

准备测试数据

 CREATE TABLE example_data (
    id int NOT NULL AUTO_INCREMENT,
   data varchar(255) DEFAULT NULL,
   PRIMARY KEY (id)
 ) ENGINE=InnoDB AUTO_INCREMENT=553161 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 
 
 CREATE DEFINER=root@localhost PROCEDURE insert_one_million_data()
 BEGIN
     DECLARE i INT DEFAULT 0;
     WHILE i < 1000000 DO
         INSERT INTO example_data (data) VALUES (CONCAT('Data ', i));
         SET i = i + 1;
     END WHILE;
 END

当然其实也不一定需要完完整整的插入一百万条数据，有一定数据量就行，下图是我当前测试表中的总数据行数：

数据的拷贝

之后，我们需要做的就是复制拷贝多几张表出来（没有该工具的同学，也可以使用如下 SQL 手动进行表的复制），供我们一会测试使用，毕竟插入这么些数据还是要花点时间的。

  -- 这是复制表结构以及数据的语法
 CREATE TABLE new_table_name AS SELECT * FROM original_table_name;

速度层面比对

DROP操作的耗时

DELETE操作的耗时

TRUNCATE操作的耗时

小结

我们可以看到，执行起来的速度差距是显而易见的，最快的无疑是DROP操作，其次是TRUNCATE操作，最慢的则是DELETE操作。

深入探讨DELETE操作和TRUNCATE操作

触发器的角度

DELETE操作

我们可以创建一个触发器来记录删除行为，看看是不是DELETE操作会触发我们的触发器。

经过测试，我们会发现，delete_log 这张表中，确实插入了非常多的删除日志。说明DELETE操作会触发该表上相关联的记录删除操作的触发器。

TRUNCATE操作

但是，如果我们通过TRUNCATE操作会发现delete_log这张表中其实是没有插入日志的，证实TRUNCATE操作不会触发触发该表上相关联的记录删除操作的触发器。

事务的角度

DELETE操作
-- 查询该表当前的总记录函数
SELECT 
  count(*) 
FROM 
  example_data_copy_0902144250 
  ;

SET
  autocommit = 0;

delete from example_data_copy_0902144250;

-- 执行删除后，但事务为回滚，查询该表的总记录数
SELECT 
  count(*) 
FROM 
  example_data_copy_0902144250 
  ;

rollback;

-- 查询事务回滚后，查询该表的总记录数
SELECT 
  count(*) 
FROM 
  example_data_copy_0902144250 
  ;

以下是执行上述 SQL 的结果：

我们再看到其中执行的三个查询语句的结果：

如上图所示，我们可以清晰的看到，该表在delete操作的前中后的三个状态，在DELETE操作执行完之后，我们通过对事务的rollback后，发现数据全部都回来了，证明DELETE操作是会消耗事务日志资源的。

TRUNCATE操作
-- 查询该表当前的总记录函数
SELECT
  count(*)
FROM
  example_data_copy_0902144250;

SET
  autocommit = 0;

TRUNCATE table example_data_copy_0902144250;

-- 执行truncate后，但事务为回滚，查询该表的总记录数
SELECT
  count(*)
FROM
  example_data_copy_0902144250;

rollback;

-- 查询事务回滚后，查询该表的总记录数
SELECT
  count(*)
FROM
  example_data_copy_0902144250;

以下是执行上述 SQL 的执行结果：

我们再看到其中执行的三个查询语句的结果：

从上述三个结果其实我们也知道了，执行TRUNCATE操作的时候，无论事务有没有回滚，数据都是无法恢复的了，所以TRUNCATE操作时无法回滚的，那么它相对也是不占用事务日志资源的。

总结

总结的话，我们就通过 Chat2DB AI 快速总结一下。

首先，总结一下DROP，DELETE，TRUNCATE这三个操作的不同以及速度：

然后，我们再从触发器以及事务的角度，总结一下这三个操作：

最后，我们总结一下这三个操作的注意事项：

avatar
文章
阅读
粉丝