创建或添加二级索引Creating or adding a secondary index

CREATE INDEX name ON table (col_list);

ALTER TABLE tbl_name ADD INDEX name (col_list);

在创建索引的过程中，表仍然可以进行读写操作。CREATE INDEX 语句仅在访问表的所有事务完成后才会结束，这样索引的初始状态就能反映表的最新内容。The table remains available for read and write operations while the index is being created. The CREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.

支持在线 DDL 添加二级索引意味着，通常你可以通过先创建没有二级索引的表，然后在数据加载完成后再添加二级索引，从而加快创建和加载表及相关索引的总体过程。Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.

新创建的二级索引只包含在 CREATE INDEX 或 ALTER TABLE 语句执行完成时表中的已提交数据。它不包含任何未提交的值、值的旧版本，或已标记为删除但尚未从旧索引中移除的值。A newly created secondary index contains only the committed data in the table at the time the CREATE INDEX or ALTER TABLE statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index.

一些因素会影响此操作的性能、空间使用和语义。有关详细信息，请参见第 17.12.8 节，“在线 DDL 限制”。Some factors affect the performance, space usage, and semantics of this operation. For details, see Section 17.12.8, “Online DDL Limitations”.

删除索引Dropping an index

DROP INDEX name ON table;

ALTER TABLE tbl_name DROP INDEX name;

在索引被删除的过程中，表仍然可以进行读写操作。DROP INDEX 语句仅在所有访问该表的事务完成后才会执行完毕，从而使索引的初始状态反映表的最新内容。The table remains available for read and write operations while the index is being dropped. The DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.

重命名索引Renaming an index

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

添加 FULLTEXT 索引Adding a FULLTEXT index

CREATE FULLTEXT INDEX name ON table(column);

如果没有用户定义的FTS_DOC_ID列，添加第一个FULLTEXT索引会重建表。可以在不重建表的情况下添加额外的FULLTEXT索引。Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Additional FULLTEXT indexes may be added without rebuilding the table.

添加 SPATIAL 索引Adding a SPATIAL index

CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

更改索引类型（USING {BTREE | HASH}）Changing the index type (USING {BTREE | HASH})

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INSTANT;

添加主键Adding a primary key

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

就地重建表。数据会被大幅重组，因此这是一个开销很大的操作。ALGORITHM=INPLACE 在某些条件下不允许使用，如果列必须转换为 NOT NULL。Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.

重构 聚集索引 总是需要复制表数据。因此，最好在创建表时定义 主键，而不是在之后执行 ALTER TABLE ... ADD PRIMARY KEY。Restructuring the clustered index always requires copying of table data. Thus, it is best to define the primary key when you create a table, rather than issuing ALTER TABLE ... ADD PRIMARY KEY later.

当你创建一个 UNIQUE 或 PRIMARY KEY 索引时，MySQL 必须做一些额外的工作。对于 UNIQUE 索引，MySQL 会检查表中是否存在键的重复值。对于 PRIMARY KEY 索引，MySQL 还会检查 PRIMARY KEY 列中是否包含 NULL。When you create a UNIQUE or PRIMARY KEY index, MySQL must do some extra work. For UNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, MySQL also checks that none of the PRIMARY KEY columns contains a NULL.

当您使用 ALGORITHM=COPY 子句添加主键时，MySQL 会将相关列中的 NULL 值转换为默认值：数字为 0，基于字符的列和 BLOB 为空字符串，DATETIME 为 0000-00-00 00:00:00。这是一种非标准行为，Oracle 建议您不要依赖。仅当 SQL_MODE 设置包含 strict_trans_tables 或 strict_all_tables 标志时，才允许使用 ALGORITHM=INPLACE 添加主键；当 SQL_MODE 设置为严格模式时，允许使用 ALGORITHM=INPLACE，但如果请求的主键列包含 NULL 值，语句仍可能失败。ALGORITHM=INPLACE 的行为更符合标准规范。When you add a primary key using the ALGORITHM=COPY clause, MySQL converts NULL values in the associated columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for DATETIME. This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using ALGORITHM=INPLACE is only permitted when the SQL_MODE setting includes the strict_trans_tables or strict_all_tables flags; when the SQL_MODE setting is strict, ALGORITHM=INPLACE is permitted, but the statement can still fail if the requested primary key columns contain NULL values. The ALGORITHM=INPLACE behavior is more standard-compliant.

如果你创建一个没有主键的表，InnoDB 会为你选择一个主键，这可能是第一个在 NOT NULL 列上定义的 UNIQUE 键，或者是系统生成的键。为了避免不确定性及额外隐藏列可能带来的空间需求，请在 CREATE TABLE 语句中指定 PRIMARY KEY 子句。If you create a table without a primary key, InnoDB chooses one for you, which can be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify the PRIMARY KEY clause as part of the CREATE TABLE statement.

MySQL 通过将原始表中的现有数据复制到具有所需索引结构的临时表来创建新的聚簇索引。一旦数据完全复制到临时表中，原始表将被重命名为不同的临时表名。包含新聚簇索引的临时表被重命名为原始表的名称，并且原始表从数据库中删除。MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database.

适用于二级索引操作的在线性能增强不适用于主键索引。InnoDB 表的行存储在一个聚集索引中，该索引是基于主键组织的，形成一些数据库系统所称的“索引组织表”。由于表结构与主键紧密相关，因此重新定义主键仍然需要复制数据。The online performance enhancements that apply to operations on secondary indexes do not apply to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key, forming what some database systems call an “index-organized table”. Because the table structure is closely tied to the primary key, redefining the primary key still requires copying the data.

当对主键进行操作时使用ALGORITHM=INPLACE，即使数据仍然被复制，它仍然比使用ALGORITHM=COPY更高效，因为：When an operation on the primary key uses ALGORITHM=INPLACE, even though the data is still copied, it is more efficient than using ALGORITHM=COPY because:

删除主键Dropping a primary key

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

只有 ALGORITHM=COPY 支持在同一个 ALTER TABLE 语句中删除主键而不添加新的主键。Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.

删除一个主键并添加另一个Dropping a primary key and adding another

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

数据会被大量重组，使其成为一个开销较大的操作。Data is reorganized substantially, making it an expensive operation.

添加列Adding a column

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

INSTANT 是 MySQL 8.4 的默认算法。INSTANT is the default algorithm in MySQL 8.4.

当 INSTANT 算法添加列时，适用以下限制：The following limitations apply when the INSTANT algorithm adds a column:

在同一个 ALTER TABLE 语句中可以添加多个列。例如：Multiple columns may be added in the same ALTER TABLE statement. For example:

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。A new row version is created after each ALTER TABLE ... ALGORITHM=INSTANT operation that adds one or more columns, drops one or more columns, or adds and drops one or more columns in the same operation. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 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（在 MySQL 9.1.0 中为 255），因为每个行版本都需要额外的表元数据空间。当达到行版本限制时，使用 ALGORITHM=INSTANT 的 ADD COLUMN 和 DROP COLUMN 操作将被拒绝，并显示建议使用 COPY 或 INPLACE 算法重建表的错误消息。When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64 (255 as of MySQL 9.1.0), as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.

错误 4092 (HY000)：表 test/t1 达到最大行版本。无法再立即添加或删除列。请使用 COPY/INPLACE。ERROR 4092 (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 表”。The following INFORMATION_SCHEMA columns provide additional metadata for instantly added columns. Refer to the descriptions of those columns for more information. See Section 28.4.9, “The INFORMATION_SCHEMA INNODB_COLUMNS Table”, and Section 28.4.23, “The INFORMATION_SCHEMA INNODB_TABLES Table”.

在添加自动递增列时，不允许并发 DML 操作。数据会被大量重新组织，这是一个开销很大的操作。至少需要ALGORITHM=INPLACE, LOCK=SHARED。Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. At a minimum, ALGORITHM=INPLACE, LOCK=SHARED is required.

如果使用ALGORITHM=INPLACE来添加列，表将被重建。The table is rebuilt if ALGORITHM=INPLACE is used to add a column.

删除列Dropping a column

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

INSTANT 是 MySQL 8.4 的默认算法。INSTANT is the default algorithm in MySQL 8.4.

在使用INSTANT算法删除列时，适用以下限制：The following limitations apply when the INSTANT algorithm is used to drop a column:

可以在同一个 ALTER TABLE 语句中删除多个列；例如：Multiple columns may be dropped in the same ALTER TABLE statement; for example:

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

每次使用 ALGORITHM=INSTANT 添加或删除列时，都会创建一个新的行版本。INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS 列用于跟踪表的行版本数量。每次即时添加或删除列时，该值会增加。初始值为 0。Each time a column is added or dropped using ALGORITHM=INSTANT, a new row version is created. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 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（在 MySQL 9.1.0 中为 255），因为每个行版本都需要额外的表元数据空间。当达到行版本限制时，使用 ALGORITHM=INSTANT 的 ADD COLUMN 和 DROP COLUMN 操作将被拒绝，并显示建议使用 COPY 或 INPLACE 算法重建表的错误消息。When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64 (255 as of MySQL 9.1.0), as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.

错误 4092 (HY000)：表 test/t1 达到最大行版本。无法再立即添加或删除列。请使用 COPY/INPLACE。ERROR 4092 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.

如果使用除 ALGORITHM=INSTANT 以外的算法，数据会被大量重新组织，这会是一个昂贵的操作。If an algorithm other than ALGORITHM=INSTANT is used, data is reorganized substantially, making it an expensive operation.

重命名列Renaming a column

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

为了允许并发 DML，请保持相同的数据类型，仅更改列名。To permit concurrent DML, keep the same data type and only change the column name.

当你保持相同的数据类型和 [NOT] NULL 属性，仅更改列名时，该操作总是可以在线执行。When you keep the same data type and [NOT] NULL attribute, only changing the column name, the operation can always be performed online.

重命名被另一个表引用的列仅允许使用 ALGORITHM=INPLACE。如果你使用 ALGORITHM=INSTANT、ALGORITHM=COPY 或其他造成该操作使用这些算法的条件，则 ALTER TABLE 语句会失败。Renaming a column referenced from another table is only permitted with ALGORITHM=INPLACE. If you use ALGORITHM=INSTANT, ALGORITHM=COPY, or some other condition that causes the operation to use those algorithms, the ALTER TABLE statement fails.

ALGORITHM=INSTANT 支持重命名虚拟列；ALGORITHM=INPLACE 不支持。ALGORITHM=INSTANT supports renaming a virtual column; ALGORITHM=INPLACE does not.

ALGORITHM=INSTANT 和 ALGORITHM=INPLACE 在同一语句中添加或删除虚拟列时不支持重命名列。在这种情况下，只支持 ALGORITHM=COPY。ALGORITHM=INSTANT and ALGORITHM=INPLACE do not support renaming a column when adding or dropping a virtual column in the same statement. In this case, only ALGORITHM=COPY is supported.

重新排序列Reordering columns

要重新排序列，请在 CHANGE 或 MODIFY 操作中使用 FIRST 或 AFTER。To reorder columns, use FIRST or AFTER in CHANGE or MODIFY operations.

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

数据会被大量重组，使其成为一个开销较大的操作。Data is reorganized substantially, making it an expensive operation.

更改列数据类型Changing the column data type

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

更改列的数据类型仅支持 ALGORITHM=COPY。Changing the column data type is only supported with ALGORITHM=COPY.

扩展 VARCHAR 列大小Extending VARCHAR column size

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR使用就地方式将单字节字符集的列大小从 VARCHAR(255) 改为 VARCHAR(256)ALTER TABLE返回此错误：The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE returns this error:

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)。Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).

设置列默认值Setting a column default value

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

仅修改表元数据。默认列值存储在 数据字典 中。Only modifies table metadata. Default column values are stored in the data dictionary.

删除列的默认值Dropping a column default value

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

更改自增值Changing the auto-increment value

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

修改存储在内存中的值，而不是数据文件。Modifies a value stored in memory, not the data file.

在使用复制或分片的分布式系统中，有时你会将表的自增计数器重置为特定值。下一行插入表时，其自增列将使用指定的值。你也可能在数据仓库环境中使用这种技术，在这种环境中，你会定期清空所有表并重新加载它们，并将自增序列从 1 重新开始。In a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically empty all the tables and reload them, and restart the auto-increment sequence from 1.

将列设置为 NULLMaking a column NULL

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

在原位重建表。数据会被大幅重组，因此这是一个开销较大的操作。Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.

将列设置为 NOT NULLMaking a column 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 语句”。数据将被大幅重组，因此这是一个开销较大的操作。Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 15.1.9, “ALTER TABLE Statement”. Data is reorganized substantially, making it an expensive operation.

修改 ENUM 或 SET 列的定义Modifying the definition of an ENUM or SET column

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 字节；这需要进行表复制。在列表中间添加成员会导致现有成员重新编号，这也需要进行表复制。Modifying the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

添加一个 STORED 列Adding a STORED column

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

ADD COLUMN 对于存储列而言不是就地操作（不使用临时表完成），因为表达式必须由服务器评估。ADD COLUMN is not an in-place operation for stored columns (done without using a temporary table) because the expression must be evaluated by the server.

修改 STORED 列顺序Modifying STORED column order

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

在原位重建表。Rebuilds the table in place.

删除一个 STORED 列Dropping a STORED column

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

在原位重建表。Rebuilds the table in place.

添加一个 VIRTUAL 列Adding a VIRTUAL column

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

对于非分区表，添加虚拟列可以立即或就地执行。Adding a virtual column can be performed instantly or in place for non-partitioned tables.

对于分区表，添加 VIRTUAL 不是就地操作。Adding a VIRTUAL is not an in-place operation for partitioned tables.

修改 VIRTUAL 列顺序Modifying VIRTUAL column order

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

删除一个 VIRTUAL 列Dropping a VIRTUAL column

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;

对于非分区表，删除 VIRTUAL 列可以立即或就地执行。Dropping a VIRTUAL column can be performed instantly or in place for non-partitioned tables.

添加外键约束Adding a foreign key constraint

当 foreign_key_checks 被禁用时，支持 INPLACE 算法。否则，仅支持 COPY 算法。The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)
  REFERENCES tbl2(col2) referential_actions;

删除外键约束Dropping a foreign key constraint

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

删除外键可以在打开或关闭 foreign_key_checks 选项的情况下在线执行。Dropping a foreign key can be performed online with the foreign_key_checks option enabled or disabled.

如果您不知道特定表上的外键约束名称，请执行以下语句，并在每个外键的 CONSTRAINT 子句中查找约束名称：If you do not know the names of the foreign key constraints on a particular table, issue the following statement and find the constraint name in the CONSTRAINT clause for each foreign key:

SHOW CREATE TABLE table\G

或者，查询信息模式 TABLE_CONSTRAINTS 表，并使用 CONSTRAINT_NAME 和 CONSTRAINT_TYPE 列来识别外键名称。Or, query the Information Schema TABLE_CONSTRAINTS table and use the CONSTRAINT_NAME and CONSTRAINT_TYPE columns to identify the foreign key names.

您还可以在单条语句中删除外键及其关联的索引：You can also drop a foreign key and its associated index in a single statement:

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

更改 ROW_FORMATChanging the ROW_FORMAT

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

数据会被大量重组，使其成为一个开销较大的操作。Data is reorganized substantially, making it an expensive operation.

有关 ROW_FORMAT 选项的更多信息，请参见 表选项。For additional information about the ROW_FORMAT option, see Table Options.

更改 KEY_BLOCK_SIZEChanging the KEY_BLOCK_SIZE

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

数据会被大量重组，使其成为一个开销较大的操作。Data is reorganized substantially, making it an expensive operation.

有关 KEY_BLOCK_SIZE 选项的更多信息，请参见 表选项。For additional information about the KEY_BLOCK_SIZE option, see Table Options.

设置持久表统计信息选项Setting persistent table statistics options

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

仅修改表元数据。Only modifies table metadata.

持久统计信息包括 STATS_PERSISTENT、STATS_AUTO_RECALC 和 STATS_SAMPLE_PAGES。有关更多信息，请参见 第 17.8.10.1 节，“配置持久优化器统计参数”。Persistent statistics include STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES. For more information, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

指定字符集Specifying a character set

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

如果新的字符编码不同，则重建表。Rebuilds the table if the new character encoding is different.

转换字符集Converting a character set

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

如果新的字符编码不同，则重建表。Rebuilds the table if the new character encoding is different.

优化表Optimizing a table

OPTIMIZE TABLE tbl_name;

对于具有FULLTEXT索引的表，不支持就地操作。该操作使用INPLACE算法，但不允许使用ALGORITHM和LOCK语法。In-place operation is not supported for tables with FULLTEXT indexes. The operation uses the INPLACE algorithm, but ALGORITHM and LOCK syntax is not permitted.

使用FORCE选项重建表Rebuilding a table with the FORCE option

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

使用ALGORITHM=INPLACE自 MySQL 5.6.17 起. ALGORITHM=INPLACE不支持具有FULLTEXT索引的表。Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.

执行“空”重建Performing a "null" rebuild

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

从 MySQL 5.6.17 开始使用ALGORITHM=INPLACE。对于具有FULLTEXT索引的表，不支持ALGORITHM=INPLACE。Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.

重命名表Renaming a table

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

重命名表可以即时进行或就地进行。MySQL 会重命名与表 tbl_name 对应的文件，而无需制作副本。（你也可以使用 RENAME TABLE 语句来重命名表。参见 第 15.1.36 节，“RENAME TABLE 语句”。）专门授予重命名表的权限不会自动迁移到新名称。必须手动更改。Renaming a table can be performed instantly or in place. MySQL renames files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 15.1.36, “RENAME TABLE Statement”.) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

重命名普通表空间Renaming a general tablespace

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

ALTER TABLESPACE ... RENAME TO 使用 INPLACE 算法，但不支持 ALGORITHM 子句。ALTER TABLESPACE ... RENAME TO uses the INPLACE algorithm but does not support the ALGORITHM clause.

启用或禁用普通表空间加密Enabling or disabling general tablespace encryption

ALTER TABLESPACE tablespace_name ENCRYPTION='Y';

ALTER TABLESPACE ... ENCRYPTION 使用 INPLACE 算法，但不支持 ALGORITHM 子句。ALTER TABLESPACE ... ENCRYPTION uses the INPLACE algorithm but does not support the ALGORITHM clause.

有关相关信息，请参阅 第 17.13 节，“InnoDB 静态数据加密”。For related information, see Section 17.13, “InnoDB Data-at-Rest Encryption”.

启用或禁用每表文件表空间加密Enabling or disabling file-per-table tablespace encryption

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

有关相关信息，请参阅 第 17.13 节，“InnoDB 静态数据加密”。For related information, see Section 17.13, “InnoDB Data-at-Rest Encryption”.