See official documents for more details: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
A few days ago, my colleague mentioned that expanding the column width below varchar 255 does not lock the table, and then expanding upward requires locking. It seems that there is such a saying. After checking the official documents, it is true. I can easily test it, so I have the following.
Official document translation:
Principle: the length and number of bytes required for the VARCHAR column must remain the same. For VARCHAR columns with sizes from 0 to 255 bytes, a length byte is required to encode the value. For VARCHAR columns with a size of 256 bytes or more, two bytes in length are required.
Therefore, IN-PLACE ALTER TABLE only supports increasing the VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a larger size.
IN-PLACE ALTER TABLE does not support increasing the size of the 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, for example, from varchar(255) to varchar(256). In this case, you have to use ALGORITHM=COPY.
Note: the byte length of the VARCHAR column depends on the byte length of the character set (that is, latin1 is different from utf8 and utf8mb4).
Take a look at our most common UTF8 character set:
CREATE TABLE `tb1` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; In the table structure above, b Columns are defined as utf8，Occupy 3 bits, that is, we should expand without lock, and the maximum can only be 255/3 About 85 this width. -- Reset next b Column length MySQL [test]> ALTER TABLE tb1 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb1 CHANGE COLUMN b b VARCHAR(85), ALGORITHM=INPLACE, LOCK=NONE; -- Reset next b Column length MySQL [test]> ALTER TABLE tb1 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb1 CHANGE COLUMN b b VARCHAR(86), ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Let's take another look at the varchar extension length of utf8mb4 character set:
CREATE TABLE `tb5` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; In the table structure above, b Columns are defined as utf8mb4 ，Occupy 4 bits, that is, we should expand without lock, and the maximum can only be 255/4 Approximately equal to the width of 63. -- Reset next b Column length MySQL [test]> ALTER TABLE tb5 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb5 CHANGE COLUMN b b VARCHAR(40), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb5 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb5 CHANGE COLUMN b b VARCHAR(63), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb5 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb5 CHANGE COLUMN b b VARCHAR(64), ALGORITHM=INPLACE, LOCK=NONE; ---> Change it to 64. There is an error here ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Take another look at the varchar extension length of the latin1 character set (255 and below):
CREATE TABLE `tb2` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Reset next b Column length MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(128), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(256), ALGORITHM=INPLACE, LOCK=NONE; ---> Change it to 256. There is an error here ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Let's look at the varchar extension length of the latin1 character set (256 to the above):
CREATE TABLE `tb4` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(256) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Reset next b Column length MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(256); MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(1024), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(256); MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(4096), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
Take another look at the varchar extension length of the latin1 character set (extending from 255 to 256):
CREATE TABLE `tb3` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(252) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Reset next b Column length MySQL [test]> ALTER TABLE tb3 CHANGE COLUMN b b VARCHAR(252); MySQL [test]> ALTER TABLE tb3 CHANGE COLUMN b b VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb3 CHANGE COLUMN b b VARCHAR(252); MySQL [test]> ALTER TABLE tb3 CHANGE COLUMN b b VARCHAR(256), ALGORITHM=INPLACE, LOCK=NONE; ---> Change it to 256. There is an error here ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Take another look at the varchar extension length of the latin1 character set (255 and below extensions):
CREATE TABLE `tb2` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Reset next b Column length MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(128), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(10); MySQL [test]> ALTER TABLE tb2 CHANGE COLUMN b b VARCHAR(256), ALGORITHM=INPLACE, LOCK=NONE; ---> Change it to 256. There is an error here ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Let's look at the varchar extension length of the latin1 character set (256 to the above):
CREATE TABLE `tb4` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(256) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Reset next b Column length MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(256); MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(1024), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Reset next b Column length MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(256); MySQL [test]> ALTER TABLE tb4 CHANGE COLUMN b b VARCHAR(4096), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
other:
- The use of replace mode ALTER TABLE to reduce the VARCHAR size is not supported. To reduce the VARCHAR size, you must use ALGORITHM=COPY
The following operations are also unlocked,
ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;
It is not listed here one by one, and the details shall be subject to the official documents.