MySQL在线修改varchar列的注意事项和方法

在数据库管理中，alter table操作是修改数据库结构的关键部分。mysql从5.6版开始提供在线操作，提供了一种在不锁定表的情况下执行这些更改的便捷方法。不过，这也有一些注意事项。在本文中，将探讨在线修改varchar列的过程，深入了解在扩大此类列的大小时获得的启发。

假设有以下表定义，该表要求扩展名为"address"的varchar列以容纳更多数据。下面是原始表定义：

?

1 2	drop table if exists abc_address; create table abc_address (id int not null auto_increment primary key,abc_address varchar(50) not null) engine=innodb default charset=utf8mb3;

执行以下alter table操作：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> alter table abce.abc_address change column abc_address abc_address varchar(85) not null default '', algorithm=inplace, lock=none; query ok, 0 rows affected (0.01 sec) records: 0  duplicates: 0  warnings: 0   mysql> show create table abc_address\G *************************** 1. row ***************************        Table: abc_address Create Table: CREATE TABLE `abc_address` (   `id` int NOT NULL AUTO_INCREMENT,   `abc_address` varchar(85) NOT NULL DEFAULT '',   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec)

修改似乎成功了，我们看到表定义也相应地被修改了。

现在，尝试后续更改--尝试扩展到100。令人惊讶的是，当我们尝试将varchar列长度增加到100时，却遇到了错误：

?

1 2	mysql> alter table abce.abc_address change column abc_address abc_address varchar(100) not null default '', algorithm=inplace, lock=none; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

DDL被拒绝，MySQL建议改用COPY算法。将DDL改成copy算法，施加共享锁，然后执行成功。如：
命令改为使用算法复制和共享锁，如下所示，成功执行了 ALTER VARCHAR 列：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> alter table abce.abc_address change column abc_address abc_address varchar(100) not null default '', algorithm=copy, lock=shared; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0   mysql> show create table abc_address\G *************************** 1. row *************************** Table: abc_address Create Table: CREATE TABLE `abc_address` ( `id` int NOT NULL AUTO_INCREMENT, `abc_address` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec)

 

in-place执行alter的限制
虽然最初允许将varchar列长度增加到85，但为什么mysql拒绝在线修改（algorithm=inplace）将varchar列长度修改为100呢？

可以从文档中找到答案，这与varchar如何存储数据（实际上是前缀和数据）有关。在mysql中，varchar值以一个或两个字节长度的前缀(记录字符串的长度)加数据的方式存储。
前缀长度取决于数据的大小。数据长度不超过255字节时，只使用一个字节的前缀，但数值超过255字节时，需要两个字节的前缀长度。因此，in-place的alter table只支持将varchar列的大小从0增加到255字节，或从256字节增加到更大。
当alter需要扩展前缀长度时，它不允许in-place修改。

要查看实际操作，可以查询information_schema.columns表。最初，当列长度被alter为85时，请注意以下细节。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

mysql> select * from information_schema.columns where table_schema = 'abce' and table_name in ('abc_address') and column_name = 'abc_address'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: abce TABLE_NAME: abc_address COLUMN_NAME: abc_address ORDINAL_POSITION: 2 COLUMN_DEFAULT: IS_NULLABLE: NO DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 85 CHARACTER_OCTET_LENGTH: 255 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb3 COLLATION_NAME: utf8mb3_general_ci COLUMN_TYPE: varchar(85) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (0.00 sec)

在这里，character_maximum_length指定了存储在列中的字符长度，
而character_octet_length则指定了以字节为单位的长度。需要注意的是，该列的字节长度已经是255，再增加就需要mysql将varchar前缀大小扩展到两个字节。将varchar列大小改为100后，存储需求如下。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

mysql> select * from information_schema.columns where table_schema = 'abce' and table_name in ('abc_address') and column_name = 'abc_address'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: abce TABLE_NAME: abc_address COLUMN_NAME: abc_address ORDINAL_POSITION: 2 COLUMN_DEFAULT: IS_NULLABLE: NO DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 100 CHARACTER_OCTET_LENGTH: 300 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb3 COLLATION_NAME: utf8mb3_general_ci COLUMN_TYPE: varchar(100) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (0.00 sec)

 

在这种情况下如何在线修改varchar列？

在mysql中，面对在线alter table遇到varchar列的限制时，可以考虑使用percona工具包的pt-online-schema-change。它是众所周知的行业标准工具，也是percona工具包的一部分，能以最少的停机时间执行在线更改。

 

alter table修改varchar列的character set
字符集定义了存储要求，更改character set也会导致所需字节数增加。因此，在更改时也需要考虑这一点。查看下面的示例，可以看到要存储85字节的varchar长度，长度已从之前的255字节增加到340字节。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> alter table abce.abc_address change column abc_address abc_address varchar(85) charset utf8mb4 not null default '', algorithm=inplace, lock=none; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. mysql> alter table abce.abc_address change column abc_address abc_address varchar(85) charset utf8mb4 not null default '', algorithm=copy, lock=shared; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0   mysql> select * from information_schema.columns where table_schema = 'abce' and table_name in ('abc_address') and column_name = 'abc_address'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: abce TABLE_NAME: abc_address COLUMN_NAME: abc_address ORDINAL_POSITION: 2 COLUMN_DEFAULT: IS_NULLABLE: NO DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 85 CHARACTER_OCTET_LENGTH: 340 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_0900_ai_ci COLUMN_TYPE: varchar(85) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (0.00 sec)

 

最后
mysql中的在线alter table操作为数据库管理带来了灵活性，但它们也有特定的局限性，尤其是在处理varchar列时。
了解底层存储机制对成功更改至关重要。copy算法可以帮助管理超出255字节限制的varchar扩展，但pt-online-schema-change更易于使用，且在执行操作时减少了出错。