Use TRUNCATE TABLE to clear data from tables and partitions - AnalyticDB

You can execute the TRUNCATE TABLE statement to delete all data from a table or from specific partitions in a table in an AnalyticDB for MySQL cluster. The table schema is preserved.

Syntax

Delete all data from a table:
```
  TRUNCATE TABLE db_name.table_name;
```

Delete data from specific partitions in a table:

  TRUNCATE TABLE db_name.table_name PARTITION partition_name[,...];

Parameters

Parameter	Description
db_name	The name of the database that contains the table you want to truncate.
table_name	The name of the table you want to truncate.
partition_name	The name of the partition you want to truncate. In AnalyticDB for MySQL, partition names are BIGINT values. You can specify multiple partition names separated by commas. To query partition names, see Query partition names.

Query partition names

To query the names of all partitions in a table, execute the following statement:

SELECT partition_name FROM information_schema.partitions WHERE table_name = 'your_table_name' ORDER BY partition_name DESC LIMIT 100;

Usage notes

When a database backup is in progress, the TRUNCATE TABLE statement cannot be executed. An error is returned if you execute this statement during a backup.
If you execute the TRUNCATE TABLE statement when your AnalyticDB for MySQL cluster has a BUILD task (a background data organization process) in the running state, an error is returned. You must wait for the BUILD task to complete and then execute the statement again. For more information about how to query the state of a BUILD task, see View the state of a BUILD task.

Examples

Delete all data from the customer table in the adb_demo database:
```
  TRUNCATE TABLE adb_demo.customer;
```
Delete data from partitions 20170103, 20170104, and 20170108 in the customer table in the adb_demo database:
```
  TRUNCATE TABLE adb_demo.customer PARTITION 20170103,20170104,20170108;
```