Renaming a Table

8.7.1 Problem

A table needs to be renamed.

8.7.2 Solution

You can use ALTER TABLE or RENAME TABLE for this.

8.7.3 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 TO log_5,
 log_3 TO log_4,
 log_2 TO log_3,
 log_1 TO log_2,
 log TO log_1,
 log_tmp TO log;

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: