Three ways to copy MySQL tables (summary)

Copy table structure and its data

The following statement will copy the data into a new table.

Note: This statement actually just creates a table with the results of the select statement, so the new table will not have a primary key or index.

create table table_name_new as (select * from table_name_old);

Copy only the table structure

create table table_name_new as select * from table_name_old where 1=2;

or

create table table_name_new like table_name_old;

Note: The former method will not copy the primary key type and index, while the latter method will copy all field types of the old table to the new table.

Copy only table data

If the two tables have the same structure

insert into table_name_new select * from table_name_old;

If the two tables have different structures

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old;

Note: Many articles say that data can be copied using the following statement. The table_name_new table does not need to exist and will be automatically created during execution. In fact, the SELECT ... INTO form stores the query results in a variable or writes them to a file, that is, table_name_new is a variable or a file.

select column1,column2,.... into table_name_new from table_name_old;

This concludes this article on three ways to copy MySQL tables (summary). For more information about copying MySQL tables, please search 123WORDPRESS.COM’s previous articles or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!