创建或添加辅助索引

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

在索引被创建时，表仍然可用于读写操作。CREATE INDEX 语句直到所有访问该表的事务都完成后才结束，以确保索引的初始状态反映了表的最新内容。

添加辅助索引的在线DDL支持意味着您可以通过创建表而不包括辅助索引，然后在数据加载完成后添加辅助索引来加速整个过程。

新创建的次要索引只包含在CREATE INDEX或ALTER TABLE语句执行完成时的已提交数据表中。它不包含未提交值、旧版本值或已经标记为删除但还没有从老索引中移除的值。

一些因素影响了该操作的性能、空间使用和语义。详见第17.12.8节，“在线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=INSTANT;

添加主要键

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

在原地重建表。数据被重新组织，变得非常昂贵。ALGORITHM=INPLACE 在某些情况下不能使用，如果列需要转换为NOT NULL。

重新结构聚簇索引总是需要将表数据复制。因此，在创建表时定义主要键，而不是在后期使用ALTER TABLE ... ADD PRIMARY KEY语句。

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

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

如果您创建了一个没有主键的表，InnoDB将自动选择一个，这可能是定义在NOT NULL列上的第一个唯一键，或者是一个系统生成的键。为了避免不确定性和潜在的额外隐藏列空间需求，请将PRIMARY KEY子句作为CREATE TABLE语句的一部分指定。

MySQL 创建了一个新的聚簇索引，通过将原始表中的数据复制到一个临时表中，该临时表具有所需的索引结构。等待所有数据被完全复制到临时表后，原始表将被重命名为不同的临时表名称。临时表，组成新的聚簇索引，将被重命名为原始表的名称，并且原始表将从数据库中删除。

对次要索引操作的在线性能增强不适用于主键索引。InnoDB 表中的行存储在一个聚簇索引中，该索引基于主键，形成一些数据库系统称为“索引组织表”。由于表结构紧密地与主键相关，重新定义主键仍然需要复制数据。

当使用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;

数据重组操作非常昂贵。

添加列

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

INSTANT 是 MySQL 8.4 的默认算法。

以下限制适用于 INSTANT 算法添加列时：

可以在同一个ALTER TABLE语句中添加多个列。例如：

ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

每次执行ALTER TABLE ... ALGORITHM=INSTANT操作时，创建一个新的行版本。该操作可能添加、删除或同时添加和删除一个或多个列。在INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS列中跟踪表的行版本数。每次添加或删除列时，该值将增加。初始值为0。

mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
        WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME    | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 |                  0 |
+---------+--------------------+

当使用ALTER TABLE或OPTIMIZE TABLE操作重建包含瞬态添加或删除列的表时，TOTAL_ROW_VERSIONS值将被重置为0。允许的最大行版本数为64，每个行版本都需要额外的表元数据空间。当达到行版本限制时，使用ALGORITHM=INSTANT的ADD COLUMN和DROP COLUMN操作将被拒绝，并显示一个错误消息，建议使用COPY或INPLACE算法重建表。

ERROR 4080 (HY000): 表test/t1的行版本数已达到最大限制。不能再使用瞬态方式添加或删除列。请使用COPY/INPLACE。

以下INFORMATION_SCHEMA列提供了添加瞬态列时的额外元数据。请查看这些列的描述以获取更多信息。见第28.4.9节，“INFORMATION_ SCHEMA INNODB_ COLUMNS 表”，和第28.4.23节，“INFORMATION_ SCHEMA INNODB_ TABLES 表”。

在添加自增列时，不允许并发DML操作。数据需要重新组织，这是一个昂贵的操作。在最小情况下，需要ALGORITHM=INPLACE, LOCK=SHARED。

使用ALGORITHM=INPLACE添加列时，将重建表。

删除列

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

INSTANT是MySQL 8.4的默认算法。

使用INSTANT算法删除列时，以下限制适用：

可以在同一个ALTER TABLE语句中删除多个列；例如：

ALTER TABLE t1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;

每次使用ALGORITHM=INSTANT添加或删除列时，都会创建一个新的行版本。INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS列跟踪表的行版本数。该值每次在添加或删除列时递增。初始值为0。

mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
        WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME    | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 |                  0 |
+---------+--------------------+

当使用ALTER TABLE或OPTIMIZE TABLE操作重建包含瞬态添加或删除列的表时，TOTAL_ROW_VERSIONS值将被重置为0。每个行版本都需要额外的表元数据空间，因此允许的最大行版本数为64。当达到行版本限制时，使用ALGORITHM=INSTANT的ADD COLUMN和DROP COLUMN操作将被拒绝，并显示错误消息，建议使用COPY或INPLACE算法重建表。

ERROR 4080 (HY000): 表test/t1的行版本数已达到最大值。不能再使用瞬态方式添加或删除列。请使用COPY/INPLACE。

如果使用的算法不是ALGORITHM=INSTANT

重命名一个列

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT;

为了允许并发DML，保持相同的数据类型，只更改列名称。

当您保持相同的数据类型和[NOT] NULL属性，只更改列名称时，这个操作总是可以在线执行的。

重命名一个列，来自另一个表的引用，只能使用ALGORITHM=INPLACE。如果您使用ALGORITHM=INSTANT、ALGORITHM=COPY或其他条件导致操作使用这些算法，ALTER TABLE语句将失败。

ALGORITHM=INSTANT支持重命名虚拟列；ALGORITHM=INPLACE不支持。

ALGORITHM=INSTANT和ALGORITHM=INPLACE都不支持在同一个语句中添加或删除虚拟列时重命名列。在这种情况下，只有ALGORITHM=COPY是支持的。

重新排序列

要重新排序列，使用FIRST或AFTER在CHANGE或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 列需要的长度字节数必须保持不变。对于大小为 0 到 255 字节的 VARCHAR 列，一字节长度字节用于编码值。对于大小为 256 字节或更大的 VARCHAR 列，两个长度字节是必要的。因此，在-place ALTER TABLE 只支持将 VARCHAR 列大小从 0 到 255 字节增加到更大，或者从 256 字节增加到更大。在-place ALTER TABLE 不支持将小于 256 字节的 VARCHAR 列大小增加到等于或更大 256 字节。在这种情况下，需要的长度字节数从 1 变为 2，这是只有表复制（ALGORITHM=COPY）才能支持的。例如，尝试使用 in-place 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.

减少VARCHAR大小使用in-placeALTER TABLE不受支持。减少VARCHAR大小需要表复制（ALGORITHM=COPY）。

设置列默认值

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

仅修改表元数据。默认列值存储在数据字典中。

删除列默认值

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

更改自增值

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

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

在使用复制或分片的分布式系统中，您有时需要将表的自增计数器重置到特定值。下一个插入到表中的行将使用指定值作为其自增列。您也可能在数据仓库环境中使用这种技术， periodically清空所有表并重新加载它们，然后从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_ TABLESSQL_MODE以成功执行操作。如果列包含NULL值，操作将失败。服务器禁止对可能导致引用完整性丢失的外键列进行更改。请参阅第15.1.9节，“ALTER TABLE Statement”。数据将被重组，导致昂贵的操作。

修改ENUM或SET列定义

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

通过添加新枚举或集合成员到有效成员值列表末尾来修改ENUM或SET列定义的存储大小不变，可以立即或在原地进行修改。例如，对于具有8个成员的SET列添加一个成员，需要将每个值的存储大小从1字节更改为2字节；这需要对表进行复制。添加到列表中间的成员会导致现有成员重新编号，这也需要对表进行复制。

添加STORED列

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

ADD COLUMN不是存储列的in-place操作（使用临时表），因为表达式必须由服务器评估。

修改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=INSTANT;

可以立即或在非分区表中进行in-place操作。

添加VIRTUAL不是分区表的in-place操作。

修改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=INSTANT;

可以立即或在非分区表中进行in-place操作。

添加外键约束

支持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

或者，查询信息SchemaTABLE_CONSTRAINTS表，并使用CONSTRAINT_NAME和CONSTRAINT_TYPE列来确定外键名称。

您也可以在单个语句中删除外键和其关联索引：

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

更改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选项的更多信息，请见Table Options.

设置持久表统计选项

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

只修改表元数据。

持久统计包括STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES。更多信息，请见第17.8.10.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索引的表进行in-place操作。该操作使用INPLACE算法，但ALGORITHM和LOCK语句不被允许。

使用FORCE选项重建表

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

从MySQL 5.6.17开始使用ALGORITHM=INPLACE。ALGORITHM=INPLACE不支持在表中包含FULLTEXT索引的表。

执行“null”重建

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

从MySQL 5.6.17开始使用ALGORITHM=INPLACE。ALGORITHM=INPLACE不支持在表中包含FULLTEXT索引的表。

重命名表

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

可以立即或在原地重命名表格。MySQL 将对应于表格 tbl_name 的文件重新命名，而不需要创建副本。（您也可以使用RENAME TABLE 语句重命名表格。见第15.1.36节，“RENAME TABLE Statement”。）对重命名后的表格授予的特定权限不会被迁移到新名称中，需要手动更改。

重命名通用表space

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

ALTER TABLESPACE ... RENAME TO 使用 INPLACE 算法，但不支持 ALGORITHM 子句。

启用或禁用通用表space 加密

ALTER TABLESPACE tablespace_name ENCRYPTION='Y';

ALTER TABLESPACE ... 加密 使用INPLACE算法，但不支持ALGORITHM子句。

有关相关信息，请参见第17.13节，“InnoDB 数据静态加密”。

启用或禁用文件表空间加密

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

有关相关信息，请参见第17.13节，“InnoDB 数据静态加密”。