content_image/707477.jpg
ALTER TABLE tbl_name  ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 

<img src='content_image/707477.jpg'>

## Note 

The byte length of a VARCHAR  column is dependant on the byte length of the character set. 

Decreasing VARCHAR  size using in-place ALTER TABLE  is not supported. Decreasing VARCHAR  size requires a table copy ( ALGORITHM=COPY ). 

•Setting a column default value 

ALTER TABLE tbl_name  ALTER COLUMN col  SET DEFAULT literal , ALGORITHM=INSTANT; 

Only modifies table metadata. Default column values are stored in the data dictionary. 

•Dropping a column default value 

ALTER TABLE tbl  ALTER COLUMN col  DROP DEFAULT, ALGORITHM=INSTANT; 

•Changing the auto-increment value 

ALTER TABLE table  AUTO_INCREMENT= next_value , ALGORITHM=INPLACE, LOCK=NONE; 

Modifies a value stored in memory, not the data file. 

In a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto- increment column. You might also use this technique in a data warehousing environment where you periodically empty all the tables and reload them, and restart the auto-increment sequence from 1. 

•Making a column NULL 

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type  NULL, ALGORITHM=INPLACE, LOCK=NONE; 

Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. 

•Making a column NOT NULL 

ALTER TABLE tbl_name  MODIFY COLUMN column_name data_type  NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; 

Rebuilds the table in place. STRICT_ALL_TABLES  or STRICT_TRANS_TABLES SQL_MODE  is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.9, “ALTER TABLE Statement” . Data is reorganized substantially, making it an expensive operation. 

•Modifying the definition of an ENUM  or SET  column 

CREATE TABLE t1 (c1 ENUM(’a’, ’b’, ’c’)); ALTER TABLE t1 MODIFY COLUMN c1 ENUM(’a’, ’b’, ’c’, ’d’), ALGORITHM=INSTANT; 

Modifying the definition of an ENUM  or SET  column by adding new enumeration or set members to the end  of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change. For example, adding a member to a SET  column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. 

