RENAME TABLE

Description

This statement is used to rename one or more tables.

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 should be no locked tables and no active transactions.

  info

  After enabling the enable_lock_priority configuration item, RENAME TABLE has the highest lock priority.

• RENAME TABLE can be used for views but does not support moving them to other databases.

• During the RENAME TABLE operation, table locking and read/write defense measures are added, which will increase the operation time. To avoid affecting other users' DDL operations, it is recommended not to perform batch RENAME TABLE operations.

Privileges

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

Parameter	Description
table_name	The original table name.
new_table_name	The new table name.
table_name TO [new_database_name.]new_table_name	When renaming multiple tables, separate them with commas (,). You can specify new_database_name to move the table to another database.

Examples

1. Create tables t1 and t2.

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

2. Rename table t1 to t11.

   RENAME TABLE t1 TO t11;

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

   RENAME TABLE t11 TO t111, t2 TO t22;

4. Move table t22 to the mysql database.

   RENAME TABLE t22 TO mysql.t22;