How to Duplicate a Table in MySQL

You can duplicate or "clone" a table's contents by executing a CREATE TABLE ... AS SELECT statement:

CREATE TABLE new_table AS SELECT * FROM original_table;

Please be careful when using this to clone big tables. This can take a lot of time and server resources.

Note also that new_table inherits ONLY the basic column definitions, null settings and default values of the original_table. It does not inherit indexes and auto_increment definitions.

To inherit all table definitions, use the CREATE TABLE... LIKE syntax:

CREATE TABLE new_table LIKE original_table;

This makes the structure of new_table exactly like that of original_table, but DOES NOT copy the data. To copy the data, you'll need INSERT ... SELECT:

INSERT INTO new_table SELECT * FROM original_table;

Again, be careful when doing this to big tables.