MySQL - Duplicate table

Question

I need to duplicate a table in MySQL, making the new table empty. That is, I need to copy only the structure of an existing table to a new one.

Brett Bender · Accepted Answer · 2009-04-20 04:55:04Z

76

Try the create table LIKE syntax.

create table users2 like users;

This should give you an empty table (users2) with the same structure as the original (users).

answered Apr 20, 2009 at 4:55

Brett Bender

19.6k2 gold badges39 silver badges46 bronze badges

thanks sir but how about creating the same table and changing the table type from InnoDB to MYISAM at the same time?
– GianFS
Commented Jan 18, 2013 at 1:03
4

You'd have to do that in two steps - either run a SHOW CREATE TABLE and modify the output to suit you, or run a CREATE TABLE LIKE followed by an ALTER TABLE.
– Brilliand
Commented Jan 18, 2013 at 22:41

Add a comment |

Vineet1982 · Accepted Answer · 2011-05-21 20:25:18Z

17

There is also another way to create a empty table as existing table and you can use the following command also

create table a select * from users2 limit 0, 0;

answered May 21, 2011 at 20:25

Vineet1982

7,8785 gold badges33 silver badges68 bronze badges

3

or CREATE TABLE a SELECT * FROM b WHERE 0=1
– Alex K
Commented Mar 15, 2013 at 15:22
1

Just FYI: doing this will NOT copy the indices.
– user499054
Commented May 28, 2014 at 15:08

Add a comment |

2 Answers 2