Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata