TRUNCATE TABLE
Syntax
TRUNCATE [TABLE] tbl_name
Description
TRUNCATE TABLE
empties a table completely. It requires the
DROP
privilege as of MySQL 5.1.16. (Before 5.1.16, it requires
the DELETE
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
is faster than DELETE
, because it drops and re-creates a table. With XtraDB/InnoDB, this is faster if the innodb_file_per_table
variable is ON.
For an InnoDB table, InnoDB processes TRUNCATE TABLE
by
deleting rows one by one if there are any FOREIGN KEY
constraints that reference the 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.
In the case that FOREIGN KEY
constraints reference the table,
InnoDB deletes rows one by one and processes the constraints on each one. If
the FOREIGN KEY
constraint specifies
DELETE CASCADE
, rows from the child (referenced) table are
deleted, and the truncated table becomes empty. If the
FOREIGN KEY
constraint does not specify
CASCADE
, the TRUNCATE
statement deletes rows
one by one and stops if it encounters a parent row that is referenced by the
child, returning this error:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
This is the same as a DELETE
statement with no
WHERE
clause.
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 in MariaDB 5.1:
- 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 withTRUNCATE 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
, theTRUNCATE
statement does not invokeON DELETE
triggers.
TRUNCATE TABLE
does not work on views.