Realization of adding fields to large data tables in MySQL

Preface

I believe everyone should be familiar with adding fields, and you can write them at will. Add fields to MySQL 1 table and execute the following sql:

ALTER TABLE tbl_tpl ADD title(255) DEFAULT '' COMMENT ' Title ' AFTER id;

However, if a table on the line has a large amount of data, the table will be locked by adding fields. This process may take a long time and even cause the service to crash, so this operation is very risky.

Then, the idea of adding fields to MySQL large table is as follows:

(1) Create a temporary new table, first copy the structure of the old table (including indexes)

create table new_table like old_table;

Add new fields to the new table

Copy the data from the old table

insert into new_table(filed1,filed2 … ) select filed1,filed2, …  from old_table

Delete the old table and rename the name of the new table to the name of the old table

However, it should be noted here that when the third step is executed, this process may take time, and new data will come in at this time, so it is best for the original table to have a field that records the writing time of the data. You can find the data after the first step and import it into the new table repeatedly until the data difference is very small. However, a very small amount of data may still be lost.

Therefore, if the data of the table is particularly large and the data is complete at the same time, it is best to stop the operation.

Another method:

1. Add fields in the slave library, and then switch between master and slave

2. Use a third-party tool to change fields online

Under normal circumstances, more than 100,000 data can be directly added to the field operation.

Summarize

The above is about the realization of adding fields in the MySQL table. I hope the content of this article can bring 1 certain help to everyone's study or work. If you have any questions, you can leave a message for communication.