Data table replication statements in MySQL (Table structure, table data)

1. Copy table structure and data to new table

CREATE Table New Table SELECT * from old table
This method copies all the contents of the oldtable, and of course we can delete them with the delete from newtable.

But one of the worst aspects of this approach is that the new table has no attributes such as the primary key, Extra (auto_increment) of the old table. Need to use "alter" to add, and easily mistaken.

2. Copy table structure to new table only

CREATE Table New Table SELECT * from old table where 1=2
or CREATE table new table like old table

3, copy the old table data to the new table (assuming two table structure)

INSERT into new table SELECT * from old table

4, copy the old table data to the new table (assuming that two table structure is not the same)

INSERT into new Table (Field 1, Field 2,.......) SELECT Field 1, Field 2,...... From old table

5, you can copy the table 1 structure to table 2

SELECT * into table 2 from table 1 WHERE 1=2

6. You can copy table 1 to table 2

SELECT * into table 2 from table 1

7, show create table old tables;

This lists the creation commands for the old table. We just need to copy the command, change the name of the table, and we can create a table that's exactly the same.

8, Mysqldump

Dump the table with mysqldump, change the name, and then guide it back or run directly on the command line