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 DELETE and CREATE privileges on the table.

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

• The TRUNCATE TABLE statement drops and recreates a table, which is much faster than row-by-row deletion, particularly for large tables.

• 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 reported.

• 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