mysql online ddl更改varchar长度的锁表情况

我们只看5.7及其之后的版本，官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

mysql online ddl的特性是指，支持以instant（8.0版本开始支持）和in-place的方式修改表，同时允许在修改表的过程中，并发地进行dml操作。

mysql ddl操作中，修改varchar长度是一个比较常见的操作。我们需要关注的是修改varchar长度过程中是否是以instant或in-place的方式进行。

官方文档是这么描述的：

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:
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.
.....
Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).

由官方文档可梳理出下面几点：

1.对于varchar这种存储可变长度字符串的类型来说，需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只需要使用1个字节表示，否则使用2个字节。
2.如果想要支持in-place的方式修改varchar的长度，varchar列的长度字节数需要保持不变。也就是说in-place的方式只支持varchar列的大小在0~255之间或者256~256+之间变动，不支持从小于等于255的值增大到255以上，比如varchar(20)-->varchar(500）。因为这个时候长度字节数从1变成了2。
3.当长度字节数发生改变时，仅支持以copy的方式进行。
4.缩减varchar长度，只能使用copy方法。

这里需要注意的是，这里我们提到的字节数并不等于字符数，也即varchar(n)中的n。

varchar实际占用的字节数会根据字符的编码和存储格式来计算VARCHAR列的实际字节数。例如，对于UTF-8编码的字符，utf8mb3占3个字节，utf8mb4占4个字节。

接下来我们实战验证一下。有张表的表结构如下：

 CREATE TABLE `t` (
  `d` datetime DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

表t的字符集是utf8mb4,所以name字段，varchar(20)所占字节数为20*4=80。

我们先试试使用inplace算法将varchar(20)改为varchar(63）。

可见，20*4(80)-->63*4(252)，255字节以内的变动，支持使用inplace。
再试试使用inplace算法将varchar(63)改为varchar(64）：

可以看到，63*4(252)-->64*4(256)，这个时候不支持inplace,执行报错：ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

使用copy算法将n修改为64后，再将n修改为200（256-->800），则又支持inplace了，如下图所示：

此外，减少varchar字段的长度，也不能使用inplace，需要使用copy算法。

注：copy 模式会有持续性的锁（DDL 的整个过程期间无法向该表写入任何数据)，要格外关注。

总结

• 缩小字段长度不能使用inpalce，只能使用copy方式，会锁表。

• 放大字段长度：in-place的方式只支持varchar列的字节数在0~255之间或者256~256+之间变动，不支持从小于等于255的值增大到255以上。以UTF8mb4编码为例，一个字符占4个字节。如：

  • varchar(20)--》varchar(63)，inplace方式，不锁表。

  • varchar(64)--》varchar(120)，inplace方式，不锁表。

  • varchar(20)--》vachar(64)，copy方式，会锁表（20*4=80个字节，64*4=256字节）；

参考：http://mysql.taobao.org/monthly/2021/03/06/
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
https://blog.csdn.net/yuanlairuci1992/article/details/126886422

点个“赞 or 在看” 你最好看！

喜欢，就关注我吧！