Copying a MySQL table with schema and keys, but not data

October 11th, 2007 | PHP, Technology

Here's a great thing I learned about MySQL today:

SQL:

CREATE TABLE newtable LIKE oldtable

Will create a copy of the table along with structure and keys (primary keys, auto increments etc) but not the data.

This is perfect solution to create new tables based on an existing one - we use it to create a new database for every customer that signs up on a project. We go in a loop of the list of tables, and use the CREATE TABLE statement to duplicate the table in a new name. This works across databases too (assuming you have permissions).

Of course, if you wanted to get all the data, you can do something like:

PLAIN TEXT

SQL:

CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM oldtable;

The full CREATE TABLE syntax is here.

3 comments ↓

#1 Shantanu Oak on 10.12.07 at 3:12 pm

A point to note is that you can crate a new table with indexes AND data in a single statement.

CREATE TABLE new_collection (ID INT auto_increment primary key)
SELECT artist, title, year FROM collection

This will be useful when you write a standard create table statement and add dummy data from another table. For e.g.
CREATE TABLE mynew
( PRIMARY KEY (nation), KEY fsearchIp (searchip)
) ENGINE = MEMORY DEFAULT CHARACTER SET utf8
SELECT * FROM world

#2 Alix Axel on 10.18.07 at 2:40 am

Hi! Another nice way to get the table structure schema is to do: SHOW CREATE TABLE mytable;

Nice post, cheers!

#3 Khurram on 02.21.08 at 4:54 pm

Your post helped me much. I was using “CREAT TABLE newtable SELECT * FROM oldtable” which correctly creates a table with data and structure of first table but it does not copy the indexes. This problem solved by your post.

Many Thanks.