TRUNCATE TABLE

The TRUNCATE TABLE statement deletes all rows in a table without logging individual row deletions.

Description

The TRUNCATE TABLE statement deletes all rows in a table without logging individual row deletions. TRUNCATE TABLE is similar to a DELETE statement without a WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

TRUNCATE TABLE has the following characteristics:

  • TRUNCATE TABLE clears the current table. If a usable snapshot was created beforehand, the old data can still be read or restored from that snapshot.

  • If the table has an AUTO_INCREMENT column, the TRUNCATE TABLE statement resets the auto-increment value to zero.

  • If the table is referenced by a FOREIGN KEY constraint from another table, the TRUNCATE TABLE statement returns an error.

  • For a table that can be truncated, the TRUNCATE TABLE statement drops the table and recreates a new one with the same structure.

The difference between DROP TABLE, TRUNCATE TABLE, and DELETE TABLE:

  • DROP TABLE: Use DROP TABLE when you no longer need the table.

  • TRUNCATE TABLE: Use TRUNCATE TABLE to keep the table, but delete all records.

  • DELETE FROM table_name ... WHERE ...: When you want to delete some records, use DELETE FROM with a WHERE clause to select the records.

Syntax

> TRUNCATE [TABLE] table_name;

Explanations

TABLE

The TABLE keyword is optional. Use this to distinguish the TRUNCATE TABLE statement from the TRUNCATE function.

Examples

create table index_table_05 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key col2(col2),key num_id(col4));
insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12');
mysql> select * from index_table_05;
+------+-------+------+------+
| col1 | col2  | col3 | col4 |
+------+-------+------+------+
|    1 | apple |    1 | 10   |
|    2 | store |    2 | 11   |
|    3 | bread |    3 | 12   |
+------+-------+------+------+
3 rows in set (0.00 sec)

mysql> truncate table index_table_05;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from index_table_05;
Empty set (0.03 sec)