MySQL Cookbook

Cloning a Table Exactly

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.

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.

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 ...