PURGE

Description

This statement is used to delete database objects from the recycle bin, including databases, tables, and indexes.

Syntax

 PURGE {DATABASE | TABLE | INDEX} object_name;

object_name:
    database_name
  | table_name
  | index_name

Parameters

Parameter	Description
database_name	The name of the database to be deleted in the recycle bin. You cannot directly specify the name of the database.
table_name	The name of the table to be deleted in the recycle bin. You cannot directly specify the name of the table.
index_name	The name of the index to be deleted in the recycle bin. You cannot directly specify the name of the index.

Examples

• Delete the database __recycle_$_1_1597384386029184 from the recycle bin.

  CREATE DATABASE db1;
  Query OK, 1 row affected (0.410 sec)
  
  DROP DATABASE db1;
  Query OK, 0 rows affected (0.430 sec)
  
  SHOW RECYCLEBIN;
  +--------------------------------+---------------+----------+----------------------------+
  | OBJECT_NAME                    | ORIGINAL_NAME | TYPE     | CREATETIME                 |
  +--------------------------------+---------------+----------+----------------------------+
  | __recycle_$_1_1597384386029184 | db1           | DATABASE | 2020-08-14 13:53:06.029367 |
  +--------------------------------+---------------+----------+----------------------------+
  1 row in set (0.058 sec)
  
  PURGE DATABASE __recycle_$_1_1597384386029184;
  Query OK, 0 rows affected (0.470 sec)
  
  SHOW RECYCLEBIN;
  Empty set (0.068 sec)

• Delete the table __recycle_$_1_1099511628776_1099511677778 from the recycle bin.

  CREATE TABLE test(c1 INT);
  Query OK, 0 rows affected (0.351 sec)
  
  DROP TABLE test;
  Query OK, 0 rows affected (0.411 sec)
  
  SHOW RECYCLEBIN;
  +-------------------------------------------+---------------+-------+----------------------------+
  | OBJECT_NAME                               | ORIGINAL_NAME | TYPE  | CREATETIME                 |
  +-------------------------------------------+---------------+-------+----------------------------+
  | __recycle_$_1_1099511628776_1099511677778 | test          | TABLE | 2017-10-20 17:40:22.304025 |
  +-------------------------------------------+---------------+-------+----------------------------+
  1 row in set (0.001 sec)
  
  PURGE TABLE __recycle_$_1_1099511628776_1099511677778;
  Query OK, 0 rows affected (0.415 sec)
  
  SHOW RECYCLEBIN;
  Empty set

• Delete the index __recycle_$_1_1597387726700872 from the recycle bin.

  CREATE TABLE t1(c1 INT);
  Query OK, 0 rows affected (0.315 sec)
  
  CREATE INDEX idx ON t1(c1);
  Query OK, 0 rows affected (0.319 sec)
  
  DROP TABLE t1;
  Query OK, 0 rows affected (0.115 sec)
  
  SHOW RECYCLEBIN;
  +--------------------------------+----------------------------+-------+----------------------------+
  | OBJECT_NAME                    | ORIGINAL_NAME              | TYPE  | CREATETIME                 |
  +--------------------------------+----------------------------+-------+----------------------------+
  | __recycle_$_1_1597387726700872 | __idx_1101710651081557_idx | INDEX | 2020-08-14 14:48:46.699145 |
  | __recycle_$_1_1597387726712976 | t1                         | TABLE | 2020-08-14 14:48:46.712643 |
  +--------------------------------+----------------------------+-------+----------------------------+
  2 rows in set (0.071 sec)
  
  PURGE INDEX __recycle_$_1_1597387726700872;
  Query OK, 0 rows affected (0.215 sec)
  
  SHOW RECYCLEBIN;
  +--------------------------------+----------------------------+-------+----------------------------+
  | OBJECT_NAME                    | ORIGINAL_NAME              | TYPE  | CREATETIME                 |
  +--------------------------------+----------------------------+-------+----------------------------+
  | __recycle_$_1_1597387726700872 | __idx_1101710651081557_idx | INDEX | 2020-08-14 14:48:46.699145 |
  +--------------------------------+----------------------------+-------+----------------------------+
  1 rows in set (0.091 sec)