MySQL: create table based on existing table

MySQL offers a simple way to create a new table with the same column and index definitions as an existing table. If you have a table named table1 defined as follows:

CREATE TABLE table1 (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
email VARCHAR(40),
created DATETIME,
index name_idx (name)
)

Then executing the following will create an identical table with the name table2:

CREATE TABLE table2 LIKE table1

This copy will retain all the column definitions and indexes as table1 and will be identical in all respects (with a few exceptions). You can also create a copy of a table from an existing table using

CREATE TABLE table2 SELECT * FROM table1

In this case table2 will have the same column type definitions and values as table1, but index information is not duplicated. The table would be defined as follows:

CREATE TABLE table2 (
id int(11) NOT NULL default '0',
name varchar(30) default NULL,
email varchar(40) default NULL,
created datetime default NULL
)

You can also restrict the values copied into table2 based on the results of the SELECT statement. For instance,

CREATE TABLE table2 SELECT id, name FROM table1 WHERE id > 1000

will only copy values from table1 where the id is greater than 1000, and only recreate the id and name columns from table1.

See the MySQL manual for all the details on this functionality.