MYSQL笔记:删除操作Delete、Truncate、Drop用法比较

发布于 2022-4-16 18:56
浏览
0收藏

今天小编给大家梳理一下MYSQL删除操作Delete、Truncate、Drop用法有什么区别,到底该如何合理使用,希望对大家能有帮助!
1、执行速度比较
Delete、Truncate、Drop关键字都可以删除数据
drop>truncate>delete
2、原理方面
2.1 delete
delete属于数据库DML操作语言,只会删除数据表中的记录,会执行事务,执行的时候也会触发触发器。
InnoDB数据库引擎中,执行delete操作只会给删除的记录打上了删除标记,并不会真正删除数据,只是把删除的数据记录设置为不可见,不会释放磁盘空间,如果插入新的数据可以覆盖该部分空间。
如果开启事务的话,执行delete操作,会先将要删除数据缓存到rollback segement中,等事务commit之后才生效。
delete from table_name 不带查询条件会删除表的全部数据,MyISAM引擎会立刻释放磁盘空间,InnoDB 不会释放磁盘空间;如果带查询条件的话都不会释放磁盘空间,可以执行optimize table table_name 会立刻释放磁盘空间。建议如果需要释放存储空间的话可以执行delete后,然后执行optimize table table_name 语句达到清理磁盘空间的目的。-- 查询数据库test对应的表t_user 占用的磁盘空间
MYSQL笔记:删除操作Delete、Truncate、Drop用法比较-开源基础软件社区

说明:delete 操作是逐行执行删除的,并且同时将每行的的删除操作日志记录在redo和undo表空间中去,便于进行回滚(rollback)和重做操作,因此生成的大量操作日志也会占用磁盘空间。
2.2 truncate
truncate是数据库DDL定义语言,不受事务影响,也不会触发 trigger。执行操作后会立即生效,无法找回删除的数据。
执行truncate table table_name 会立刻释放磁盘空间 ,不管是 InnoDB和MyISAM 都一样 。
truncate可以退快速清空一个表。并且重置auto_increment自动增长的值。针对不同类型的数据存储引擎是有区别的,具体如下:
MyISAM:truncate会重置auto_increment(自增序列)的值为1。而delete后表仍然保持auto_increment。
InnoDB:truncate会重置auto_increment的值为1。delete后表仍然保持auto_increment。但是在做delete整个表之后重启MySQL的话,则重启后的auto_increment会被置为1。
说明:InnoDB的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存,但是重启后就找不到了,只能从1开始。实际上重启后的auto_increment会从 SELECT 1+MAX(ai_col) FROM t 开始。
使用truncate操作的时候要最好备份表,避免出现不可挽回的情况。
2.3 drop
drop属于数据库DDL定义语言,和truncate一样。执行后会立即生效,不可恢复。
drop table table_name 执行成功后不管是MyISM还是InnoDB都会立刻释放磁盘空间 ,并且会删除该数据表上依赖的约束(constrain)、触发器(trigger)、索引(index);  依赖于该表的存储过程/函数将保留,但是会变为失效状态。
总结
在工作当中执行数据库删除的时候一定要慎重再慎重,建议每次进行数据删除的使用最好数据表的备份工作,这样就会大大减少你删除跑路的几率。很多时候不要过于相信自己的动手能力,老虎还有打盹的时候,万一手滑了呢。尽可能养成好的数据库运维习惯,这样会让自己少跌跟头,你的事业才会更加顺利。

本文转载自微信公众号「IT技术分享社区」

收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐