TRUNCATE TABLE

Description

You can use this statement to completely clear a specified table but retain the table structure, including the partition information defined in the table. Logically, this statement is equivalent to the DELETE FROM statement that is used to delete all rows. To execute the TRUNCATE TABLE statement, you must have the delete and create permissions for the table. It is a DDL statement.

The TRUNCATE TABLE statement differs from DELETE FROM in the following aspects:

• TRUNCATE TABLE deletes a table and recreates one, which is much faster than deleting rows one by one.

• The execution result of the TRUNCATE TABLE statement shows that 0 rows are affected.

• When you execute the TRUNCATE TABLE statement, the table management program does not record the last AUTO_INCREMENT value. Instead, the program resets the value to zero.

• The TRUNCATE TABLE statement cannot be executed during transactions or when the table is locked. Otherwise, an error is returned.

• If the table definition file is valid, you can use the TRUNCATE TABLE statement to recreate the table as an empty table, even if the data or indexes are corrupted.

Syntax

TRUNCATE [TABLE] table_name;

Parameters

Example

Completely clear Table tb1.

obclient> TRUNCATE TABLE tb1;