Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 TABLE old_name RENAME TO new_name;

As of Version 3.23.23, MySQL includes an explicit RENAME TABLE statement:

RENAME TABLE old_name TO new_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 TABLE name1 TO temp_name, name2 TO name1, tmp_name to name2;

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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata