创建或添加二级索引

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 键，也可以是系统生成的键。为了避免不确定性以及可能为额外隐藏列分配的存储空间，请在 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;

在 MySQL 9.0 中，INSTANT 是默认算法。

当 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): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use 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;

在 MySQL 9.0 中，INSTANT 是默认算法。

当使用 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): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use 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

或者，查询信息架构 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 数据休眠加密”。