MySQL Cookbook

3.26 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 structure—including its indexes (keys), column attributes, and table type:

mysql> SHOW CREATE TABLE mail\G
*************************** 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`)
) TYPE=MyISAM

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):
    try:
        cursor = conn.cursor ( )
        cursor.execute ("SHOW CREATE TABLE " + src_tbl)
        row = cursor.fetchone ( )
        cursor.close ( )
        if row == None:
            query = None
        else:
            # Replace src_tbl with dst_tbl in the CREATE TABLE statement
            query = re.sub ("CREATE TABLE .*`" + src_tbl + "`",
                            "CREATE TABLE `" + dst_tbl + "`",
                            row[1])
    except:
        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.