TRUNCATE TABLE

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:

• It cannot be restored after the TRUNCATE TABLE is deleted.
• If the table has an AUTO_INCREMENT column, the TRUNCATE TABLE statement resets the auto-increment value to zero.
• The TRUNCATE TABLE statement deletes rows individually if the table has FOREIGN KEY constraints.
• If the table does not have any FOREIGN KEY constraints, the TRUNCATE TABLE statement will drop the table and recreate 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 TABLE: When you want to delete some records, use DELETE TABLE.

Syntax

1. > TRUNCATE [TABLE] table_name;

Explanations

TABLE

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

Examples

1. 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));
2. insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12');
3. mysql> select * from index_table_05;
4. +------+-------+------+------+
5. | col1 | col2  | col3 | col4 |
6. +------+-------+------+------+
7. |    1 | apple |    1 | 10   |
8. |    2 | store |    2 | 11   |
9. |    3 | bread |    3 | 12   |
10. +------+-------+------+------+
11. 3 rows in set (0.00 sec)

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

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