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:
mysql> SHOW CREATE TABLE mailG *************************** 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 ( )
|