创建或添加辅助索引

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;

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

重构 聚集索引 始终需要复制表数据。因此，最好在创建表时定义 主键，而不是在以后发出 ALTER TABLE ... ADD PRIMARY KEY。

当您创建 UNIQUE 或 PRIMARY KEY 索引时，MySQL 需要进行一些额外的操作。对于 UNIQUE 索引，MySQL 会检查表中是否存在键值的重复值。对于 PRIMARY KEY 索引，MySQL 还会检查 PRIMARY KEY 列中是否包含 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 列上的第一个 UNIQUE 键，也可以是系统生成的键。为了避免不确定性和潜在的额外隐藏列的空间需求，请将 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。

重新排序列

要重新排序列，请在 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，这只有表复制 (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 大小需要进行表复制 (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;

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

在使用复制或分片的分布式系统中，有时需要将表的自动递增计数器重置为特定值。插入到表中的下一行使用指定的值作为其自动递增列的值。您也可以在数据仓库环境中使用此技术，在该环境中您定期清空所有表并重新加载它们，并从 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 值，则操作将失败。服务器禁止更改可能导致引用完整性丢失的外键列。请参阅 第 15.1.9 节“ALTER TABLE 语句”。数据将被大量重新组织，这使其成为一项昂贵的操作。

修改 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 对于存储列不是原地操作（无需使用临时表完成），因为表达式必须由服务器计算。

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

对于非分区表，可以立即或原地添加虚拟列。

对于分区表，添加 VIRTUAL 不是原地操作。

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

对于非分区表，可以立即或原地删除 VIRTUAL 列。

添加外键约束

当 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 选项在线删除外键。

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

SHOW CREATE TABLE table\G

或者，查询 Information Schema TABLE_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 选项的更多信息，请参阅 表选项。

设置持久表统计信息选项

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 索引的表，不支持原地操作。该操作使用 INPLACE 算法，但不允许 ALGORITHM 和 LOCK 语法。

使用 FORCE 选项重建表

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

从 MySQL 5.6.17 开始使用 ALGORITHM=INPLACE。对于具有 FULLTEXT 索引的表，不支持 ALGORITHM=INPLACE。

执行“空”重建

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

从 MySQL 5.6.17 开始使用 ALGORITHM=INPLACE。对于具有 FULLTEXT 索引的表，不支持 ALGORITHM=INPLACE。

重命名表

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

可以立即或原地重命名表。MySQL 会重命名与表 tbl_name 相对应的文件，而不会进行复制。（您也可以使用 RENAME TABLE 语句来重命名表。请参阅 第 15.1.36 节“RENAME TABLE 语句”。）专门为重命名的表授予的权限不会迁移到新名称。它们必须手动更改。

重命名一般表空间

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

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

启用或禁用一般表空间加密

ALTER TABLESPACE tablespace_name ENCRYPTION='Y';

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

有关相关信息，请参阅 第 17.13 节“InnoDB 数据休眠加密”。

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

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

有关相关信息，请参阅 第 17.13 节“InnoDB 数据休眠加密”。