Cloning a Table Exactly

3.26.1 Problem

You need an exact copy of a table, and CREATE TABLE ... SELECT doesn't suit your purposes because the copy must include the same indexes, default values, and so forth.

3.26.2 Solution

Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all. Then modify the statement to change the table name to that of the clone table and execute the statement. If you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.

3.26.3 Discussion

Because CREATE TABLE ... SELECT does not copy indexes or the full set of column attributes, it doesn't necessarily create a destination table as an exact copy of the source table. Because of that, you might find it more useful to issue a SHOW CREATE TABLE query for the source table. This statement is available as of MySQL 3.23.20; it returns a row containing the table name and a CREATE TABLE statement that corresponds to the table's structureincluding its indexes (keys), column attributes, and table type:

*************************** 1. row ***************************
 Table: mail
Create Table: CREATE TABLE `mail` (
 `t` datetime default NULL,
 `srcuser` char(8) default NULL,
 `srchost` char(20) default NULL,
 `dstuser` char(8) default NULL,
 `dsthost` char(20) default NULL,
 `size` bigint(20) default NULL,
 KEY `t` (`t`)

By issuing a SHOW CREATE TABLE statement from within a program and performing a string replacement to change the table name, you obtain a statement that can be executed to create a new table with the same structure as the original. The following Python function takes three arguments (a connection object, and the names of the source and destination tables). It retrieves the CREATE TABLE statement for the source table, modifies it to name the destination table, and returns the result:

# Generate a CREATE TABLE statement to create dst_tbl with the same
# structure as the existing table src_tbl. Return None if an error
# occurs. Requires the re module.

def gen_clone_query (conn, src_tbl, dst_tbl):
 cursor = conn.cursor ( )
 cursor.execute ("SHOW CREATE TABLE " + src_tbl)
 row = cursor.fetchone ( )
 cursor.close ( )
 if row == None:
 query = None
 # Replace src_tbl with dst_tbl in the CREATE TABLE statement
 query = re.sub ("CREATE TABLE .*`" + src_tbl + "`",
 "CREATE TABLE `" + dst_tbl + "`",
 query = None
 return query

You can execute the resulting statement as is to create the new table if you like:

query = gen_clone_query (conn, old_tbl, new_tbl)
cursor = conn.cursor ( )
cursor.execute (query)
cursor.close ( )

Or you can get more creative. For example, to create a temporary table rather than a permanent one, change CREATE to CREATE TEMPORARY before executing the statement:

query = gen_clone_query (conn, old_tbl, new_tbl)
query = re.sub ("CREATE ", "CREATE TEMPORARY ", query)
cursor = conn.cursor ( )
cursor.execute (query)
cursor.close ( )

Executing the statement returned by gen_clone_query( ) creates an empty copy of the source table. To copy the contents as well, do something like this after creating the copy:

cursor = conn.cursor ( )
cursor.execute ("INSERT INTO " + new_tbl + " SELECT * FROM " + old_tbl)
cursor.close ( )

Prior to MySQL 3.23.50, there are a few attributes that you can specify in a CREATE TABLE statement that SHOW CREATE TABLE does not display. If your source table was created with any of these attributes, the cloning technique shown here will create a destination table that does not have quite the same structure.

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: