RENAME TABLE

Purpose

This statement is used to rename tables and materialized views.

Limitations and considerations

• During the table renaming process, the system automatically locks the tables and prevents any other threads from reading these tables until the renaming is complete.

• If there are ongoing active transactions on the target table, the renaming operation will wait. The renaming process cannot proceed until all active transactions are either committed or rolled back.

• If this statement is used to rename multiple tables, the renaming operation proceeds from left to right.

• When executing RENAME TABLE, there must be no locked tables and no active transactions.

  Note

  After enabling the tenant-level configuration item enable_lock_priority, the RENAME TABLE statement has the highest lock priority.

• RENAME TABLE can be used for views, but it does not support moving the view to another database.

• During the RENAME TABLE operation, table locking and read/write protection measures are implemented, which may increase the operation's duration. To avoid affecting other users' DDL operations, it is recommended not to perform batch RENAME TABLE operations.

Privilege requirements

This operation requires the ALTER and DROP privileges on the original table, as well as the CREATE and INSERT privileges on the new table.

Syntax

RENAME TABLE table_name TO [new_database_name.]new_table_name
    [, table_name2 TO [new_database_name.]new_table_name2 ...];

Parameters

Examples

1. Create tables order_info and product_info.

   obclient> CREATE TABLE order_info(order_id INT PRIMARY KEY, customer_name VARCHAR(50));
   obclient> CREATE TABLE product_info(product_id INT PRIMARY KEY, product_name VARCHAR(50));
   

2. Rename the order_info table to order_table.

   obclient> RENAME TABLE order_info TO order_table;
   

   The query result is as follows:

   obclient> SHOW TABLES;
   +------------------+
   | Tables_in_test   |
   +------------------+
   | order_table      |
   | product_info     |
   +------------------+
   

3. Rename the order_table table to order_detail and the product_info table to product_detail.

   obclient> RENAME TABLE order_table TO order_detail, product_info TO product_detail;
   

   The query result is as follows:

   obclient> SHOW TABLES;
   +------------------+
   | Tables_in_test   |
   +------------------+
   | order_detail     |
   | product_detail   |
   +------------------+
   

4. Move the order_detail table to the mysql database.

   obclient> RENAME TABLE order_detail TO mysql.order_detail;
   

   The query result is as follows:

   obclient> SHOW TABLES FROM mysql LIKE 'order_detail';
   +------------------+
   | Tables_in_mysql  |
   +------------------+
   | order_detail     |
   +------------------+