INFORMATION_SCHEMA.INNODB_COLUMNS.HAS_DEFAULT  and DEFAULT_VALUE  provide metadata about default values for instantly added columns. 

Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. At a minimum, ALGORITHM=INPLACE, LOCK=SHARED is required. 

The table is rebuilt if ALGORITHM=INPLACE  is used to add a column. 

•Dropping a column 

ALTER TABLE tbl_name  DROP COLUMN column_name , ALGORITHM=INPLACE, LOCK=NONE; 

Data is reorganized substantially, making it an expensive operation. 

•Renaming a column 

ALTER TABLE tbl  CHANGE old_col_name new_col_name data_type , ALGORITHM=INPLACE, LOCK=NONE; 

To permit concurrent DML, keep the same data type and only change the column name. 

When you keep the same data type and [NOT] NULL  attribute, only changing the column name, the operation can always be performed online. 

You can also rename a column that is part of a foreign key constraint. The foreign key definition is automatically updated to use the new column name. Renaming a column participating in a foreign key only works with ALGORITHM=INPLACE . If you use the ALGORITHM=COPY  clause, or some other condition causes the command to use ALGORITHM=COPY  behind the scenes, the ALTER TABLE statement fails. 

ALGORITHM=INPLACE  is not supported for renaming a generated column. 

•Reordering columns 

To reorder columns, use FIRST  or AFTER  in CHANGE  or MODIFY  operations. 

ALTER TABLE tbl_name  MODIFY COLUMN col_name column_definition  FIRST, ALGORITHM=INPLACE, LOCK=NONE; 

Data is reorganized substantially, making it an expensive operation. 

•Changing the column data type 

ALTER TABLE tbl_name  CHANGE c1 c1 BIGINT, ALGORITHM=COPY; 

Changing the column data type is only supported with ALGORITHM=COPY . 

•Extending VARCHAR  column size 

ALTER TABLE tbl_name  CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE; 

The number of length bytes required by a VARCHAR  column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR  columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE  only supports increasing VARCHAR  column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE  does not support increasing the size of a VARCHAR  column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy ( ALGORITHM=COPY ). For example, attempting to change VARCHAR  column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE  returns this error: 

