14.13.1 在线 DDL 操作

本节以下主题下提供了 DDL 操作的在线支持详细信息，语法示例和使用说明。

• Index Operations

• 主键操作

• Column Operations

• 生成的列操作

• 外键操作

• Table Operations

• Tablespace Operations

• Partitioning Operations

Index Operations

下 table 概述了对索引操作的联机 DDL 支持。星号 table 示其他信息，异常或依赖项。有关详细信息，请参见语法和用法说明。

table14.10 对索引操作的在线 DDL 支持

Operation	In Place	Rebuilds Table	允许并发 DML	仅修改元数据
创建或添加二级索引	Yes	No	Yes	No
删除索引	Yes	No	Yes	Yes
重命名索引	Yes	No	Yes	Yes
添加FULLTEXT索引	Yes*	No*	No	No
添加SPATIAL索引	Yes	No	No	No
更改索引类型	Yes	No	Yes	Yes

语法和用法说明

• 创建或添加二级索引

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

创建索引时，该 table 仍可用于读取和写入操作。 CREATE INDEX语句仅在访问 table 的所有事务完成后才完成，因此索引的初始状态反映了 table 的最新内容。

在线 DDL 对添加二级索引的支持意味着您通常可以通过以下方式来创建和加载 table 及相关索引的整个过程加快速度：创建没有二级索引的 table，然后在数据加载后添加二级索引。

CREATE INDEX或ALTER TABLE语句完成执行时，新创建的二级索引仅包含 table 中的提交数据。它不包含任何未提交的值，值的旧版本或标记为要删除但尚未从旧索引中删除的值。

如果服务器在创建二级索引时退出，则在恢复时，MySQL 将删除所有部分创建的索引。您必须重新运行ALTER TABLE或CREATE INDEX语句。

一些因素会影响此操作的性能，空间使用率和语义。有关详细信息，请参见第 14.13.6 节“在线 DDL 限制”。

• 删除索引

DROP INDEX name ON table;

ALTER TABLE tbl_name DROP INDEX name;

删除索引时，该 table 仍可用于读取和写入操作。 DROP INDEX语句仅在访问 table 的所有事务完成后才完成，因此索引的初始状态反映了 table 的最新内容。

• 重命名索引

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

• 添加FULLTEXT索引

CREATE FULLTEXT INDEX name ON table(column);

如果没有用户定义的FTS_DOC_ID列，则添加第一个FULLTEXT索引将重建 table。可以添加其他FULLTEXT索引而不重建 table。

• 添加SPATIAL索引

CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

• 更改索引类型(USING {BTREE | HASH})

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;

主键操作

下 table 概述了对主键操作的联机 DDL 支持。星号 table 示其他信息，异常或依赖项。参见语法和用法说明。

table14.11 对主键操作的在线 DDL 支持

Operation	In Place	Rebuilds Table	允许并发 DML	仅修改元数据
添加主键	Yes*	Yes*	Yes	No
删除主键	No	Yes	No	No
删除主键并添加另一个	Yes	Yes	Yes	No

语法和用法说明

• 添加主键

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

重建 table 到位。数据被大量重组，使其成为昂贵的操作。如果必须将列转换为NOT NULL，则在某些情况下不允许ALGORITHM=INPLACE。

重组clustered index始终需要复制 table 数据。因此，最好在创建 table 时定义primary key，而不是稍后再发布ALTER TABLE ... ADD PRIMARY KEY。

创建UNIQUE或PRIMARY KEY索引时，MySQL 必须做一些额外的工作。对于UNIQUE索引，MySQL 检查 table 是否不包含键的重复值。对于PRIMARY KEY索引，MySQL 还检查PRIMARY KEY列中是否不包含NULL。

当您使用ALGORITHM=COPY子句添加主键时，MySQL 会将关联列中的NULL值转换为默认值：0table 示数字，空字符串 table 示基于字符的列和 BLOB，而 0000-00-00 00:00:00table 示DATETIME。这是 Oracle 建议您不要依赖的非标准行为。仅当SQL_MODE设置包含strict_trans_tables或strict_all_tables标志时，才允许使用ALGORITHM=INPLACE添加主键。当SQL_MODE设置严格时，允许ALGORITHM=INPLACE，但是如果请求的主键列包含NULL值，则该语句仍然会失败。 ALGORITHM=INPLACE行为更符合标准。

如果创建的 table 没有主键，则InnoDB为您选择一个，它可以是NOT NULL列上定义的第一个UNIQUE键，也可以是系统生成的键。为避免不确定性和额外隐藏列的潜在空间要求，请在_语句中指定PRIMARY KEY子句。

MySQL 通过将现有数据从原始 table 复制到具有所需索引结构的临时 table 中来创建新的聚集索引。数据完全复制到临时 table 后，原始 table 将使用其他临时 table 名重命名。包含新的聚集索引的临时 table 将用原始 table 的名称重命名，然后将原始 table 从数据库中删除。

适用于二级索引操作的联机性能增强不适用于主键索引。 InnoDBtable 的行存储在基于primary key进行组织的clustered index中，形成某些数据库系统称为“索引组织的 table”的 table。由于 table 结构与主键紧密相关，因此重新定义主键仍然需要复制数据。

当对主键进行的操作使用ALGORITHM=INPLACE时，即使仍在复制数据，它也比使用ALGORITHM=COPY更有效，因为：

• ALGORITHM=INPLACE不需要撤消日志记录或相关的重做日志记录。这些操作会增加使用ALGORITHM=COPY的 DDL 语句的开销。

  • 二级索引条目已预先排序，因此可以按 Sequences 加载。

  • 不使用更改缓冲区，因为辅助索引中没有随机访问插入。

如果服务器在创建新的聚集索引时退出，则不会丢失任何数据，但是您必须使用该过程中存在的临时 table 来完成恢复过程。由于很少在大型 table 上重新创建聚 Cluster 索引或重新定义主键，或者在此操作期间遇到系统崩溃的情况很少，因此本手册不提供有关从此方案中恢复的信息。

• 删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

只有ALGORITHM=COPY支持删除主键而不在同一ALTER TABLE语句中添加新的主键。

• 删除主键并添加另一个

ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

数据被大量重组，使其成为昂贵的操作。

Column Operations

下 table 概述了对列操作的在线 DDL 支持。星号 table 示其他信息，异常或依赖项。有关详细信息，请参见语法和用法说明。

table14.12 列操作的在线 DDL 支持

Operation	In Place	Rebuilds Table	允许并发 DML	仅修改元数据
添加列	Yes	Yes	Yes*	No
删除列	Yes	Yes	Yes	No
重命名列	Yes	No	Yes*	Yes
Reordering columns	Yes	Yes	Yes	No
设置列默认值	Yes	No	Yes	Yes
更改列数据类型	No	Yes	No	No
扩展VARCHAR列大小	Yes	No	Yes	Yes
删除列默认值	Yes	No	Yes	Yes
更改自动增量值	Yes	No	Yes	No*
制作一列NULL	Yes	Yes*	Yes	No
制作一列NOT NULL	Yes*	Yes*	Yes	No
修改ENUM或SET列的定义	Yes	No	Yes	Yes

语法和用法说明

• 添加列

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

添加auto-increment列时不允许使用并发 DML。数据被大量重组，使其成为昂贵的操作。至少需要ALGORITHM=INPLACE, LOCK=SHARED。

• 删除列

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

数据被大量重组，使其成为昂贵的操作。

• 重命名列

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

要允许并发 DML，请保持相同的数据类型，仅更改列名。

当您保持相同的数据类型和[NOT] NULL属性时，仅更改列名，即可始终在线执行该操作。

您还可以重命名属于外键约束的列。外键定义会自动更新为使用新的列名。重命名参与外键的列仅适用于ALGORITHM=INPLACE。如果您使用ALGORITHM=COPY子句，或其他一些条件导致该命令在后台使用ALGORITHM=COPY，则ALTER TABLE语句将失败。

ALGORITHM=INPLACE不支持重命名generated column。

• Reordering columns

要重新排序列，请在CHANGE或MODIFY操作中使用FIRST或AFTER。

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

数据被大量重组，使其成为昂贵的操作。

• 更改列数据类型

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

仅ALGORITHM=COPY支持更改列数据类型。

• 扩展VARCHAR列大小

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

VARCHAR列所需的长度字节数必须保持相同。对于大小为 0 到 255 个字节的VARCHAR列，需要一个长度的字节来编码该值。对于大小为 256 字节或更大的VARCHAR列，需要两个长度的字节。结果，就地ALTER TABLE仅支持将VARCHAR列的大小从 0 增大到 255 字节，或从 256 字节增大到更大的大小。就地ALTER TABLE不支持将VARCHAR列的大小从少于 256 个字节增加到等于或大于 256 个字节的大小。在这种情况下，所需的长度字节数从 1 更改为 2，这仅由 table 副本(ALGORITHM=COPY)支持。例如，尝试使用就地ALTER TABLE将单字节字符集的VARCHAR列大小从 VARCHAR(255)更改为 VARCHAR(256)会返回此错误：

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.

不支持使用就地ALTER TABLE减小VARCHAR的大小。减小VARCHAR大小需要 table 副本(ALGORITHM=COPY)。

• 设置列默认值

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INPLACE, LOCK=NONE;

仅修改 table 元数据。默认列值存储在 table 的.frm file中，而不是InnoDB data dictionary中。

• 删除列默认值

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;

• 更改自动增量值

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

修改存储在内存中的值，而不是数据文件。

在使用复制或分片的分布式系统中，有时会将 table 的自动增量计数器重置为特定值。插入 table 中的下一行将为其自动增量列使用指定的值。您还可以在数据仓库环境中使用此技术，在该环境中，您定期清空所有 table 并重新加载它们，然后从 1 重新开始自动递增序列。

• 制作一列NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

重建 table 到位。数据被大量重组，使其成为昂贵的操作。

• 制作一列NOT NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

重建 table 到位。要成功执行此操作，需要STRICT_ALL_TABLES或STRICT_TRANS_TABLES SQL_MODE。如果该列包含 NULL 值，则操作失败。服务器禁止对外键列进行更改，否则可能会导致丢失引用完整性。参见第 13.1.8 节“ ALTER TABLE 语句”。数据被大量重组，使其成为昂贵的操作。

• 修改ENUM或SET列的定义

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;

只要不改变数据类型的存储大小，就可以通过向有效成员值列 table 的* end *添加新的枚举或设置成员来修改ENUM或SET列的定义。例如，将成员添加到具有 8 个成员的SET列会将每个值所需的存储从 1 字节更改为 2 字节；这需要一个 table 副本。在列 table 中间添加成员会导致对现有成员重新编号，这需要 table 副本。

生成的列操作

下 table 概述了对生成的列操作的在线 DDL 支持。有关详细信息，请参见语法和用法说明。

table14.13 生成的列操作的在线 DDL 支持

Operation	In Place	Rebuilds Table	允许并发 DML	仅修改元数据
添加STORED列	No	Yes	No	No
修改STORED列 Sequences	No	Yes	No	No
删除STORED列	Yes	Yes	Yes	No
添加VIRTUAL列	Yes	No	Yes	Yes
修改VIRTUAL列 Sequences	No	Yes	No	No
删除VIRTUAL列	Yes	No	Yes	Yes

语法和用法说明

• 添加STORED列

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;

ADD COLUMN不是已存储列的就地操作(不使用临时 table 完成)，因为该 table 达式必须由服务器评估。

• 修改STORED列 Sequences

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

重建 table 到位。

• 删除STORED列

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

重建 table 到位。

• 添加VIRTUAL列

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INPLACE, LOCK=NONE;

添加虚拟列是对未分区 table 的就地操作。但是，添加虚拟列不能与其他ALTER TABLE操作组合使用。

添加VIRTUAL不是分区 table 的就地操作。

• 修改VIRTUAL列 Sequences

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

• 删除VIRTUAL列

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

删除VIRTUAL列是非分区 table 的就地操作。但是，删除虚拟列不能与其他ALTER TABLE操作组合使用。

删除VIRTUAL不是分区 table 的就地操作。

外键操作

下 table 概述了对外键操作的联机 DDL 支持。星号 table 示其他信息，异常或依赖项。有关详细信息，请参见语法和用法说明。

table14.14 对外键操作的在线 DDL 支持

Operation	In Place	Rebuilds Table	允许并发 DML	仅修改元数据
添加外键约束	Yes*	No	Yes	Yes
删除外键约束	Yes	No	Yes	Yes

语法和用法说明

• 添加外键约束

禁用foreign_key_checks时支持INPLACE算法。否则，仅支持COPY算法。

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)
  REFERENCES tbl2(col2) referential_actions;

• 删除外键约束

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

启用或禁用foreign_key_checks选项可以在线执行外键删除。

如果您不知道特定 table 上的外键约束的名称，请发出以下语句，并在CONSTRAINT子句中找到每个外键的约束名称：

SHOW CREATE TABLE table\G

或者，查询INFORMATION_SCHEMA.TABLE_CONSTRAINTStable，并使用CONSTRAINT_NAME和CONSTRAINT_TYPE列标识外键名称。

您还可以在单个语句中删除外键及其关联的索引：

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

Table Operations

下 table 概述了对 table 操作的在线 DDL 支持。星号 table 示其他信息，异常或依赖项。有关详细信息，请参见语法和用法说明。

table14.15 对 table 操作的在线 DDL 支持

Operation	In Place	Rebuilds Table	允许并发 DML	仅修改元数据
更改ROW_FORMAT	Yes	Yes	Yes	No
更改KEY_BLOCK_SIZE	Yes	Yes	Yes	No
设置持久 table 统计信息	Yes	No	Yes	Yes
指定字符集	Yes	Yes*	No	No
转换字符集	No	Yes*	No	No
优化 table	Yes*	Yes	Yes	No
使用FORCE选项重建	Yes*	Yes	Yes	No
执行空重建	Yes*	Yes	Yes	No
重命名 table 格	Yes	No	Yes	Yes

语法和用法说明

• 更改ROW_FORMAT

ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

数据被大量重组，使其成为昂贵的操作。

有关ROW_FORMAT选项的其他信息，请参见Table Options。

• 更改KEY_BLOCK_SIZE

ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

数据被大量重组，使其成为昂贵的操作。

有关KEY_BLOCK_SIZE选项的其他信息，请参见Table Options。

• 设置持久 table 统计信息选项

ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

仅修改 table 元数据。

永久统计信息包括STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES。有关更多信息，请参见第 14.8.11.1 节“配置持久性优化器统计参数”。

• 指定字符集

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

如果新字符编码不同，则重建 table。

• 转换字符集

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

如果新字符编码不同，则重建 table。

• 优化 table

OPTIMIZE TABLE tbl_name;

具有FULLTEXT索引的 table 不支持就地操作。该操作使用INPLACE算法，但不允许使用ALGORITHM和LOCK语法。

• 使用FORCE选项重建 table 格

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

从 MySQL 5.6.17 开始使用+338++337+`。

• 执行“空”重建

ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

自 MySQL 5.6.17 起使用ALGORITHM=INPLACE。具有FULLTEXT索引的 table 不支持ALGORITHM=INPLACE。

• 重命名 table 格

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INPLACE, LOCK=NONE;

MySQL 重命名与 table* tbl_name *对应的文件，而不进行复制。 (您也可以使用RENAME TABLE语句重命名 table。请参见第 13.1.33 节“ RENAME TABLE 语句”。)专为重命名 table 授予的特权不会迁移到新名称。必须手动更改它们。

Tablespace Operations

下 table 概述了对 table 空间操作的联机 DDL 支持。有关详细信息，请参见语法和用法说明。

table14.16 对 table 空间操作的在线 DDL 支持

Operation	In Place	Rebuilds Table	允许并发 DML	仅修改元数据
启用或禁用每 table 文件 table 空间加密	No	Yes	No	No

语法和用法说明

启用或禁用每 table 文件 table 空间加密

ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;

仅每个 table 文件 table 空间支持加密。有关相关信息，请参见第 14.14 节“ InnoDB 静态数据加密”。

Partitioning Operations

除大多数ALTER TABLE分区子句外，已分区InnoDBtable 的联机 DDL 操作遵循与常规InnoDBtable 相同的规则。

大多数ALTER TABLE分区子句与常规的未分区InnoDBtable 没有通过相同的内部在线 DDL API。结果，在线支持ALTER TABLE分区子句的方式有所不同。

下 table 显示了每个ALTER TABLE分区语句的联机状态。无论使用哪种在线 DDL API，MySQL 都会尽可能地减少数据复制和锁定。

使用ALGORITHM=COPY或仅允许“ ALGORITHM=DEFAULT, LOCK=DEFAULT”的ALTER TABLE分区选项，使用COPY算法对 table 进行重新分区。换句话说，将使用新的分区方案创建一个新的分区 table。新创建的 table 包括ALTER TABLE语句应用的所有更改，并将 table 数据复制到新的 table 结构中。

table14.17 分区操作的在线 DDL 支持

Partitioning Clause	In Place	Permits DML	Notes
PARTITION BY	No	No	许可ALGORITHM=COPY，LOCK={DEFAULT|SHARED|EXCLUSIVE}
ADD PARTITION	No	No	只允许ALGORITHM=DEFAULT，LOCK=DEFAULT。不复制由RANGE或LIST分区的 table 的现有数据。允许对HASH或LIST分区的 table 进行并发查询。 MySQL 在持有共享锁的同时复制数据。
DROP PARTITION	No	No	只允许ALGORITHM=DEFAULT，LOCK=DEFAULT。不复制由RANGE或LIST分区的 table 的现有数据。
DISCARD PARTITION	No	No	只允许ALGORITHM=DEFAULT，LOCK=DEFAULT
IMPORT PARTITION	No	No	只允许ALGORITHM=DEFAULT，LOCK=DEFAULT
TRUNCATE PARTITION	Yes	Yes	不复制现有数据。它只是删除行；它不会更改 table 本身或其任何分区的定义。
COALESCE PARTITION	No	No	只允许ALGORITHM=DEFAULT，LOCK=DEFAULT。并发查询允许对由HASH或LIST分区的 table 进行查询，因为 MySQL 在持有共享锁的同时复制了数据。
REORGANIZE PARTITION	No	No	只允许ALGORITHM=DEFAULT，LOCK=DEFAULT。允许对LINEAR HASH或LIST分区的 table 进行并发查询。 MySQL 持有共享的元数据锁时从受影响的分区复制数据。
EXCHANGE PARTITION	Yes	Yes
ANALYZE PARTITION	Yes	Yes
CHECK PARTITION	Yes	Yes
OPTIMIZE PARTITION	No	No	ALGORITHM和LOCK子句被忽略。重建整个 table。参见第 22.3.4 节“分区维护”。
REBUILD PARTITION	No	No	只允许ALGORITHM=DEFAULT，LOCK=DEFAULT。允许对LINEAR HASH或LIST分区的 table 进行并发查询。 MySQL 持有共享的元数据锁时从受影响的分区复制数据。
REPAIR PARTITION	Yes	Yes
REMOVE PARTITIONING	No	No	许可ALGORITHM=COPY，LOCK={DEFAULT|SHARED|EXCLUSIVE}

分区 table 上的非分区在线ALTER TABLE非分区操作遵循与常规 table 相同的规则。但是，ALTER TABLE在每个 table 分区上执行联机操作，由于在多个分区上执行操作，这导致对系统资源的需求增加。

有关ALTER TABLE分区子句的其他信息，请参见Partitioning Options和第 13.1.8.1 节，“ ALTER TABLE 分区操作”。有关一般分区的信息，请参见第 22 章，分区。