RENAME TABLE

If for some reason you want to rename an existing MySQL table, use this MySQL statement. It's the easiest way.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

RENAME TABLE table TO table[,...]

Explanation

Use this MySQL statement to rename a given MySQL 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 MySQL statement to rename a view, but you cannot use it to move the view to a different database as you can with MySQL tables.

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

Examples

As an example of this MySQL statement, 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 contain no data. Here's one way you might 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 MySQL 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 name of the backup copy could be generated based upon the date (e.g., survey_2008dec07) so that each day's data could be preserved. As mentioned before, you can also change the database of a table in the process:

CREATE TABLE survey_new LIKE survey_bak;

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.