RENAME TABLE

Purpose

This statement is used to rename tables and materialized views.

Limitations and considerations

• During the renaming of a table, the system automatically locks the process 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 operations are performed from left to right.

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

  Note

  After enabling the tenant-level parameter 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 them to other databases.

• During the RENAME TABLE operation, due to the addition of table locking and read/write protection measures, the operation time will be increased. 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 t1 and t2.

   obclient> CREATE TABLE t1(c1 INT);
   obclient> CREATE TABLE t2(c1 INT);
   

2. Rename table t1 to t11.

   obclient> RENAME TABLE t1 TO t11;
   

3. Rename table t11 to t111 and table t2 to t22.

   obclient> RENAME TABLE t11 TO t111, t2 TO t22;
   

4. Move table t22 to the mysql database.

   obclient> RENAME TABLE t22 TO mysql.t22;