This statement renames one or more tables.
The rename operation is done atomically, which means that no other
session 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
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 file system, you can use
RENAME TABLE to move a table from
one database to another:
Beginning with MySQL 5.0.2, if there are any triggers associated
with a table which is moved to a different database using
RENAME TABLE, then the statement
fails with the error Trigger in wrong
As of MySQL 5.0.14,
also works for views, as long as you do not try to rename a view
into a different database.
Any privileges granted specifically for the renamed table or view are not migrated to the new name. They must be changed manually.
When you execute
RENAME, you cannot have any
locked tables or active transactions. You must also have the
DROP privileges on the original
table, and the
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.
You cannot use
RENAME to rename a
TEMPORARY table. However, you can use
ALTER TABLE instead:
ALTER TABLE orig_name RENAME new_name;
If the rename operation would move the table to a database located on a different file system, outcome success is platform specific and depends on the underlying operating system calls used to move table files.