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

1、比较执行速度

Delete、Truncate、Drop关键字可以删除数据

drop>truncate>delete

delete属于数据库DML操作语言，只删除数据表中的记录，执行事务，执行时触发触发器。

在InnoDB数据库引擎中，执行delete操作只会在删除记录上标记删除记录，而不会真正删除数据。只需将删除的数据记录设置为不可见且不会释放磁盘空间。如果插入新数据，则可以覆盖这部分空间。

如果开始事务，执行delete操作，将首先将删除数据缓存到rollback 在segement中，事务commit后才生效。

delete from table_name MyISAM引擎会立即释放磁盘空间，无需查询条件就可以删除表中的所有数据，InnoDB 磁盘空间不会释放；如果有查询条件，磁盘空间不会释放，optimizeee可以执行 table table_name 磁盘空间将立即释放。如果需要释放存储空间，可以执行delete，然后执行optimizee table table_name 语句达到清洁磁盘空间的目的。

-- 查询数据库test对应的表T_user 占据磁盘空间的selectt concat(round(sum(DATA_LENGTH/1024/1024)'M') as table_size from information_schema.tables where table_schema='test' AND table_name='t_user';

说明：delete 操作是逐行删除的，同时在redo和undo表空间记录每行删除的操作日志，便于回滚（rollback）并且重做操作，所以生成的大量操作日志也会占用磁盘空间。

truncate是数据库DDL定义语言，不受事务影响，也不会触发 trigger。执行操作后会立即生效，删除的数据无法找回。

执行truncatete table table_name 磁盘空间将立即释放 ，不管是 InnnoDB和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操作时，最好备份表，避免不可挽回的情况。

drop属于数据库DDL定义语言，与truncate相同。执行后会立即生效，无法恢复。

drop table table_name 无论是MyISM还是InnoDB，执行成功后都会立即释放磁盘空间 ，数据表上依赖的约束将被删除(constrain)、触发器(trigger)、索引(index); 将保留依赖表的存储过程/函数，但将变为故障状态。

在工作中删除数据库时，我们必须小心。建议每次使用最佳数据表进行数据删除备份，这将大大降低您删除逃跑的机会。大多数时候，不要太相信你的动手能力。老虎还在打盹。如果你的手滑倒了。尽可能养成数据库运维的良好习惯，这将减少你的跟踪，使你的事业更加顺利