详细了解SQL中delete，drop，truncate的区别和不同

最新推荐文章于 2024-04-27 12:08:27 发布

征服bug

最新推荐文章于 2024-04-27 12:08:27 发布

阅读量2.3k

点赞数 3

分类专栏：关于数据库的那当事文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_53678904/article/details/130205150

版权

36 篇文章 9 订阅

订阅专栏

其实总体来说就一句话就可以概况了，当你不再需要该表时，用 drop；当你仍要保留该表，但要删除所有记录时，用 truncate；当你要删除部分记录时, 用 delete。

看完这就好详细学习过SQL的家人就已经对他们有啥不同和使用场景已经有所概念了吧，但是往往在面试中与会遇到类似的这种问题，所以我们要其更加深入的了解。

命令格式：

delete from 表名 [where 条件]    --删除指定行

drop table 表名;                --删除表

truncate table 表名                --清空表内容，不删除表

1.操作对象不同

delete：能删除表中或基于真实表创建的视图中的数据
truncate：只能删除表中的数据，无法应用在视图上

2.条件限制区别

delete：删除表中数据时，可以通过 where 关键字，进行选择性s删除
truncate：不能使用 where 关键字进行选择性删除

3.事务

delete：删除表中数据时，涉及到事务处理：回退（缓存）、提交、撤退
truncate：删除缓存时，不涉及缓存事务处理

4.删除效率

delete：由于 delete 删除数据时要考虑事务管理（缓存、回退机制、日志记录），所以当删除大批量数据时，速度慢，效率低，甚至达不到删除的目的
truncate：删除大批量数据时，速度快、效率高、但无法撤销

5.激活触发器

delete：在进行删除操作时，可能激活触发器的处理
truncate：不存在激活触发器处理

6.外键约束

delete：可以运用 SQL 语句、按照业务逻辑、按照一定的先后顺序可以删除相关表中的数据
truncate：不能删除对于由 foreign key 约束引用的表，不能删除该表中的数据

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
对用TRUNCATE TABLE删除数据的表上增加数据时，要使用UPDATE STATISTICS来维护索引信息。
如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。

delete语句，是DML语句，truncate语句通常被认为是DDL语句。
delete语句，后面可以跟where子句，通常指定where子句中的条件表达式，只删除满足条件的部分记录，而truncate语句，只能用于删除表中的所有记录。
truncate语句，删除表中的数据后，向表中添加记录时，自动增加字段的默认初始值重新从1开始，而使用delete语句，删除表中所有记录后，向表中添加记录时，自动增加字段的值，为删除时该字段的最大值加1，也就是在原来的基础上递增。
delete语句，每删除一条记录，都会在日志中记录，而使用truncate语句，不会在日志中记录删除的内容，因此，truncate语句的执行效率比delete语句高。
效率方面:drop > truncate > delete
drop一般用于删除整体性数据如表，模式，索引，视图，完整性限制等；delete用于删除局部性数据如表中的某一元组
DROP把表结构都删了；DELETE只是把数据清掉；truncate是将表进行清空
当你不再需要该表时，用 drop；当你仍要保留该表，但要删除所有记录时，用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.

关注