mysql大表如何修改表结构，如增加字段或修改字段类型

Answer 1

The idea of ​​adding fields to a large MySQL 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;

2. Add new fields to the new table

3. Copy the data from the old table

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

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

It should be noted that when executing the third step, this process may also take time. At this time, new data comes in, so it would be best if the original table has a field to record the writing time of the data. You can find and execute this Operate the data after one step and import into the new table repeatedly until the data differences are small. However, a very small amount of data may still be lost.

So, if the data in the table is particularly large and the data integrity needs to be ensured, it is best to shut down the operation.

Answer 2

Percona has an online ddl tool. The principle is to use a temporary table to add a new data transfer trigger. You can use it