13.1.10. RENAME TABLE Syntax
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
This statement renames one or more tables.
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 (assuming that backup_table does not
already exist):
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
tmp_table does not already exist):
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 use
RENAME TABLE to move a table from one
database to another:
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
RENAME TABLE also works for views, as long as
you do not try to rename a view into a different database.
When you execute RENAME, you cannot 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 return
everything to its original state.
