TRUNCATE TABLE

Purpose

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

Limitations and considerations

• The TRUNCATE TABLE operation efficiently empties the 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 when dealing with large data tables, as it avoids row-level locking and logging.

• The execution result of the TRUNCATE TABLE statement always shows 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 transaction processing or table locking. If attempted, an error will be returned.

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

Privilege requirements

To execute the TRUNCATE TABLE statement, the current user must have the DROP privilege on the table. For more information about OceanBase Database privileges, see Privilege types in MySQL mode.

Syntax

TRUNCATE [TABLE] table_name;

Parameters

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
   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
   

References

Clear a table