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

今天小编给大家梳理一下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 占用的磁盘空间

说明：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技术分享社区」