Skip to content

TRUNCATE TABLE

Grammar Description

The TRUNCATE TABLE statement is used to delete all rows in a table without recording a single row deletion operation. TRUNCATE TABLE is similar to the DELETE statement without the WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

TRUNCATE TABLE has the following characteristics:

  • TRUNCATE TABLE After deletion, it cannot be restored.

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

  • If the table has any foreign key constraints (FOREIGN KEY), the TRUNCATE TABLE statement deletes rows one by one.

  • If the table does not have any foreign key constraints (FOREIGN KEY), the TRUNCATE TABLE statement will delete the table and recreate a new table 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 when you want to delete all records but still keep the table.
  • DELETE TABLE: When you want to delete a partial record, use DELETE TABLE.

Grammar Structure

> TRUNCATE [TABLE] table_name;

Syntax interpretation

TABLE

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

Example

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)