[MySQL] Summary of table replication solutions in MySQL

We often encounter the need to copy the structure or data of a table when using the database. Today we have summarized the corresponding solutions for the complex tabulation structure and data in MySQL. Record it for emergency purposes.

In general, replication is divided into two categories: Copy table structure and copy table data (of course, you can also copy table structure and table data at the same time ).

Suppose we have a data table SRC:

First, let's look at the table structure and data: (Note that we have added indexes to the table ID, wp_id, and label_id)

desc src;

select * from src;

Test and summarize the following solutions:

1. Copy the table by using create table xx as select * from.

create table src_test as select * from src;

Then let's look at the data and structure of the new table:

desc src_test;

Take a closer look at the key in the figure: We found that the original table index and other information were lost during table copying. You can manually add these indexes.

Look at the data again:

select * from src_test;

The data is copied. Therefore, this method is suitable for copying data from the original table. However, if you want to retain the index information, you need to select another method.

In addition, this solution is not suitable if you only need to copy the table structure and maintain the index.

2. Use create table like to copy the table structure and Index

Another way is to copy the table structure instead of tabulation data.

create table src_test like src;

As shown in the figure, the create table xxx like YYY method only copies the table structure of the original table, without table information, and retains the index information of the original table while copying the table structure.

Then, we can copy the data through the insert XX select method:

Of course, in this way, you can also insert some data from the original table into the corresponding fields of the new table.

3. Create a new table by copying the table creation statement of the original table:

show create table src;

In addition, many database management software (such as phpMyAdmin, navicat, MySQL-front, and mymanager) support the function of exporting SQL statements. You can export it as an SQL file and then copy the corresponding table or data.

Do you have any better solutions: Please leave a message.

There is no end to learning.