Copying a table in MySQL and including a primary key

Question

I have an old piece of code that I am using to copy tables in MySQL.

CREATE TABLE new_table (select * from old_table);

This works great with one exception. It does not copy the primary key or other table indexes.

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

+2

Alan storm Apr 30 10 at 22:20

2 answers

You can use this

CREATE TABLE new_table LIKE old_table;

but it does not provide data duplication.

+2

hsz Apr 30 10 at 22:28

cdnicoll · Accepted Answer · 2010-04-30T22:29:20+0000

This is one of two ways. To see how to build a table, you can use

SHOW CREATE TABLE old_table

You can 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;