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, a table named
old_table can be
new_table as shown here:
RENAME TABLE old_table TO new_table;
This statement is equivalent to the following
ALTER TABLE statement:
ALTER TABLE old_table RENAME new_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;
MySQL checks the destination table name before checking whether
the source table exists. For example, if
new_table already exists and
old_table does not, the following statement
fails as shown here:
SHOW TABLES;+----------------+ | Tables_in_mydb | +----------------+ | table_a | +----------------+ 1 row in set (0.00 sec) mysql>
RENAME TABLE table_b TO table_a;ERROR 1050 (42S01): Table 'table_a' already exists
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:
You can use this method to move all tables from one database to a different one, in effect renaming the database. (MySQL 5.1.7 through 5.1.23 had a RENAME DATABASE statement, but this statement was not always safe to use, and was subsequently removed.)
If there are any triggers associated with a table which is moved
to a different database using
then the statement fails with the error Trigger in
Foreign keys that point to the renamed table are not automatically updated. In such cases, you must drop and re-create the foreign keys in order for them to function properly.
RENAME TABLE 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 TABLE, 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 TABLE to rename a
TEMPORARY table. However, you can use
ALTER TABLE with temporary tables.
ALTER TABLE ...
RENAME can also be used to move a table to a different
database. Regardless of the statement used to perform the rename,
if the rename operation would move the table to a database located
on a different file system, the success of the outcome is platform
specific and depends on the underlying operating system calls used
to move the table files.