:: DEVELOPER ZONE
MySQL Reference Manual :: 13.2.9 TRUNCATE Syntax
Search the MySQL manual:
- 13.2 Data Manipulation Statements
- 13.2.1 DELETE Syntax
- 13.2.2 DO Syntax
- 13.2.3 HANDLER Syntax
- 13.2.4 INSERT Syntax
- 13.2.5 LOAD DATA INFILE Syntax
- 13.2.6 REPLACE Syntax
- 13.2.7 SELECT Syntax
- 13.2.8 Subquery Syntax
- 13.2.9 TRUNCATE Syntax
- 13.2.10 UPDATE Syntax
Additional languages
Additional formats
TRUNCATE TABLE tbl_name
TRUNCATE TABLE empties a table completely.
Logically, this is equivalent to a DELETE
statement that deletes all rows, but there are practical differences
under some circumstances.
For InnoDB before version 5.0.3,
TRUNCATE TABLE is mapped to
DELETE, so there is no difference. Starting with
MySQL/InnoDB-5.0.3, fast TRUNCATE TABLE is
available. The operation is still mapped to
DELETE if there are foreign key constraints that
reference the table.
For other storage engines, TRUNCATE TABLE differs
from DELETE FROM in the following ways from MySQL
4.0 and up:
Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.
Truncate operations are not transaction-safe; you get an error if you have an active transaction or an active table lock.
The number of deleted rows is not returned.
As long as the table definition file
tbl_name.frmTRUNCATE TABLE, even if the data or index files have become corrupted.The table handler does not remember the last used
AUTO_INCREMENTvalue, but starts counting from the beginning. This is true even forMyISAMandInnoDB, which normally does not reuse sequence values.
In MySQL 3.23, TRUNCATE TABLE is mapped to
COMMIT; DELETE FROM
, so it behaves like
tbl_nameDELETE. See Section 13.2.1, “DELETE Syntax”.
TRUNCATE TABLE is an Oracle SQL extension. This
statement was added in MySQL 3.23.28, although from 3.23.28 to
3.23.32, the keyword TABLE must be omitted.
© 1995-2005 MySQL AB. All rights reserved.
User Comments
Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245
Add your own comment.