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=MyISAMBy issuing a SHOW CREATE
TABLE statement from within a program and performing a string replacement ...