drop、delete、truncate的区别

一、drop

    1、drop是DDL语言，会隐式提交，所以不能回滚，不会触发触发器。

    2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放出来。

    3、drop语句是删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留，但是变为invalid状态

二、delete

    1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的删除操作记录redo和undo表空间中，以便进行回滚（rollback）和重做操作，但是需要注意表空间足够大，需要手动提交（commit）操作才能生效，可以通过回滚（rollback）撤销操作。

    2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那边表中删除所有记录。

    3、delete语句不影响表所占用的盘区（extent），高水线（high watermark）保持原位置不变。

三、truncate

    1、truncate是DDL，会隐式提交，所以不能回滚，不会触发触发器。

    2、truncate会删除表中所有记录，并且将重新设置高水线（high watermark）和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用resue storage，不会记录日志，所以执行速度很快，但是不能通过rollback撤销操作。（如果不小心把表truncate掉，也是可以恢复的）

    3、对于外键（foreign key）约束引用的表，不能使用 truncate table，而应该使用不带where子句的delete语句。

    4、truncate table不能用于参与索引视图的表。

总结：

1、在速度上，一般来说，drop>truncate >delete。

2、在使用drop和truncate时一定注意，慎重使用，避免麻烦。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大：

如果想删除表，当然用drop;

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果时整理内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

 

mysql 批量删除表

       Select CONCAT( 'drop table ', table_name, ';' ) 
       FROM information_schema.tables

      Where table_name LIKE 'dede_%';

      dede_指定前缀表

       生成删表语句，然后执行这些语句

显示异常：

       Cannot delete or update a parent row: a foreign key constraint fails。是由表关联生成的强制约束问题，在删除的时候回检查表之间的关联关系，从而导致无法删除。

      SET foreign_key_checks = 0;  // 先设置外键约束检查关闭
 
       drop table table1;  // 删除表，如果要删除视图，也是如此
 
       SET foreign_key_checks = 1; // 开启外键约束检查，以保持表结构完整性

 

MySQL的环境变量中存在一个foreign_key_checks，这是默认检查外键的配置项，如果将其设置为0，则表示不检查外键约束。查看foreign_key_checks的值：

show VARIABLES like "foreign%";