How to add fields to a large data table in MySQL

Preface

I believe everyone is familiar with adding fields. You can easily write it out. To add a field to a MySQL table, just execute the following SQL:

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

However, if an online table has a large amount of data, adding a field will lock the table. This process may take a long time or even cause the service to crash, so this operation is very risky.

So, the idea of ​​adding fields to a large MySQL table is as follows:

① 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 new table to the name of the old table

However, it should be noted that when executing the third step, this process may also take time. At this time, new data will come in, so it would be best if the original table has a field that records the time when the data was written. You can find the data after executing this 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 in the table is particularly large and the data integrity must be guaranteed, it is best to shut down the operation.

Another method:

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

2. Use a third-party online field modification tool

Generally speaking, for data volumes of hundreds of thousands, you can directly add fields.

Summarize

The above is the implementation idea of ​​adding fields to a large MySQL table. I hope that the content of this article can be of some help to your study or work. If you have any questions, you can leave a message to communicate.