长丰网站制作,春节网页制作素材,免费企业网站源代码,八师石河子精神文明建设网站✅作者简介#xff1a;大家好#xff0c;我是Leo#xff0c;热爱Java后端开发者#xff0c;一个想要与大家共同进步的男人#x1f609;#x1f609; #x1f34e;个人主页#xff1a;Leo的博客 #x1f49e;当前专栏#xff1a;每天一个知识点 ✨特色专栏#xff1a…✅作者简介大家好我是Leo热爱Java后端开发者一个想要与大家共同进步的男人 个人主页Leo的博客 当前专栏每天一个知识点 ✨特色专栏 MySQL学习 本文内容MySQL中drop、truncate和delete的区别 个人知识库 Leo知识库欢迎大家访问
1.前言
对于drop、truncate和delete虽然简单但是真要使用或者面试时候问到还是需要有一定的总结今天来简单讲讲他们直接的区别。在此之前先简单了解下什么是DDL和DML。 DDL(数据定义语言,Data Definition Language)DDL代表数据定义语言是一种有助于创建数据库模式的SQL命令。DDL中常用的命令有createdropaltertruncate和rename等等。 DML(数据操作语言Data Manipulation Language)DML代表数据操作语言是一种有助于检索和管理关系数据库中数据的SQL命令。DML中常用的命令有insertupdatedelete和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来恢复
示例
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。
如果想删除部分数据用delete注意带上where子句回滚段要足够大如果想删除表当然用drop如果想保留表而将所有数据删除如果和事务无关(不能回滚)用truncate即可如果和事务有关或者想触发trigger还是用delete如果是整理表内部的碎片可以用truncate跟上reuse stroage再重新导入/插入数据。truncate与drop是DDL语句执行后无法回滚delete是DML语句可回滚。truncate只能作用于表deletedrop可作用于表、视图等。truncate会清空表中的所有行但表结构及其约束、索引等保持不变drop会删除表的结构及其所依赖的约束、索引等。truncate会重置表的自增值delete不会。truncate不会激活与表有关的删除触发器delete可以。truncate后会使表和索引所占用的空间会恢复到初始大小delete操作不会减少表或索引所占用的空间drop语句将表所占用的空间全释放掉。
