52

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.

Gerardo
  • 1,928
  • 4
  • 24
  • 34

2 Answers2

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).

Brett Bender
  • 19,388
  • 2
  • 38
  • 46
  • thanks sir but how about creating the same table and changing the table type from InnoDB to MYISAM at the same time? – GianFS Jan 18 '13 at 01: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 Jan 18 '13 at 22:41
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;
Vineet1982
  • 7,730
  • 4
  • 32
  • 67