MySQL in a Nutshell

Name

RENAME TABLE

Synopsis

RENAME TABLE table TO table[,...]

Use this statement to rename a given table to the name given after the TO keyword. Additional tables may be specified for renaming in a comma-separated list. Multiple renames are performed left to right, and if any errors are encountered, all of the table name changes are reversed from right to left. While tables are being renamed, no other client can interact with the tables involved. Tables that are currently locked or tables that are part of a transaction in progress cannot be renamed.

Tables can be renamed and moved to databases on the same filesystem. As an example, suppose that users add data to a particular table during the course of the day and that each day the contents of the table are to be preserved. Suppose further that you want to reset the table back to no data. Here’s how to do that:

CREATE TABLE survey_new LIKE survey_bak;
RENAME TABLE survey TO survey_bak,
             survey_new TO survey;

In this example, a new table called survey_new is created based on the table structure of the old table called survey, but without the data. In the second SQL statement, the old table is renamed to survey_bak and the blank table, survey_new, is renamed to survey. If issued from a program, the new name could be generated based upon the date so that each day’s data could be preserved.