页面导航:首页 > 数据库 > Mysql > MySQL的TRUNCATE使用


来源: 作者: 时间:2016-02-02 08:56 【

TRUNCATE [TABLE] tbl_nameTRUNCATE TABLE用于完全清空一个表。从逻辑上说,该语句与用于删除所有行的DELETE语句等同,但是在有些情况下,两者在使用上有所不同。对于InnoDB表,如果有需要引用



TRUNCATE TABLE empties a table completely. It requires the DROP privilege.

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL(定义语言) statement rather than a DML(数据操作语言) statement. It differs from DELETE in the following ways in MySQL 5.7:

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

  • Truncate operations cause an implicit(隐式) commit, and so cannot be rolled back.

  • Truncation operations cannot be performed if the session holds an active table lock.

  • TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

  • Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”

  • As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted(败坏的).

  • 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.

  • The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE—that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not permit statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode. (Bug #36763) However, it is still applied on replication slaves using InnoDB in the manner described previously.

TRUNCATE TABLE can be used with Performance Schema summary tables, but the effect is to reset the summary columns to 0 or NULL, not to remove rows. 



TRUNCATE t_parent;


[TRUNCATE - 0 row(s), 0.000 secs]  [Error Code: 1701, SQL State: 42000]  Cannot truncate a table referenced in a foreign key constraint (`test`.`t_child`, CONSTRAINT `FK_t_child` FOREIGN KEY (`parent_id`) REFERENCES `test`.`t_parent` (`id`))

Code: 1701 SQL State: HY000 --- Cannot truncate a table referenced in a foreign key constraint (`test`.`t_child`, CONSTRAINT `FK_t_child` FOREIGN KEY (`parent_id`) REFERENCES `test`.`t_parent` (`id`))

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]


TRUNCATE t_parent;
select count(*) from t_parent;




最 近 更 新
热 点 排 行