6.5.5 RENAME TABLE Syntax

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

The rename is done atomically, which means that no other thread can access any of the tables while the rename is running. This makes it possible to replace a table with an empty one :

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

The rename is done from left to right, which means that if you want to swap two table names, you have to:

RENAME TABLE old_table    TO backup_table,
             new_table    TO old_table,
             backup_table TO new_table;

As long as two databases are on the same disk you can also rename 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 will do a reverse rename for all renamed tables to get everything back to the original state.

RENAME TABLE was added in MySQL 3.23.23.

User Comments

In the following, the original table (db1.tbl1) is
removed during rename:
<tt>RENAME TABLE db1.tbl1 TO db2.tb1;</tt>
Therefore, RENAME TABLE does not allow you to
COPY a table from one database to another. Such a
COPY extension would be a handy feature. (Or does
someone know an existing method to copy a table
from one database to another?)

ADDENDUM: I figured out a way to
duplicate all
tables in one database to a different database. To
duplicate a database,
pipe 'mysqldump' to 'mysql.' Dump the original
table's structure as shown:
<tt>mysqldump --add-drop-table db1 | mysql
db2</tt>
If matching tables in db2 already exist, they get
dropped before being re-created via
<tt>--add-drop-table</tt>. This is a
good way to copy all tables from one database to
another on the same server.

<tt>mysqldump db1 tbl1 | sed
's/tbl1/tbl2/g' | mysql db2</tt>

Works nicely for table COPY.
db1 and db2 can be the same
Be warned that if the name of tbl1 appears
anywhere else (such as in the data) it will also
be substituted with this regex. Might not be what
you want.

To copy a table, you can always do:

CREATE TABLE y AS SELECT * FROM x;

However, indices will not be preserved.

Add your own comment.