Copy a MySQL table including indexes

Question

I can copy a MySQL table to create a new table:

CREATE TABLE newtable SELECT * FROM oldtable

This works, but the indexes are not copied to the new table. How can I copy a table including the indexes?

`indexes not create` vs `prevent the indexes` is ambiguous, what do you want to do? — Pentium10, Mar 10 '10 at 09:52
possible duplicate of [Duplicating a MySQL table, indexes and data](http://stackoverflow.com/questions/3280006/duplicating-a-mysql-table-indexes-and-data) — luchaninov, Oct 01 '14 at 18:41
@luchaninov the answer on that question mentions this question. This is an infinite loop. — FrancescoMM, Sep 20 '17 at 08:21

score 127 · Accepted Answer · edited Oct 16 '19 at 14:11

127

CREATE TABLE newtable LIKE oldtable; 
INSERT INTO newtable SELECT * FROM oldtable;

edited Oct 16 '19 at 14:11

Mojtaba

answered Mar 10 '10 at 09:53

raveren

5

Also, `create table newtable like oldtable;` copies column data types, so your records won't be coerced to an unexpected data type. – simeonwillbanks Dec 18 '12 at 22:15
Employing this I find that NotNull and PrimaryKey attributes are dropped - any thoughts on this? – Andrew Seabrook Feb 16 '16 at 15:54
1

@AndrewSeabrook, as i have tried PrimaryKey and NotNull will be saved as well. – Gary Jun 30 '16 at 02:30
1

To copy without indexes, just structure & data use this `CREATE TABLE tbl_new AS SELECT * FROM tbl_old` – Lukas Liesis Dec 11 '16 at 21:14
INSERT newtable SELECT * FROM oldtable; must be INSERT INTO newtable SELECT * FROM oldtable; – TexWiller Mar 06 '18 at 05:33
@TexWiller: `INTO` is optional. – raveren Mar 08 '18 at 12:50

1 Answers1