MySQL 更改 varchar 列长度需注意的事

2024-07-16 15:26| 来源: 网络整理| 查看: 265

由于个人能力有限，文中可能存在错误，并且很多细节没有深入分析，欢迎批评指正。

文章目录问题现象原因分析注意总结

问题现象

MySQL 5.7 版本中，在对表字段 varchar 长度进行更改时出现报错，如下所示：

mysql> ALTER TABLE t1 MODIFY COLUMN C1 varchar(100) NOT NULL,ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

根据报错提示，本次字段长度更改并不支持 INPLACE 算法，需通过 COPY 的方式执行。

关于 MySQL DDL 算法： 1）COPY ，MySQL 5.5 及以下版本默认算法。 2）INPLACE ，MySQL 5.6开始被引入并默认使用，包含 rebuild-table 和 not-rebuild-table 两种类型，两者的主要差异在于是否需要重建源表。其次，该算法支持在 DDL 过程中间的读写，但是对写入的数据量有上限，不能超过 innodb_online_alter_log_max_size（默认为 128M）。 3）INSTANT ，MySQL 8.0.12 开始被引入并默认使用，支持快速加列，更多信息请自行查询官方手册。

表结构信息：

CREATE TABLE `t1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `c1` varchar(10) NOT NULL, `c2` varchar(20) NOT NULL, `c3` varchar(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_c123` (`c1`,`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4

此外，该问题在 MySQL 8.0 版本中也存在。

原因分析

该 DDL 操作计划是将字段 c1 数据类型长度从 varchar(10) 扩展为 varchar(100)，这意味着该字段所能容纳的字符数上限将由原先的 10 个字符提升至 100 个字符。

在 MySQL 中，字段长度的存储单位是字节，而非字符。考虑到每个字节由 8 位（bits）构成，因此单个字节能够表示的最大数值是 2^8 - 1，即 255。这是因为二进制计数从 0 开始，所以 8 位二进制数的范围是从 00000000 到 11111111 ，转换为十进制后的最大值为 255。

在本次操作中，由于字符集采用的是 utf8mb4，每个字符占用的字节数为 4。因此，当 c1 字段的长度从 varchar(10) 变为 varchar(100) 时，所需的存储空间从原先的 40 字节（10个字符 x 4字节/字符）增加到了 400 字节（100个字符 x 4字节/字符）。这意味着存储该字段长度的元数据需要两个字节来表示。

在这种情况下，如果 SQL 语句中指定使用 INPLACE 算法来执行这一操作，则会报错，因为INPLACE 算法通常不支持这种需要增加存储空间的修改操作。此时，需要通过 COPY 算法来执行这一 DDL 操作，具体而言，就是创建一个新的表结构，将旧表的数据复制到新表中，然后替换旧表。这一过程确保了数据的完整性和一致性，同时适应了字段长度变更所带来的新的存储需求。

更多信息也可参考官方手册说明：

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. Note The byte length of a VARCHAR column is dependant on the byte length of the character set.

注意总结

总结关于 varchar 列长度更改的限制如下：

长度变化在 0 到 255 字节的之间：这种更改可以通过 INPALCE 进行，此时长度字节的数量（1个）保持不变。长度从 256 字节或更多增至更大：此类更改同样支持 INPALCE 进行，因为长度字节的数量（2个）也维持不变。长度从小于 256 字节增至 256 字节或更多：这种更改不能通过 INPALCE 进行，因为长度字节的数量会从 1 个增加到 2 个。此类更改需要复制整个表（使用 ALGORITHM=COPY），以容纳新增的长度字节。长度减少的更改：减少varchar列的长度同样不支持 INPALCE 操作。

在进行 varchar 列长度更改时，务必注意上述限制，以避免因锁表而对业务产生不良影响。同时，在尝试执行指定的原地算法时，需准备好应对可能出现的报错，并采取相应的排查措施。

【本文地址】

公司简介

联系我们