Rename multiple tables in MySQL

Posted January 10th, 2009 in MySql

Some time back I posted how to rename a table in MySQL, either using a SQL query or using phpMyAdmin. In this post I will show how to rename multiple MySQL tables from a single query, including how you would swap two table names. Note that it is not generally advised to do this sort of thing on busy production servers.

As a recap, to rename a table from e.g. "products" to "products_old" run this SQL query:

RENAME TABLE products TO products_old

To rename more than one table at once, simply comma separate the tables to be renamed as in the following example, where we rename the example tables "products" and "categories" to "products_old" and "categories_old":

RENAME TABLE
  products TO products_old,
  categories TO categories_old

If you wanted to switch the table names of two tables you can also do this in a single query. The renaming is done from left to right in sequence, so you'd rename the first table to a temporary name, the second table to the first table's name and then the temporary table to the original name. Obviously it's not going to work if the temporary name is already being used.

The following example demonstrates this, where we rename "products" to "products_bak", and the existing "products_bak" to "products":

RENAME TABLE
  products TO products_tmp,
  products_bak TO products,
  products_tmp TO products_bak

Note as I mentioned at the top of this post it's not something you'd want to do on a busy production website, unless you take the site down temporarily first, as per the following note from the MySQL documentation: "The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running".