GNU Info

(mysql.info)RENAME TABLE

---

Next: DROP TABLE Prev: ALTER TABLE Up: Data Definition

---

Enter node , (file) or (file)node

`RENAME TABLE' Syntax
---------------------

     RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_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 tables 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_database.table_name TO other_database.table_name;

When you execute `RENAME', you can't have any locked tables or active
transactions.  You must also have the `ALTER' and `DROP' privilege on
the original table and `CREATE' and `INSERT' privilege 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.