Documentation Home

14.13.1 在线DDL操作

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

索引操作

下表概述了对索引操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.10 索引操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
创建或添加二级索引 是的 是的
删除索引 是的 是的 是的
重命名索引 是的 是的 是的
添加FULLTEXT索引 是的* 不*
添加SPATIAL索引 是的
更改索引类型 是的 是的 是的

语法和使用说明
  • 创建或添加二级索引

    CREATE INDEX name ON table (col_list);
    ALTER TABLE tbl_name ADD INDEX name (col_list);

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

    在线 DDL 支持添加二级索引意味着您通常可以通过创建不带二级索引的表,然后在加载数据后添加二级索引来加快创建和加载表及关联索引的整个过程。

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

    如果服务器在创建二级索引时退出,则在恢复时,MySQL 会删除任何部分创建的索引。您必须重新运行ALTER TABLE orCREATE INDEX语句。

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

  • 删除索引

    DROP INDEX name ON table;
    ALTER TABLE tbl_name DROP INDEX name;

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

  • 重命名索引

    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);

    FULLTEXT如果没有用户定义的 FTS_DOC_ID列, 添加第一个索引会重建表。FULLTEXT可以在不重建表的情况下添加 额外 的索引。

  • 添加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;

主键操作

下表概述了对主键操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。请参阅 语法和使用说明

表 14.11 对主键操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
添加主键 是的* 是的* 是的
删除主键 是的
删除主键并添加另一个 是的 是的 是的

语法和使用说明
  • 添加主键

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

    就地重建表。数据被大量重组,使其成为一项昂贵的操作。 ALGORITHM=INPLACE如果必须将列转换为 ,则在某些情况下不允许使用 NOT NULL.

    重组 聚集索引 总是需要复制表数据。因此,最好在创建表时 定义主键ALTER TABLE ... ADD PRIMARY KEY,而不是稍后发布。

    当你创建一个UNIQUEPRIMARY KEY索引时,MySQL 必须做一些额外的工作。对于UNIQUE索引,MySQL 检查表中是否包含键的重复值。对于PRIMARY KEY索引,MySQL 还会检查没有任何PRIMARY KEY 列包含NULL.

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

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

    MySQL 通过将现有数据从原始表复制到具有所需索引结构的临时表来创建新的聚集索引。一旦数据被完全复制到临时表中,原始表将被重命名为不同的临时表名称。包含新聚簇索引的临时表被重命名为原始表的名称,并且原始表从数据库中删除。

    适用于二级索引操作的在线性能增强不适用于主键索引。InnoDB 表的行存储在 基于 主键组织的聚簇索引中,形成一些数据库系统所谓的索引组织表。由于表结构与主键紧密相关,重新定义主键仍然需要复制数据。

    当对主键的操作使用 ALGORITHM=INPLACE时,即使数据仍然被复制,它也比使用更高效, ALGORITHM=COPY因为:

    • 不需要撤消日志记录或关联的重做日志记录ALGORITHM=INPLACE。这些操作增加了使用 ALGORITHM=COPY.

    • 二级索引条目是预先排序的,因此可以按顺序加载。

    • 未使用更改缓冲区,因为二级索引中没有随机访问插入。

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

  • 删除主键

    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;

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

列操作

下表概述了对列操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.12 列操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
添加列 是的 是的 是的*
删除列 是的 是的 是的
重命名列 是的 是的* 是的
重新排序列 是的 是的 是的
设置列默认值 是的 是的 是的
更改列数据类型 是的
扩展VARCHAR列大小 是的 是的 是的
删除列默认值 是的 是的 是的
更改自动增量值 是的 是的 不*
制作专栏NULL 是的 是的* 是的
制作专栏NOT NULL 是的* 是的* 是的
修改ENUMor SET列的定义 是的 是的 是的

语法和使用说明
  • 添加列

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

    添加自 增列时不允许并发 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不支持重命名生成的列

  • 重新排序列

    要对列重新排序,请使用FIRSTor AFTERin CHANGEor MODIFY操作。

    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必须保持不变。对于VARCHAR大小为 0 到 255 字节的列,需要一个长度字节来对值进行编码。对于VARCHAR 大小为 256 字节或更多的列,需要两个长度字节。因此,就地ALTER TABLE仅支持将 VARCHAR列大小从 0 字节增加到 255 字节,或从 256 字节增加到更大的大小。就地 ALTER TABLE不支持增加a的大小 VARCHAR从小于 256 字节到等于或大于 256 字节的列。在这种情况下,所需的长度字节数从 1 变为 2,这仅由表副本(ALGORITHM=COPY)支持。例如,尝试VARCHAR就地使用将单字节字符集的列大小从 VARCHAR(255) 更改为 VARCHAR(256) 会ALTER TABLE返回此错误:

    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.
    笔记

    列的字节长度VARCHAR取决于字符集的字节长度。

    不支持VARCHAR使用就地 减小尺寸。ALTER TABLE减小VARCHAR 大小需要一个表副本 ( ALGORITHM=COPY)。

  • 设置列默认值

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

    仅修改表元数据。默认列值存储在表的.frm 文件 中,而不是InnoDB 数据字典中。

  • 删除列默认值

    ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;
  • 更改自动增量值

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

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

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

  • 制作专栏NULL

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

    就地重建表。数据被大量重组,使其成为一项昂贵的操作。

  • 制作专栏NOT NULL

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

    就地重建表。 STRICT_ALL_TABLES或者 STRICT_TRANS_TABLES SQL_MODE是操作成功所必需的。如果该列包含 NULL 值,则操作失败。服务器禁止更改可能导致引用完整性丢失的外键列。请参阅第 13.1.8 节,“ALTER TABLE 语句”。数据被大量重组,使其成为一项昂贵的操作。

  • 修改ENUMor SET列 的定义

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

    通过将新的枚举或集合成员添加到有效成员值列表的末尾ENUM来修改or 列 的定义 可以就地执行,只要数据类型的存储大小不变即可。例如,向 具有 8 个成员的列添加一个成员会将每个值所需的存储空间从 1 个字节更改为 2 个字节;这需要一个表副本。在列表中间添加成员会导致现有成员重新编号,这需要表副本。 SETSET

生成的列操作

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

表 14.13 生成列操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
添加STORED 是的
修改STORED列顺序 是的
删除STORED 是的 是的 是的
添加VIRTUAL 是的 是的 是的
修改VIRTUAL列顺序 是的
删除VIRTUAL 是的 是的 是的

语法和使用说明
  • 添加STORED

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

    ADD COLUMN不是存储列的就地操作(在不使用临时表的情况下完成),因为表达式必须由服务器计算。

  • 修改STORED列顺序

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

    就地重建表。

  • 删除STORED

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

    就地重建表。

  • 添加VIRTUAL

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

    添加虚拟列是非分区表的就地操作。但是,添加虚拟列不能与其他ALTER TABLE操作结合使用。

    添加 aVIRTUAL不是分区表的就地操作。

  • 修改VIRTUAL列顺序

    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列是非分区表的就地操作。但是,删除虚拟列不能与其他 ALTER TABLE操作结合使用。

    删除 aVIRTUAL不是分区表的就地操作。

外键操作

下表概述了对外键操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.14 外键操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
添加外键约束 是的* 是的 是的
删除外键约束 是的 是的 是的

语法和使用说明
  • 添加外键约束

    禁用时支持 该INPLACE算法 。foreign_key_checks否则,仅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启用或禁用选项的情况下在线执行删除外键。

    如果您不知道特定表的外键约束名称,请执行以下语句并在 CONSTRAINT子句中为每个外键查找约束名称:

    SHOW CREATE TABLE table\G

    或者,查询 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 表并使用CONSTRAINT_NAMECONSTRAINT_TYPE列来标识外键名称。

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

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

如果正在更改的表中已经存在外键(即它是 包含子句的子表FOREIGN KEY ... REFERENCE),则附加限制适用于在线 DDL 操作,即使是那些不直接涉及外键列的操作:

  • 如果对父表的更改通过使用or 参数 的or 子句 ALTER TABLE导致子表中的相关更改,则子表上的 an 可以等待另一个事务提交。ON UPDATEON DELETECASCADESET NULL

  • 同样,如果一个表是外键关系中的 父表,即使它不包含任何子句,如果, , or 语句导致 子表中 的or操作FOREIGN KEY,它也可以等待ALTER TABLE完成。INSERTUPDATEDELETEON UPDATEON DELETE

表操作

下表概述了表操作的在线 DDL 支持。星号表示附加信息、异常或依赖关系。有关详细信息,请参阅 语法和使用说明

表 14.15 表操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
改变ROW_FORMAT 是的 是的 是的
改变KEY_BLOCK_SIZE 是的 是的 是的
设置持久表统计信息 是的 是的 是的
指定字符集 是的 是的*
转换字符集 是的*
优化表 是的* 是的 是的
FORCE使用选项重建 是的* 是的 是的
执行空重建 是的* 是的 是的
重命名表 是的 是的 是的

语法和使用说明
  • 改变ROW_FORMAT

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

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

    有关该 ROW_FORMAT选项的其他信息,请参阅 表格选项

  • 改变KEY_BLOCK_SIZE

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

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

    有关该 KEY_BLOCK_SIZE选项的其他信息,请参阅 表格选项

  • 设置持久表统计选项

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

    仅修改表元数据。

    持久性统计信息包括 STATS_PERSISTENTSTATS_AUTO_RECALCSTATS_SAMPLE_PAGES。有关详细信息,请参阅第 14.8.11.1 节,“配置持久优化器统计参数”

  • 指定字符集

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

    如果新字符编码不同,则重建表。

  • 转换字符集

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

    如果新字符编码不同,则重建表。

  • 优化表

    OPTIMIZE TABLE tbl_name;

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

  • FORCE使用选项 重建表

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

    ALGORITHM=INPLACE从 MySQL 5.6.17 开始 使用ALGORITHM=INPLACE不支持带FULLTEXT 索引的表。

  • 执行“空”重建

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

    从 MySQL 5.6.17 开始使用ALGORITHM=INPLACEALGORITHM=INPLACE不支持带FULLTEXT 索引的表。

  • 重命名表

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

    MySQL 重命名与表对应的文件 tbl_name而不制作副本。(您也可以使用该RENAME TABLE语句重命名表。请参阅 第 13.1.33 节,“RENAME TABLE 语句”。)专门为重命名的表授予的权限不会迁移到新名称。它们必须手动更改。

表空间操作

下表概述了对表空间操作的在线 DDL 支持。有关详细信息,请参阅 语法和使用说明

表 14.16 表空间操作的在线 DDL 支持

手术 到位 重建表 允许并发 DML 仅修改元数据
启用或禁用 file-per-table 表空间加密 是的

语法和使用说明

启用或禁用 file-per-table 表空间加密

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

加密仅支持 file-per-table 表空间。有关相关信息,请参阅 第 14.14 节,“InnoDB 静态数据加密”

分区操作

除了大多数ALTER TABLE分区子句之外,分区InnoDB表的在线 DDL 操作遵循适用于常规InnoDB表的相同规则。

大多数ALTER TABLE分区子句不通过与常规非分区InnoDB表相同的内部在线 DDL API。因此,对ALTER TABLE分区子句的在线支持各不相同。

下表显示了每个 ALTER TABLE分区语句的在线状态。无论使用何种在线 DDL API,MySQL 都会尝试尽可能减少数据复制和锁定。

ALTER TABLEALGORITHM=COPY使用或仅允许 ALGORITHM=DEFAULT, LOCK=DEFAULT的分区选项,使用该COPY算法对表进行重新分区 。换句话说,一个新的分区表是用新的分区方案创建的。新创建的表包括 ALTER TABLE语句应用的任何更改,表数据被复制到新的表结构中。

表 14.17 分区操作的在线 DDL 支持

分割条款 到位 允许 DML 笔记
PARTITION BY 许可证ALGORITHM=COPYLOCK={DEFAULT|SHARED|EXCLUSIVE}
ADD PARTITION 只允许ALGORITHM=DEFAULT, LOCK=DEFAULT. 不复制按RANGE或 分区的表的现有数据LISTHASH对于按或 分区的表,允许并发查询LIST。MySQL 在持有共享锁的同时复制数据。
DROP PARTITION 只允许ALGORITHM=DEFAULT, LOCK=DEFAULT. 不复制按RANGE或 分区的表的现有数据LIST
DISCARD PARTITION 只允许ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION 只允许ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION 是的 是的 不复制现有数据。它只是删除行;它不会改变表本身或其任何分区的定义。
COALESCE PARTITION 只允许ALGORITHM=DEFAULT, LOCK=DEFAULT. HASH对于由or分区的表,允许并发查询 LIST,因为 MySQL 在持有共享锁的同时复制数据。
REORGANIZE PARTITION 只允许ALGORITHM=DEFAULT, LOCK=DEFAULT. LINEAR HASH对于按或分区的表,允许并发查询LIST。MySQL 在持有共享元数据锁的同时从受影响的分区复制数据。
EXCHANGE PARTITION 是的 是的
ANALYZE PARTITION 是的 是的
CHECK PARTITION 是的 是的
OPTIMIZE PARTITION ALGORITHMLOCK子句被忽略。重建整个表。请参阅 第 22.3.4 节,“分区的维护”
REBUILD PARTITION 只允许ALGORITHM=DEFAULT, LOCK=DEFAULT. LINEAR HASH对于按或分区的表,允许并发查询LIST。MySQL 在持有共享元数据锁的同时从受影响的分区复制数据。
REPAIR PARTITION 是的 是的
REMOVE PARTITIONING 许可证ALGORITHM=COPYLOCK={DEFAULT|SHARED|EXCLUSIVE}

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

有关ALTER TABLE分区子句的其他信息,请参阅 分区选项第 13.1.8.1 节,“ALTER TABLE 分区操作”。有关分区的一般信息,请参阅 第 22 章,分区