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 privilege on the table. If you are not the owner of the table, you must have the DROP ANY TABLE privilege.
The TRUNCATE TABLE statement differs from the DELETE FROM statement in the following aspects:
The
TRUNCATE TABLEoperation 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 TABLEalways indicates that 0 rows were affected.When you use
TRUNCATE TABLE, the table management program does not record the lastAUTO_INCREMENTvalue, but resets it to zero.The
TRUNCATE TABLEstatement 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 TABLEstatement to recreate the table as an empty table, even if the data or indexes are corrupted.
Syntax
TRUNCATE [TABLE] [schema.]table_name;
Parameters
| Parameter | Description |
|---|---|
| schema. | The schema. If schema. is omitted, the table is in the current schema by default. |
| table_name | The table name. |
Examples
Truncate the tb1 table.
obclient> TRUNCATE TABLE tbl1;
Query OK, 0 rows affected