You can execute the TRUNCATE TABLE statement to delete all data in a specified table and retain the table schema, including the partition information of the table. The TRUNCATE TABLE statement implements the same logic as the DELETE FROM statement. You can use DELETE FROM to delete all rows. To execute the TRUNCATE TABLE statement, ensure that you have the permissions to create and delete tables. The TRUNCATE TABLE statement is a Data Definition Language (DDL) statement.
The TRUNCATE TABLE and DELETE FROM statements have the following differences:
- The TRUNCATE TABLE statement deletes and creates the table again, whereas the DELETE FROM statement deletes rows one after another. Therefore, The response time for the TRUNCATE TABLE statement is shorter than that for the DELETE FROM statement.
- The output of the TRUNCATE TABLE statement shows that the number of affected rows is always 0.
- If you execute the TRUNCATE TABLE statement, each auto-incremented value is reset to the start value. The table manager does not store the latest auto-incremented value.
- You cannot execute the TRUNCATE TABLE statement when a transaction is being processed or the table is locked. If you execute the statement in these scenarios, the system returns errors.
- If the file that defines the table is valid, you can execute the TRUNCATE TABLE statement to create the table again as an empty table. This applies even if the data or the index file is corrupted.
TRUNCATE [TABLE] table_name;
The name of the table.
Remove all records from the tb1 table.
TRUNCATE [TABLE] tb1;