MySQL DELETE语句和TRUNCATE TABLE

Question

两者到底有什么区别， 据说 TRUNCATE 操作， binlog 也恢复不了。

Answer 1

个人理解：truncate是对表结构的一种重置功能，通过truncate之后，表会自动恢复到create时的状态，它不仅仅是数据删除的问题，而是整个表结构的恢复，例如你的row_id如果是auto increment的，delete是不会影响它的，如果是truncate，那么它就会恢复到了初始值了。

Answer 2

truncate不记录日志,不能从日志中恢复数据

The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all rows in a table. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs only the deallocation of whole data pages. TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.

As with DELETE, the definition of a table emptied using TRUNCATE TABLE remains in the database, along with its indexes and other associated objects. The DROP TABLE statement must be used to drop the definition of the table.

REF: http://bbs.csdn.net/topics/80111360