✅作者简介:大家好,我是Leo,热爱Java后端开发者,一个想要与大家共同进步的男人😉😉
🍎个人主页:Leo的博客
💞当前专栏:每天一个知识点
✨特色专栏: MySQL学习
🥭本文内容:MySQL中drop、truncate和delete的区别
📚个人知识库: Leo知识库,欢迎大家访问
1.前言
对于drop、truncate和delete,虽然简单,但是真要使用或者面试时候问到还是需要有一定的总结,今天来简单讲讲他们直接的区别。在此之前先简单了解下什么是DDL和DML。
DDL(数据定义语言,Data Definition Language):DDL代表数据定义语言,是一种有助于创建数据库模式的SQL命令。DDL中常用的命令有:create
,drop
,alter
,truncate
和rename
等等。
DML(数据操作语言,Data Manipulation Language):DML代表数据操作语言,是一种有助于检索和管理关系数据库中数据的SQL命令。DML中常用的命令有:insert
,update
,delete
和select
等等。
2.drop
DROP
命令用于删除整个表(结构和数据),或数据库等对象,特点如下:
-
彻底删除:
DROP
命令不仅删除表内的所有数据,还删除了表的结构定义。这个过程是不可逆的,除非有备份可以恢复。 -
释放资源:执行
DROP
命令后,与该表相关的数据库资源会被释放。 -
非事务性操作:
DROP
操作通常不能回滚,执行DROP
命令后,相关对象就被立即删除。 - 自增ID:如果创建新表,自增ID会重新开始计数。
代码示例:
DROP TABLE employees;
注意:DROP语句执行后立即生效,无法找回。
3.truncate
TRUNCATE
命令用于删除表中的所有行,其特点如下:
-
快速清空表:
TRUNCATE
比使用DELETE
删除表中的所有行要快得多,因为它不逐行删除数据,而是通过释放存储这些数据的数据页来删除数据并重新初始化表。 -
非事务性操作:尽管某些数据库管理系统可能允许
TRUNCATE
操作在事务中回滚,但在很多情况下,TRUNCATE
并不记录详细的日志,因此不能像DELETE
操作那样保证事务安全。 -
不触发触发器:通常,执行
TRUNCATE
操作不会触发表的触发器。 -
自动重置自增ID:对于有自增主键的表,
TRUNCATE
会重置自增计数器。 -
truncate会删除表中所有记录,并且将重新设置高水线和所有的索引。
就是truncate会删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子),缺省情况下将空间释放到minextents的extent(就是表结构中的段内的区域),除非使用reuse storage(使用这句话,所在的extent空间不会被回收,只是将数据删除掉,数据删除之后的freespace空间,只能供本表使用,其他的不可以使用)。不会记录日志,所以执行速度很快,但不能通过rollback撤消操作(如果一不小心把一个表truncate掉,也是可以恢复的,只是不能通过rollback来恢复
示例:文章来源:https://www.toymoban.com/news/detail-849893.html
TRUNCATE TABLE employees;
注意:TRUNCATE语句执行后立即生效,无法找回。
4.delete
DELETE
命令用于删除表中的一行或多行记录,具有如下特点:
-
选择性删除:可以通过
WHERE
子句指定删除哪些行。若不指定WHERE
子句,则删除表中的所有行。 -
事务性操作:
DELETE
操作是事务安全的,这意味着你可以在一个事务中回滚DELETE
操作。这在你意外删除了错误数据时非常有用。 -
触发器:如果表上有触发器,执行
DELETE
操作会触发它们。 -
性能:因为
DELETE
操作逐行删除数据,并记录日志,所以在删除大量数据时可能会比较慢。 -
delete语句不影响表所占用的extent(就是表结构的中的区),高水线(high watermark)保持原位置不变。 (高水位线就存在于段(segment)中,它用于标识段中已使用过的数据块与未使用的数据块二者间交界,扫描表数据的时候,高水位线以下的所有数据块都必须被扫描。)
-
在 InnoDB 中,delete其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。虽然未释放磁盘空间,但是下次插入数据的时候,仍然可以重用这部分空间(重用 → 覆盖)。
-
delete执行时,会先将所删除数据缓存到rollback segement中,事务commit之后生效;
delete from table_name
删除表的全部数据,对于MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间;
对于delete from table_name where xxx
带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
delete操作以后使用optimize table table_name
则会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的,delete以后执行optimize table
操作。
示例:
DELETE FROM employees WHERE department = 'Sales';
5.总结
在速度上,一般来说,drop> truncate > delete
。文章来源地址https://www.toymoban.com/news/detail-849893.html
- 如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;如果想删除表,当然用drop;
- 如果想保留表而将所有数据删除,如果和事务无关(不能回滚),用truncate即可;
- 如果和事务有关,或者想触发trigger,还是用delete;
- 如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。
- truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,可回滚。
- truncate只能作用于表;delete,drop可作用于表、视图等。
- truncate会清空表中的所有行,但表结构及其约束、索引等保持不变;drop会删除表的结构及其所依赖的约束、索引等。
- truncate会重置表的自增值;delete不会。
- truncate不会激活与表有关的删除触发器;delete可以。
- truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减少表或索引所占用的空间,drop语句将表所占用的空间全释放掉。
到了这里,关于MySQL中drop、truncate和delete的区别的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!