Syntax
------ 
TRUNCATE [TABLE] tbl_name
 [WAIT n | NOWAIT]
 
Description
----------- 
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:
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.
Truncation operations cannot be performed if the session
holds an
 active table lock.
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.
The table handler does not remember the last
 used AUTO_INCREMENT value, but starts counting
 from the beginning. 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.
Since truncation of a table does not make any use of 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.
 
WAIT/NOWAIT
 
Set the lock wait timeout. See WAIT and NOWAIT.
 
Oracle-mode
 
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.
 
Performance
 
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.
 


URL: https://mariadb.com/kb/en/truncate-table/