Description
This statement is used to rename a table or materialized view.
Limitations and considerations
During a table rename, the system automatically locks the process and prevents any other threads from reading these tables until the rename is complete.
If there are ongoing active transactions on the target table, the renaming operation will wait. The table cannot be renamed until all active transactions are either committed or rolled back.
If this statement is used to rename multiple tables, the renaming operation is performed from left to right.
No tables can be locked, and no active transactions are allowed when executing the
RENAME TABLEstatement.Note
After the tenant-level parameter enable_lock_priority is enabled,
RENAME TABLEoperations have the highest lock priority.The
RENAME TABLEstatement can be used for views, but views cannot be moved to another database.The
RENAME TABLEoperation takes longer due to table locking and read/write prevention measures. To avoid impacting other users' DDL operations, it is recommended not to performRENAME TABLEoperations in batches.
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
Parameters |
Description |
|---|---|
| table_name | 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
Create the
order_infoandproduct_infotables.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));Rename the
order_infotable toorder_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 | +------------------+Rename the
order_tabletoorder_detailand theproduct_infotoproduct_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 | +------------------+Move the
order_detailtable to themysqldatabase.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 | +------------------+