Renaming a Table
Problem
A table needs to be renamed.
Solution
You can use ALTER TABLE or
RENAME TABLE for this.
Discussion
To rename a table, use the RENAME option of the
ALTER TABLE statement:
ALTER TABLEold_nameRENAME TOnew_name;
As of Version 3.23.23, MySQL includes an explicit
RENAME TABLE statement:
RENAME TABLEold_nameTOnew_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 TABLEname1TOtemp_name,name2TOname1,tmp_nametoname2;
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 ...