Mysql method for copying table structure and table data _ MySQL

This article mainly introduces the methods for copying table structures and table data in Mysql. For more information, see this article, for more information, see the following.

1. copy the table structure and data to the new table

Create table new table select * FROM old TABLE

This method will copy all content in oldtable. of course, we can use delete from newtable; to delete it.

However, the worst thing about this method is that the new table does not have the primary key, Extra (auto_increment) and other attributes of the old table. You need to use "alter" to add it yourself, and it is easy to make a mistake.

2. only copy the table structure to the new table

Create table new table select * FROM old table where 1 = 2

Or create table new table like old TABLE

3. copy the data from the old table to the new table (assuming the two tables have the same structure)

Insert into new table SELECT * FROM old table

4. copy the data from the old table to the new table (assuming the two tables have different structures)

Insert into new table (Field 1, field 2,...) SELECT field 1, field 2,... FROM old table

5. copy the Table 1 structure to Table 2.

SELECT * INTO table 2 FROM Table 1 WHERE 1 = 2

6. you can copy all contents of Table 1 to Table 2.

SELECT * INTO table 2 FROM Table 1

7. show create table old table;

This will list the creation commands of the old table. We only need to copy the command and change the table name to create a table with the same name.

8. mysqldump

Use mysqldump to dump the table, change its name, and export it back or run it directly in the command line.

The above content is all described in this article and I hope you will like it.