How to copy data from a MySQL table to a new table

In mysql, if we want to perform full table copy on the data, in addition to importing and exporting data, we can also directly use commands to perform operations, which can help us with some special operations, such: the table structure can be copied when data is sharded.

In mysql, if we want to perform full table copy on the data, in addition to importing and exporting data, we can also directly use commands to perform operations, which can help us with some special operations, such: the table structure can be copied when data is sharded.

Copy the table in MySQL and copy the old_table table to the new_table table.

1. Do not copy table data, just copy structure.

The Code is as follows:
Create table new_table LIKE old_table2

. Copy the data using the SELECT query. The new_table table will lose the primary key, index, and other information.

Reference

The Code is as follows:
Create table new_table ( SELECT * FROM old_table )

3. Completely copy the table

The Code is as follows:
Create table new_table LIKE old_table; Insert into new_table SELECT * FROM old_table;

4. Copy fields only

The Code is as follows:
Create table new_table ( SELECT field1, field2 FROM old_table )

5. Partial copy

The Code is as follows:
Create table new_table ( SELECT * FROM old_table WHERE field1 = 'mangguo' )

Data Replication

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

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

Create table new TABLE

SELECT * FROM old table WHERE 1 = 2: Make the WHERE condition not true