Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 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 an API program, the name of the backup copy could be generated based on the date (e.g., survey_2008dec07) so that each day’s data could be preserved. As mentioned earlier, you can also change the database of a table in the process:

CREATE TABLE survey_new LIKE survey;

RENAME TABLE survey TO backup.survey_2008dec07,
survey_new TO survey;

In this example, the old table is renamed and moved into a database called backup.