创建或添加次要索引

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 会检查表中没有重复的键值。对于 PRIMARY KEY 索引，MySQL 还会检查主键列中没有 NULL 值。

当您使用 ALGORITHM=COPY 子句添加主键时，MySQL 会将关联列中的 NULL 值转换为默认值：数字为 0，字符基于列和 BLOBs 为空字符串，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 将为您选择一个，这可能是第一个 UNIQUE 键定义在 NOT NULL 列上，或者是一个系统生成的键。为了避免不确定性和潜在的空间需求，建议在 CREATE TABLE 语句中指定 PRIMARY KEY 子句。

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.3 中的默认算法。

以下限制适用于使用 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，因为每个行版本都需要额外的表元数据空间。当达到行版本限制时，ADD COLUMN 和 DROP COLUMN 操作使用 ALGORITHM=INSTANT 将被拒绝，并显示错误消息，建议使用 COPY 或 INPLACE 算法。

ERROR 4080 (HY000): 表 test/t1 的行版本达到最大限制。无法再使用 ALGORITHM=INSTANT 添加或删除列。请使用 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.3 中的默认算法。

使用 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 列的定义，通过将新枚举或集合成员添加到有效成员值列表的末尾，可以实时或就地执行，只要存储大小不变。例如，将成员添加到 SET 列中，该列有 8 个成员，改变每个值的存储大小从 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 列可以即时或在非分区表中就地执行。

添加外键约束

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

或者，查询信息模式 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 索引的表。

执行“空”重建

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

从 MySQL 5.6.17 开始使用 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 语句”。）授予特定表的权限不会被迁移到新名称。它们必须手动更改。

重命名通用表空间

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 静态数据加密”。