Syntax\n------\n\nTRUNCATE [TABLE] tbl_name\n  [WAIT n | NOWAIT]\n\nDescription\n-----------\n\nTRUNCATE TABLE empties a table completely. It requires the DROP privilege. See GRANT.\n\ntbl_name can also be specified in the form db_name.tbl_name (see Identifier Qualifiers).\n\nLogically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.\n\nTRUNCATE TABLE will fail for an InnoDB table if any FOREIGN KEY constraints from other tables reference the table, returning the error:\n\n``sql\nERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint\n`\n\nForeign Key constraints between columns in the same table are permitted.\n\nFor an InnoDB table, if there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one. The AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a FOREIGN KEY constraint.\n\nThe count of rows affected by TRUNCATE TABLE is accurate only when it is mapped to a DELETE statement.\n\nFor other storage engines, TRUNCATE TABLE differs fromDELETE in the following ways:\n\n Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.\n Truncate operations cause an implicit commit.\n Truncation operations cannot be performed if the session holds an active table lock.\n 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."\n 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.\n The table handler does not remember the last used AUTO_INCREMENT value, but starts counting\\\n  from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.\n 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.\n Since truncation of a table does not make any use of DELETE, the TRUNCATE statement does not invoke ON DELETE triggers.\n* TRUNCATE TABLE will only reset the values in the Performance Schema summary tables to zero or null, and will not remove the rows.\n\nFor the purposes of binary logging and replication, TRUNCATE TABLE is treated as DROP TABLE followed by CREATE TABLE (DDL rather than DML).\n\nTRUNCATE TABLE does not work on views. Currently, TRUNCATE TABLE drops all historical records from a system-versioned table.\n\nWAIT/NOWAIT\n\nSet the lock wait timeout. See WAIT and NOWAIT.\n\nOracle-mode\n\nOracle-mode permits the optional keywords REUSE STORAGE or DROP STORAGE to be used.\n\n`sql\nTRUNCATE [TABLE] tbl_name [{DROP | REUSE} STORAGE] [WAIT n | NOWAIT]\n`\n\nThese have no effect on the operation.\n\nPerformance\n\nTRUNCATE TABLE is faster than DELETE, because it drops and re-creates a table.\n\nWith InnoDB, TRUNCATE TABLE is slower if innodb_file_per_table=ON is set (the default). This is because TRUNCATE TABLE unlinks the underlying tablespace file, which can be an expensive operation. See MDEV-8069 for more details.\n\nThe performance issues with innodb_file_per_table=ON can be exacerbated in cases where the InnoDB buffer pool is very large and innodb_adaptive_hash_index=ON is set. In that case, using DROP TABLE followed by CREATE TABLE instead of TRUNCATE TABLE may perform better. Setting innodb_adaptive_hash_index=OFF can also help.\n\nSetting innodb_adaptive_hash_index=OFF can also improve TRUNCATE TABLE` performance in general. See MDEV-16796 for more details.\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table