作为产品DBA,经常被开发问,修改字段长度锁表吗?然后凭借"经验"给出回答:如果字段长度超过256个字符就会锁表。
现在看来回答错误 。看看MySQL 官方文档
Extending VARCHAR column size
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).
翻译上面的意思
另外就是不支持以 in-place 方式缩小字段长度。
Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).
t1 表的字符集为utf8mb4,初始字段长度为20 ,80个字节,可以使用1byte表示。分别修改字符串长度为 60--->64--->128。当字段的字节数变动 跨越了256 则会锁表。
关于MySQL online ddl 奉献一份 收藏多年的流程图, 以xmind 文件方式分享到百度云盘上,方便给位查看,可以直接访问原文直达百度云盘。
链接: https://pan.baidu.com/s/1kzMqNK0ZhV1GUzFjWCc88w
提取码: 8duj
-The End-