How to duplicate a table with keys & other structure features retained in MySQL?

Question

How to duplicate a table with keys & other structure features retained? including primary key, foreign keys, and indexes.

Can this be done with a single MySQL query?

I'm using "create table newtable as select..." but this method makes all keys & indexes lost.

You can turn indexes on after creating table using "ALTER TABLE ADD INDEX ...". This way is good, because insertion in new table will be faster without indexes. — Oroboros102, Nov 22 '11 at 12:23
possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — mimarcel, Mar 06 '14 at 12:48

score 30 · Accepted Answer · edited Dec 23 '13 at 15:20

duplicating a table from another table (with indexing and structure)cannot be done with a single query you will need need 2 queries.

1) To create Duplicate table.

CREATE TABLE Table2 LIKE Table1;

This will create an exact copy of the table.

2) Fill in the Duplicate table with values from original table.

INSERT INTO Table2 SELECT * from Table1;

will fill Table2 with all the records fom Table1

score 7 · Answer 2 · answered Nov 22 '11 at 12:24

7

you can do it in this query

CREATE TABLE a LIKE b

after you can insert

INSERT INTO a SELECT * FROM b

3 Answers3