TRUNCATE [TABLE] tbl_name
Truncate table is used to completely empty a TABLE. Logically, this statement is equivalent to the DELETE statement used to DELETE all rows, but in some cases, the two are used differently. For an InnoDB TABLE, if there is a foreign key restriction for the TABLE to be referenced, The truncate table is mapped to the delete table; otherwise, you can use Quick DELETE (cancel and recreate the TABLE ). Use truncate table to reset the AUTO_INCREMENT counter. When setting the counter, do not consider whether there is a foreign key restriction.
For other storage engines, in MySQL 5.1, The truncate table and delete from have the following differences:
TRUNCATE cannot use where to delete a specific record, but delete from can. The delete operation will cancel and re-create the table, which is much faster than deleting one row. The delete operation cannot guarantee the security of transactions. An error occurs when you try to delete a transaction during transaction processing and table locking. The number of deleted rows is not returned. As long as the TABLE definition file tbl_name.frm is valid, you can use truncate table to recreate the TABLE as an empty TABLE, even if the data or index file has been damaged. The table manager does not remember the AUTO_INCREMENT value that was last used, but starts counting from the beginning. Even for MyISAM and InnoDB. MyISAM and InnoDB generally do not use sequence values again. When used for a TABLE with partitions, The truncate table retains partitions. That is, the data and index files are canceled and re-created, and the partition definition (. par) files are not affected.