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

raveren · Accepted Answer · 2023-03-22T08:13:10.570

129

To copy with indexes and triggers do these 2 queries:

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

To copy just structure and data use this one:

CREATE TABLE new_table AS SELECT * FROM old_table;

edited Mar 22 '23 at 08:13

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