Purpose
This statement is used to completely clear a specified table while retaining its structure, including the partition information defined in the table. Logically, this statement is equivalent to a DELETE FROM statement that deletes all rows.
Limitations and considerations
The
TRUNCATE TABLEstatement efficiently clears a table by deleting all its data and resetting the table's metadata. This method offers significant performance advantages over row-by-row deletion using theDELETE FROMstatement, especially for large data tables, as it eliminates the need for row-level locking and logging.The number of rows affected by a
TRUNCATE TABLEstatement is always displayed as0rows.When you use the
TRUNCATE TABLEstatement, the table manager does not remember the last usedAUTO_INCREMENTvalue but restarts the count from zero.The
TRUNCATEstatement cannot be executed during transaction processing or when a table is locked. Using it in such situations will result in an error.As long as the table definition file is valid, you can use
TRUNCATE TABLEto recreate a 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 AI Database privileges, see Permission classification.
Syntax
TRUNCATE [TABLE] table_name;
Parameters
Parameters |
Description |
|---|---|
| table_name | Specify the table name. |
Examples
Create the table
test_tbl1.CREATE TABLE test_tbl1(col1 INT AUTO_INCREMENT PRIMARY KEY, col2 VARCHAR(20)) PARTITION BY HASH(col1) PARTITIONS 5;Insert test data into the
test_tbl1table.INSERT INTO test_tbl1(col2) VALUES('A1'),('A2'),('A3');The return result is as follows:
Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0Completely truncate the
test_tbl1table.TRUNCATE TABLE test_tbl1;Query the data in the
test_tbl1table.SELECT * FROM test_tbl1;The result is as follows:
Empty setQuery the definition of the
test_tbl1table.SHOW CREATE TABLE test_tbl1;The 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