truncate 、delete与drop区别

truncate

作用：清空表中的数据；

• truncate之后，自增属性列的值被重置为初始值；

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

• 仅清空表中的数据，表的元信息不变;

When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.

• 无事务日志，且不触发Trigger，数据无法恢复;
• 删除整个表的数据时，truncate的速度比delete快很多；

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

delete

作用：删除表中的某些行；

• 被delete删除的数据，可以通过事务日志恢复；
• 不要使用delete删除大量数据，会造成长时间停顿；

解决方案：将剩余数据拷贝到新表，使用drop删除旧表，重命名新表；

　　If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:
1. Select the rows not to be deleted into an empty table that has the same structure as the original table:
2. Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
3. Drop the original table:

drop

作用：清空表的数据，以及表的元信息；

• drop命令执行成功后，表以及数据无法恢复；

参考：

1. https://dev.mysql.com/doc/refman/5.6/en/drop-table.html；
2. https://dev.mysql.com/doc/refman/5.6/en/delete.html；
3. https://dev.mysql.com/doc/refman/5.6/en/truncate-table.html；