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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.