TRUNCATE TABLE

Purpose

You can use this statement to truncate a specified table but retain the table structure, including the partitions of 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 DROP and CREATE privileges on the table.

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

• The TRUNCATE TABLE operation drops a table and creates it again. It is much faster than deleting data row by row, especially for large tables.

• The output of TRUNCATE TABLE always indicates that 0 rows were affected.

• When you use TRUNCATE TABLE, the table management program does not record the last AUTO_INCREMENT value, but resets it 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] [schema.]table_name;

Parameters

Examples

Truncate the tb1 table.

obclient> TRUNCATE TABLE tbl1;
Query OK, 0 rows affected