526互联

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

发布时间 2023-08-03 11:30:27作者: abce


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

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

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操作：

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时，却遇到了错误：

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 列：

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时，请注意以下细节。

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后，存储需求如下。

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字节。

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更易于使用，且在执行操作时减少了出错。