TRUNCATE TABLE

Description

This statement is used to completely empty a specified table while retaining its structure, including the partition information defined in the table. Logically, this statement is equivalent to the DELETE FROM statement for deleting all rows.

Limitations and Considerations

• The TRUNCATE TABLE operation efficiently empties a table by deleting all data and resetting the table's metadata. This method offers significant performance advantages over row-by-row deletion (using the DELETE FROM statement), especially for large tables, as it avoids row-level locking and logging.

• The execution result of the TRUNCATE TABLE statement will always show 0 rows affected.

• When using the TRUNCATE TABLE statement, the table management program does not retain the last used AUTO_INCREMENT value and will start counting from the beginning.

• The TRUNCATE statement cannot be executed during a transaction or table lock. If it is used, an error will be returned.

• As long as the table definition file is valid, the TRUNCATE TABLE statement can be used to recreate the table as an empty table, even if the data or index files are damaged.

Privilege Requirements

To execute the TRUNCATE TABLE statement, the current user must have the DROP privilege on the table. For more information about seekdb privileges, see seekdb Privilege Types.

Syntax

TRUNCATE [TABLE] table_name;

Parameter Description

Parameter	Description
table_name	Specifies the table name.

Examples

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1(col1 INT AUTO_INCREMENT PRIMARY KEY, col2 VARCHAR(20))
       PARTITION BY HASH(col1) PARTITIONS 5;

2. Insert test data into the test_tbl1 table.

   INSERT INTO test_tbl1(col2) VALUES('A1'),('A2'),('A3');

   The returned result is as follows:

   Query OK, 3 rows affected (0.001 sec)
   Records: 3  Duplicates: 0  Warnings: 0

3. Completely empty the test_tbl1 table.

   TRUNCATE TABLE test_tbl1;

4. View the data in the test_tbl1 table.

   SELECT * FROM test_tbl1;

   The returned result is as follows:

   Empty set

5. View the definition of the test_tbl1 table.

   SHOW CREATE TABLE test_tbl1;

   The returned result is as follows:

   +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
   +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | test_tbl1 | CREATE TABLE `test_tbl1` (
   `col1` int(11) NOT NULL AUTO_INCREMENT,
   `col2` varchar(20) DEFAULT NULL,
   PRIMARY KEY (`col1`)
   ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1)
   (partition `p0`,
   partition `p1`,
   partition `p2`,
   partition `p3`,
   partition `p4`) |
   +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.021 sec)

References

Empty a Table