About table clearing

OceanBase Database in MySQL mode allows you to clear a table by using the DELETE or TRUNCATE statement.

Prerequisites

The current user has the DELETE privilege for the target table.

DELETE statement

Description

You can use this statement to delete rows that meet the conditions from one or more tables.

Syntax

Single-Table-Delete Syntax:
    DELETE [hint_options] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY order_expression_list]
    [LIMIT row_count]

Multiple-Table-Delete Syntax:
    DELETE [hint_options] tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]
Or:
    DELETE [hint_options] FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]
 
where_condition:
    expression

order_expression_list:
    order_expression [, order_expression ...]

order_expression:
    expression [ASC | DESC]

limit_row_count:
    INT_VALUE
  
table_references:
    {tbl_name | joined_table | table_subquery | select_with_parens} [, ...]
 

Parameters

Example

Clear the data in the dws_ny table.

1. Query the data in the dws_ny table.

   obclient> SELECT * FROM dws_ny;
   +----+--------+------------+
   | id | name   | date       |
   +----+--------+------------+
   |  1 | Zhang San   | 0000-00-00 |
   |  2 | Li Si   | 0000-00-00 |
   +----+--------+------------+
   2 rows in set
   

2. Clear the data in the dws_ny table.

   obclient> DELETE FROM dws_ny;
   Query OK, 2 rows affected
   

3. Query the data in the dws_ny table again.

   obclient> SELECT * FROM dws_ny;
   Empty set
   

TRUNCATE statement

Description

You can use this statement to completely clear a specified table but retain the table structure, including the partitions defined in the table. Logically, this statement is equivalent to the DELETE FROM statement that is used to delete all rows. To execute the TRUNCATE TABLE statement, you must have the DROP and CREATE privileges on the table. It is a DDL statement.

The TRUNCATE TABLE statement differs from the DELETE FROM statement in the following aspects:

• TRUNCATE TABLE deletes a table and recreates one, which is much faster than deleting rows one by one.

• The execution result of the TRUNCATE TABLE statement shows that 0 rows are affected.

• When you execute the TRUNCATE TABLE statement, the table management program does not record the last AUTO_INCREMENT value. Instead, the program resets the value to zero.

• The TRUNCATE TABLE statement cannot be executed during transactions or when the table is locked. Otherwise, an error is returned.

• If the table definition file is valid, you can use the TRUNCATE TABLE statement to recreate the table as an empty table, even if the data or indexes are corrupted.

Syntax

TRUNCATE [TABLE] table_name;

Parameters

Example

Clear the data in the t1 table.

1. Query the data in the t1 table.

   obclient>  SELECT * FROM t1;
   +----+--------+------+
   | id | name   | age  |
   +----+--------+------+
   |  1 | Zhang San   |   22 |
   |  2 | Li Si   |   33 |
   +----+--------+------+
   2 rows in set
   

2. Clear the data in the t1 table.

   obclient> TRUNCATE t1;
   Query OK, 0 rows affected
   

3. Query the data in the t1 table again.

   obclient>  SELECT * FROM t1;
   Empty set