TRUNCATE [TABLE] tbl_name [WAIT n | NOWAIT]
TRUNCATE TABLE
empties a table completely. It requires the DROP
privilege. See GRANT
.
tbl_name
can also be specified in the form db_name
.tbl_name
(see Identifier Qualifiers).
Logically, TRUNCATE TABLE
is equivalent to a DELETE
statement that deletes all rows, but there are practical differences under some circumstances.
TRUNCATE TABLE
will fail for an InnoDB table if any FOREIGN KEY constraints from other tables reference the table, returning the error:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
Foreign Key constraints between columns in the same table are permitted.
For 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.
The count of rows affected by TRUNCATE TABLE
is accurate only when it is mapped to a DELETE
statement.
For other storage engines, TRUNCATE TABLE
differs from DELETE
in the following ways:
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. AUTO_INCREMENT
value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values. 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. DELETE
, the TRUNCATE
statement does not invoke ON DELETE
triggers. TRUNCATE TABLE
will only reset the values in the Performance Schema summary tables to zero or null, and will not remove the rows. For the purposes of binary logging and replication, TRUNCATE TABLE
is treated as DROP TABLE
followed by CREATE TABLE
(DDL rather than DML).
TRUNCATE TABLE
does not work on views. Currently, TRUNCATE TABLE
drops all historical records from a system-versioned table.
Set the lock wait timeout. See WAIT and NOWAIT.
Oracle-mode from MariaDB 10.3 permits the optional keywords REUSE STORAGE or DROP STORAGE to be used.
TRUNCATE [TABLE] tbl_name [{DROP | REUSE} STORAGE]
These have no effect on the operation.
TRUNCATE TABLE
is faster than DELETE, because it drops and re-creates a table.
With XtraDB/InnoDB, TRUNCATE TABLE
is slower if innodb_file_per_table=ON is set (the default since MariaDB 5.5). This is because TRUNCATE TABLE
unlinks the underlying tablespace file, which can be an expensive operation. See MDEV-8069 for more details.
The 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. In MariaDB 10.2.19 and later, this performance can also be improved by setting innodb_safe_truncate=OFF. See MDEV-9459 for more details.
Setting innodb_adaptive_hash_index=OFF can also improve TRUNCATE TABLE
performance in general. See MDEV-16796 for more details.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/truncate-table/