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.

Solution 1

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

Solution 2

you can do it in this query

CREATE TABLE a LIKE b

after you can insert

INSERT INTO a SELECT * FROM b

read more information in this article