[Mysql] A 1000W record table, modify the table structure, add a value for an enum field, how long will it take?

The Mysql5.5 table is InnoDB and the field is named type, is a normal field and is not included in any index/primary key.

typeThe field would have two values ' a ' , ' B ' , and now I'm going to add the value ' C ' , so I have a modified statement:

ALTER TABLE `table` MODIFY COLUMN `type` enum('a','b','c');

Now I want to know how long it will take me to execute the above statement.

I mainly want to know because this field does not involve the index, the time spent on the modification is related to the 1000w record of the table.

Everyone help me to estimate, I have a plan for downtime, thank you!

Reply content:

The Mysql5.5 table is InnoDB and the field is named as type a normal field and is not included in any index/primary key.

typeThe field would have two values ' a ' , ' B ' , and now I'm going to add the value ' C ' , so I have a modified statement:

ALTER TABLE `table` MODIFY COLUMN `type` enum('a','b','c');

Now I want to know how long it will take me to execute the above statement.

I mainly want to know because this field does not involve the index, the time spent on the modification is related to the 1000w record of the table.

Everyone help me to estimate, I have a plan for downtime, thank you!

I've been looking at high-performance MySQL (third edition) recently, and that's what it says. Modify column causes the table to be rebuilt (the internal implementation is to build a table with the latest structure, then import the data and then delete the old table, renaming the new table as the old table name), so it will be slower to use ALTER TABLE XXX ALTER COLUMN to modify the. frm file directly so it will be quick. It has nothing to do with how much the table data.

The other approach, in principle, is to modify the. frm file: 1. Create a new table for a new institution 2.flush tables with read lock. Closes the table that is in use and prevents any tables from being opened. 3. Swap. frm file 4.unlock tables release the second step of the lock.

I've seen it in the last few nights and I haven't tried it myself, so I should go and test it.

static long getAlterTable(){if(数据库没备份){  do备份  getAlterTable();  }else{   return test alter table time }};

You can look at the Percona online modification table structure. Support Online modification.

