MySQL in a Nutshell, 2nd Edition

Name

RENAME TABLE

Synopsis

RENAME TABLE table TO table[,...]

Use this statement to rename a table to a new name, given after the TO keyword. Multiple tables may be specified in a comma-separated list, following the format old_name TO new_name. 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. If a trigger is associated with a table that is renamed and moved to a new database, the trigger will fail when used. You won’t be warned of this possibility when renaming the table.

You can use this statement to rename a view, but you cannot use it to move the view to a different database.

This statement requires ALTER and DROP privileges for the table being renamed. CREATE and INSERT privileges are needed for the new table and database if the table is being moved.

As an example, suppose that users add data to a particular table during the course of the day, and each day the contents of the table are to be preserved. Suppose further that you want to reset the table to contain no data. Here’s one way you might do that:

CREATE TABLE survey_new LIKE survey;

RENAME TABLE survey TO survey_bak,
survey_new TO survey;

In this example, a new table called survey_new ...