Mysql query to copy the structure of a table to create another table

Looking for help in creating a Mysql que开发者_如何学Cry to copy the structure of an existing table to create another table.

To create a table as in an exact replica of another table:

CREATE TABLE `new_table_name` LIKE `old_table_name`;

If you want to also copy the contents of the table you can do:

CREATE TABLE `new_table_name` LIKE `old_table_name`;
INSERT INTO `new_table_name` SELECT * FROM `old_table_name`;

If you want to copy the table structure including its keys, then you should use:

CREATE TABLE `new_table_name` LIKE `old_table_name`;

To copy the whole table

CREATE TABLE `new_table_name` SELECT * FROM `old_table_name`;

It will create the table and insert all the data from the old table but without bringing the keys from the old table. So you will need to set the keys for the new table.

Its old thread but if someone needed. If you want create query that will create exactly copy of existing table.

show create table <existing table name>

MySQL query to copy the structure of a table to create another table structure without data another way is...

CREATE TABLE `table_name_new` select * from `table_name_old` limit 0;

also you can use this:

CREATE TABLE 'new_table_name' SELECT * FROM 'pattern_table' where 1=0;