Copying a Table in MySQL and including the Primary Key

Question

I have an ancient code snippet I use for copying tables in MySQL.

CREATE TABLE new_table (select * from old_table);

This works great, with one exception. It doesn't copy the primary key or other table indexes.

Is there any way to copy a table in MySQL AND include the indexes/primary key??

Answer 1

You can use this

CREATE TABLE new_table LIKE old_table;

but it does not provide duplicating data.

Answer 2

Theres one of two ways. To see how a table is built you can use

SHOW CREATE TABLE old_table

You could also (I think you'll have to test it), run this:

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;