[心得]详解MySQL三种删除表的方法

大家都知道，删除表有三种方法：drop，delete，和truncate。工作中有时候图省事，全部drop掉。但没有仔细分析为啥drop最省事。这个问题经常在面试中被问到。

drop，不带where子句的delete，以及truncate都会删除表内数据。

不同的方面如下：
1. delete是一种DML，它会放到rollback segment，事务提交之后才生效。如果有对应的触发器，那么执行时还会触发trigger。
truncate，drop是DDL，操作立即生效，也就意味着不能回滚。
2. truncate和delete不删除表的结构，drop将删除表的结构被依赖的约束，触发器，索引；依赖于表的存储过程/函数将被保留，但是变为invalid状态。
3. delete语句不影响表所占用的extent，high watermark不动。drop语句将最大限度释放该表所占的空间。truncate语句缺省情况下将空间释放到最小个extent，除非使用reuse storage，那种情况下下truncate将空间恢复到最大个extent。
4. 速度上，drop>truncate>delete
5. 安全性，drop和druncate都不能回滚，因此慎用。
6. 使用上，想删部分数据行，用带where子句的delete命令。想删除表，用drop。想保留表而将所有数据删除，并且和事务无关，用truncate；如果和事务有关，还是用delete。如果想整理表内碎片，用truncate加上reuse_storage，再重新导入/插入数据。