Several Mysql methods for copying table structure and table data

This article shares with you several examples of copying table structures and table data. Please see below for details.

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

CREATE TABLE new table SELECT * FROM old table

This method will copy all the contents in the oldtable , of course we can use

delete from newtable;

to delete.

However, one of the worst aspects of 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 add it yourself using the alter command, and it is easy to make mistakes.

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 of the old table to the new table (assuming that the two table structures are different)

INSERT INTO new table (field 1, field 2,...) SELECT field 1, field 2, ...... FROM old table

5. You can copy the structure of table 1 to table 2

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

6. You can copy all the contents of table 1 to table 2

SELECT * INTO table 2 FROM table 1

7. show create table old table;

This will replace the old table Table creation commands are listed. We only need to copy this command and change the name of the table to create an identical table

8, mysqldump

Use mysqldump to dump the table, change the name and then import it back Or run it directly in the command line

The above content is the several methods recommended by this article for copying table structure and table data. I hope it will be helpful to everyone.

Related recommendations:

SQL statements and time functions for MySQL to copy table structure and table data

Mysql copies table structure and table data And modify the primary key

MySQL SQL statement to copy the table structure and content to another table

The above is the detailed content of Several Mysql methods for copying table structure and table data. For more information, please follow other related articles on the PHP Chinese website!