Renaming a Table

8.7.1 Problem

A table needs to be renamed.

8.7.2 Solution

You can use ALTER TABLE or RENAME TABLE for this.

8.7.3 Discussion

To rename a table, use the RENAME option of the ALTER TABLE statement:

ALTER TABLE old_name RENAME TO new_name;

As of Version 3.23.23, MySQL includes an explicit RENAME TABLE statement:

RENAME TABLE old_name TO new_name;

RENAME TABLE allows you to rename multiple tables, which allows you to do things such as swap the names of two tables in a single statement:

RENAME TABLE name1 TO temp_name, name2 TO name1, tmp_name to name2;

You can achieve the same result with ALTER TABLE, except that you need three separate statements. Because of that, the tables become available to other clients in the brief intervals between statements, which may be undesirable. Using a single RENAME TABLE statement avoids this problem.

RENAME TABLE is also useful for rotating tables. To do this without having an interval in which the log table is unavailable to clients, create an empty version under a temporary name, then rotate the files using a single RENAME TABLE statement. For example, if you want to keep monthly log tables, named using the year and month, you might do something like this:

CREATE TABLE log_temp (...);
RENAME TABLE log TO log_2001_05, log_temp TO log;

To rotate log tables to keep a set of daily tables covering the last week, you could run the following statements daily:

CREATE TABLE log_temp (...);
DROP TABLE IF exists log_7;
RENAME TABLE log_6 TO log_7,
 log_5 TO log_6,
 log_4 TO log_5,
 log_3 TO log_4,
 log_2 TO log_3,
 log_1 TO log_2,
 log TO log_1,
 log_tmp TO log;