MySQL Reference Manual :: 13.1.9 RENAME TABLE Syntax

       RENAME TABLE tbl_name TO new_tbl_name
       [, tbl_name2 TO new_tbl_name2] ...
     

This statement renames one or more tables. It was added in MySQL 3.23.23.

The rename operation is done atomically, which means that no other thread can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one as follows:

       CREATE TABLE new_table (...);
       RENAME TABLE old_table TO backup_table, new_table TO old_table;
     

If the statement renames more than one table, renaming operations are done from left to right. If you want to swap two table names, you can do so like this (assuming that no table named tmp_table currently exists):

       RENAME TABLE old_table TO tmp_table,
       new_table TO old_table,
       tmp_table TO new_table;
     

As long as two databases are on the same filesystem you can also rename a table to move it from one database to another:

       RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
     

When you execute RENAME, you can't have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

If MySQL encounters any errors in a multiple-table rename, it does a reverse rename for all renamed tables to get everything back to the original state.