13.1.9 ALTER TABLE Statement语句

13.1.9.1 ALTER TABLE Partition Operations分区操作
13.1.9.2 ALTER TABLE and Generated Columns和生成的列
13.1.9.3 ALTER TABLE Examples示例
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]
alter_option: {
table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
reference_definition
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP {CHECK | CONSTRAINT} symbol
  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
      | SET {VISIBLE | INVISIBLE}
      | DROP DEFAULT
    }
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE | ENABLE} KEYS
  | {DISCARD | IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | RENAME [TO | AS] new_tbl_name
  | {WITHOUT | WITH} VALIDATION
}
partition_options:
partition_option [partition_option] ...
partition_option: {
    ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
}
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
    USING {BTREE | HASH}
index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
}
table_options:
table_option [[,] table_option] ...
table_option: {
    AUTOEXTEND_SIZE [=] value
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
    (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. ALTER TABLE更改表的结构。For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. 例如，您可以添加或删除列、创建或销毁索引、更改现有列的类型或重命名列或表本身。You can also change characteristics such as the storage engine used for the table or the table comment.您还可以更改特性，例如用于表或表注释的存储引擎。

There are several additional aspects to the ALTER TABLE statement, described under the following topics in this section:ALTER TABLE语句还有几个附加方面，在本节的以下主题下进行了描述：

Table Options表格选项

table_options signifies table options of the kind that can be used in the CREATE TABLE statement, such as ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, MAX_ROWS, ROW_FORMAT, or TABLESPACE.table_options表示可以在CREATE TABLE语句中使用的表格选项，例如ENGINE、AUTO_INCREMENT、AVG_ROW_LENGTH、MAX_ROWS、ROW_FORMAT或TABLESPACE。

For descriptions of all table options, see Section 13.1.20, “CREATE TABLE Statement”. 有关所有表格选项的说明，请参阅第13.1.20节，“CREATE TABLE语句”。However, ALTER TABLE ignores DATA DIRECTORY and INDEX DIRECTORY when given as table options. 但是，当作为表选项提供时，ALTER TABLE会忽略DATA DIRECTORY和INDEX DIRECTORY。ALTER TABLE permits them only as partitioning options, and requires that you have the FILE privilege.ALTER TABLE只允许它们作为分区选项，并且要求您具有文件特权。

Use of table options with ALTER TABLE provides a convenient way of altering single table characteristics. 将表格选项与ALTER TABLE一起使用，可以方便地更改单个表格的特性。For example:例如：

To verify that the table options were changed as intended, use SHOW CREATE TABLE, or query the INFORMATION_SCHEMA.TABLES table.若要验证表选项是否已按预期更改，请使用SHOW CREATE TABLE或查询INFORMATION_SCHEMA.TABLES表。

Performance and Space Requirements性能和空间要求

ALTER TABLE operations are processed using one of the following algorithms:使用以下算法之一处理ALTER TABLE操作：

The ALGORITHM clause is optional. ALGORITHM子句是可选的。If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INSTANT for storage engines and ALTER TABLE clauses that support it. 如果省略了ALGORITHM子句，MySQL将使用ALGORITHM=INSTANT作为存储引擎和支持它的ALTER TABLE子句。Otherwise, ALGORITHM=INPLACE is used. 否则，将使用ALGORITHM=INPLACE。If ALGORITHM=INPLACE is not supported, ALGORITHM=COPY is used.如果不支持ALGORITHM=INPLACE，则使用ALGORITHM=COPY。

Specifying an ALGORITHM clause requires the operation to use the specified algorithm for clauses and storage engines that support it, or fail with an error otherwise. 指定ALGORITHM子句要求操作对支持它的子句和存储引擎使用指定的算法，否则操作将失败并出现错误。Specifying ALGORITHM=DEFAULT is the same as omitting the ALGORITHM clause.指定ALGORITHM=DEFAULT与省略ALGORITHM子句相同。

ALTER TABLE operations that use the COPY algorithm wait for other operations that are modifying the table to complete. 使用COPY算法的ALTER TABLE操作将等待修改表的其他操作完成。After alterations are applied to the table copy, data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. 对表副本应用更改后，将复制数据，删除原始表，并将表副本重命名为原始表的名称。While the ALTER TABLE operation executes, the original table is readable by other sessions (with the exception noted shortly). 在执行ALTER TABLE操作时，其他会话可以读取原始表（稍后会注意到异常）。Updates and writes to the table started after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table. 在ALTER TABLE操作开始后启动的对表的更新和写入被暂停，直到新表准备就绪，然后自动重定向到新表。The temporary copy of the table is created in the database directory of the original table unless it is a RENAME TO operation that moves the table to a database that resides in a different directory.表的临时副本在原始表的数据库目录中创建，除非是将表移动到位于其他目录中的数据库的RENAME TO操作。

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. 前面提到的例外情况是，ALTER TABLE在准备从表和表定义缓存中清除过时的表结构时会阻止读取（而不仅仅是写入）。At this point, it must acquire an exclusive lock. 此时，它必须获得独占锁。To do so, it waits for current readers to finish, and blocks new reads and writes.为此，它等待当前读卡器完成，并阻止新的读写操作。

An ALTER TABLE operation that uses the COPY algorithm prevents concurrent DML operations. 使用COPY算法的ALTER TABLE操作可防止并发DML操作。Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML). 仍然允许并发查询。也就是说，表复制操作始终至少包括LOCK=SHARED（允许查询，但不允许DML）的并发限制。You can further restrict concurrency for operations that support the LOCK clause by specifying LOCK=EXCLUSIVE, which prevents DML and queries. 通过指定LOCK=EXCLUSIVE，可以进一步限制支持LOCK子句的操作的并发性，这将阻止DML和查询。For more information, see Concurrency Control.有关更多信息，请参阅并发控制。

To force use of the COPY algorithm for an ALTER TABLE operation that would otherwise not use it, specify ALGORITHM=COPY or enable the old_alter_table system variable. 若要强制将COPY算法用于本来不会使用它的ALTER TABLE操作，请指定ALGORIGHT=COPY或启用系统变量old_alter_table。If there is a conflict between the old_alter_table setting and an ALGORITHM clause with a value other than DEFAULT, the ALGORITHM clause takes precedence.如果old_alter_table设置与具有非DEFAULT的ALGORITHM子句之间存在冲突，则ALGORITHM子句优先。

For InnoDB tables, an ALTER TABLE operation that uses the COPY algorithm on a table that resides in a shared tablespace can increase the amount of space used by the tablespace. 对于InnoDB表，对驻留在共享表空间中的表使用COPY算法的ALTER TABLE操作可以增加表空间使用的空间量。Such operations require as much additional space as the data in the table plus indexes. 这样的操作需要与表中的数据加索引一样多的额外空间。For a table residing in a shared tablespace, the additional space used during the operation is not released back to the operating system as it is for a table that resides in a file-per-table tablespace.对于驻留在共享表空间中的表，操作期间使用的额外空间不会释放回操作系统，就像对于驻留在每个表空间的文件中的表一样。

For information about space requirements for online DDL operations, see Section 15.12.3, “Online DDL Space Requirements”.有关联机DDL操作的空间要求的信息，请参阅第15.12.3节，“联机DDL空间要求”。

ALTER TABLE operations that support the INPLACE algorithm include:支持INPLACE（就地）算法的ALTER TABLE操作包括：

ALTER TABLE operations that support the INSTANT algorithm include:支持INSTANT（即时）算法的ALTER TABLE操作包括：

For more information about operations that support ALGORITHM=INSTANT, see Section 15.12.1, “Online DDL Operations”.有关支持ALGORITHM=INSTANT的操作的更多信息，请参阅第15.12.1节，“在线DDL操作”。

ALTER TABLE upgrades MySQL 5.5 temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. ALTER TABLE将MySQL 5.5临时列升级为5.6格式，用于ADD COLUMN、CHANGE COLUMN、MODIFY COLUMN、ADD INDEX和FORCE操作。This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. 无法使用INPLACE算法完成此转换，因为必须重新生成表，因此在这些情况下指定ALGORITHM=INPLACE会导致错误。Specify ALGORITHM=COPY if necessary.如有必要，指定ALGORITHM=COPY。

If an ALTER TABLE operation on a multicolumn index used to partition a table by KEY changes the order of the columns, it can only be performed using ALGORITHM=COPY.如果用于按KEY对表进行分区的多列索引上的ALTER TABLE操作更改了列的顺序，则只能使用ALGORITHM=COPY来执行该操作。

The WITHOUT VALIDATION and WITH VALIDATION clauses affect whether ALTER TABLE performs an in-place operation for virtual generated column modifications. WITH VALIDATION和WITH VALIDATION子句影响ALTER TABLE是否对虚拟生成的列修改执行就地操作。See Section 13.1.9.2, “ALTER TABLE and Generated Columns”.请参阅第13.1.9.2节，“更改表格和生成的列”。

NDB Cluster 8.0 supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. NDB Cluster 8.0支持使用与标准MySQL服务器相同的ALGORITHM=INPLACE语法进行在线操作。NDB does not support changing a tablespace online; beginning with NDB 8.0.21, it is disallowed. NDB不支持在线更改表空间；从NDB 8.0.21开始，它是不允许的。See Section 23.5.11, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.更多信息，请参阅第23.5.11节，“NDB集群中ALTER TABLE的在线操作”。

ALTER TABLE with DISCARD ... PARTITION ... TABLESPACE or IMPORT ... PARTITION ... TABLESPACE does not create any temporary tables or temporary partition files.ALTER TABLE与DISCARD ... PARTITION ... TABLESPACE或IMPORT ... PARTITION ... TABLESPACE不创建任何临时表或临时分区文件。

ALTER TABLE with ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, or REORGANIZE PARTITION does not create temporary tables (except when used with NDB tables); however, these operations can and do create temporary partition files.使用ADD PARTITION（添加分区）、DROP PARTITION（删除）、COALESCE PARTITION（合并分区）、REBUILD PARTITION（重建分区）或REORGANIZE PARTITION（重新组织分区）的ALTER TABLE不会创建临时表（与NDB表一起使用时除外）；但是，这些操作可以并且确实创建临时分区文件。

ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. RANGE分区或LIST分区的ADD或DROP操作是即时操作，或者几乎是即时操作。ADD or COALESCE operations for HASH or KEY partitions copy data between all partitions, unless LINEAR HASH or LINEAR KEY was used; this is effectively the same as creating a new table, although the ADD or COALESCE operation is performed partition by partition. 除非使用LINEAR HASH或LINEAR KEY，否则HASH或KEY分区的ADD（添加）或COALESCE合并操作会在所有分区之间复制数据；这实际上与创建新表相同，尽管添加或合并操作是逐分区执行的。REORGANIZE operations copy only changed partitions and do not touch unchanged ones.REORGANIZE操作只复制已更改的分区，不接触未更改的分区。

For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.对于MyISAM表，通过将系统变量myisam_sort_buffer_size设置为高值，可以加快索引的重新创建（更改过程中最慢的部分）。

Concurrency Control并发控制

For ALTER TABLE operations that support it, you can use the LOCK clause to control the level of concurrent reads and writes on a table while it is being altered. 对于支持它的ALTER TABLE操作，您可以使用LOCK子句在表被更改时控制表上并发读写的级别。Specifying a non-default value for this clause enables you to require a certain amount of concurrent access or exclusivity during the alter operation, and halts the operation if the requested degree of locking is not available.为此子句指定非默认值使您能够在alter操作期间要求一定量的并发访问或独占性，并在请求的锁定程度不可用时停止操作。

Only LOCK = DEFAULT is permitted for operations that use ALGORITHM=INSTANT. 对于使用ALGORITHM=INSTANT的操作，只允许LOCK = DEFAULT。The other LOCK clause parameters are not applicable.其他LOCK子句参数不适用。

The parameters for the LOCK clause are:LOCK子句的参数为：

Adding and Dropping Columns添加和删除列

Use ADD to add new columns to a table, and DROP to remove existing columns. 使用ADD向表中添加新列，使用DROP删除现有列。DROP col_name is a MySQL extension to standard SQL.DROP col_name是标准SQL的MySQL扩展。

To add a column at a specific position within a table row, use FIRST or AFTER col_name. 要在表格行中的特定位置添加列，请使用FIRST或AFTER col_name名称。The default is to add the column last.默认设置为在最后面添加列。

If a table contains only one column, the column cannot be dropped. 如果表仅包含一列，则无法删除该列。If what you intend is to remove the table, use the DROP TABLE statement instead.如果要删除该表，请改用DROP TABLE语句。

If columns are dropped from a table, the columns are also removed from any index of which they are a part. 如果从表中删除列，则这些列也将从它们所属的任何索引中删除。If all columns that make up an index are dropped, the index is dropped as well. 如果删除了构成索引的所有列，则该索引也将被删除。If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.如果使用CHANGE或MODIFY来缩短列上存在索引的列，并且得到的列长度小于索引长度，MySQL会自动缩短索引。

For ALTER TABLE ... ADD, if the column has an expression default value that uses a nondeterministic function, the statement may produce a warning or error. 对于ALTER TABLE ... ADD，如果列具有使用不确定函数的表达式默认值，则该语句可能会产生警告或错误。For further information, see Section 11.6, “Data Type Default Values”, and Section 17.1.3.7, “Restrictions on Replication with GTIDs”.有关更多信息，请参阅第11.6节，“数据类型默认值”和第17.1.3.7节，“使用GTID复制的限制”。

Renaming, Redefining, and Reordering Columns重命名、重新定义和重新排列列

The CHANGE, MODIFY, RENAME COLUMN, and ALTER clauses enable the names and definitions of existing columns to be altered. CHANGE、MODIFY、RENAME COLUMN和ALTER子句允许更改现有列的名称和定义。They have these comparative characteristics:它们具有以下比较特征：

CHANGE is a MySQL extension to standard SQL. CHANGE是对标准SQL的MySQL扩展。MODIFY and RENAME COLUMN are MySQL extensions for Oracle compatibility.MODIFY和RENAME COLUMN是用于Oracle兼容性的MySQL扩展。

To alter a column to change both its name and definition, use CHANGE, specifying the old and new names and the new definition. 要更改列以同时更改其名称和定义，请使用CHANGE，指定新旧名称和新定义。For example, to rename an INT NOT NULL column from a to b and change its definition to use the BIGINT data type while retaining the NOT NULL attribute, do this:例如，要将INT NOT NULL列从a重命名为b，并将其定义更改为使用BIGINT数据类型，同时保留NOT NULL属性，请执行以下操作：

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;

To change a column definition but not its name, use CHANGE or MODIFY. 要更改列定义但不更改其名称，请使用CHANGE或MODIFY。With CHANGE, the syntax requires two column names, so you must specify the same name twice to leave the name unchanged. 使用CHANGE时，语法需要两个列名，因此必须指定两次相同的名称才能保持名称不变。For example, to change the definition of column b, do this:例如，要更改列b的定义，请执行以下操作：

ALTER TABLE t1 CHANGE b b INT NOT NULL;

MODIFY is more convenient to change the definition without changing the name because it requires the column name only once:MODIFY在不更改名称的情况下更改定义更方便，因为它只需要一次列名：

ALTER TABLE t1 MODIFY b INT NOT NULL;

To change a column name but not its definition, use CHANGE or RENAME COLUMN. 若要更改列名但不更改其定义，请使用CHANGE或RENAME COLUMN。With CHANGE, the syntax requires a column definition, so to leave the definition unchanged, you must respecify the definition the column currently has. 使用CHANGE时，语法需要一个列定义，因此要保持该定义不变，必须重新指定该列当前的定义。For example, to rename an INT NOT NULL column from b to a, do this:例如，要将INT NOT NULL列从b重命名为a，请执行以下操作：

ALTER TABLE t1 CHANGE b a INT NOT NULL;

RENAME COLUMN is more convenient to change the name without changing the definition because it requires only the old and new names:RENAME COLUMN在不更改定义的情况下更改名称更方便，因为它只需要旧名称和新名称：

ALTER TABLE t1 RENAME COLUMN b TO a;

In general, you cannot rename a column to a name that already exists in the table. 通常，不能将列重命名为表中已存在的名称。However, this is sometimes not the case, such as when you swap names or move them through a cycle. 但是，有时情况并非如此，例如交换名称或在循环中移动名称时。If a table has columns named a, b, and c, these are valid operations:如果表包含名为a、b和c的列，则这些是有效的操作：

-- swap a and b
ALTER TABLE t1 RENAME COLUMN a TO b,
               RENAME COLUMN b TO a;
-- "rotate" a, b, c through a cycle
ALTER TABLE t1 RENAME COLUMN a TO b,
               RENAME COLUMN b TO c,
               RENAME COLUMN c TO a;

For column definition changes using CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. 对于使用CHANGE或MODIFY进行的列定义更改，定义必须包括数据类型和应应用于新列的所有属性，而不是索引属性（如主键或唯一）。Attributes present in the original definition but not specified for the new definition are not carried forward. 原始定义中存在但未为新定义指定的属性不会结转。Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows, intending to change only INT to BIGINT:假设列col1被定义为INT UNSIGNED DEFAULT 1 COMMENT 'my column'，您对该列进行如下修改，打算仅将INT更改为BIGINT：

ALTER TABLE t1 MODIFY col1 BIGINT;

That statement changes the data type from INT to BIGINT, but it also drops the UNSIGNED, DEFAULT, and COMMENT attributes. 该语句将数据类型从INT更改为BIGINT，但也会删除UNSIGNED、DEFAULT和COMMENT属性。To retain them, the statement must include them explicitly:要保留它们，语句必须明确包含它们：

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

For data type changes using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.对于使用CHANGE或MODIFY进行的数据类型更改，MySQL会尽可能地将现有列值转换为新类型。

Warning警告

This conversion may result in alteration of data. 此转换可能导致数据的更改。For example, if you shorten a string column, values may be truncated. 例如，如果缩短字符串列，值可能会被截断。To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.11, “Server SQL Modes”).如果转换为新数据类型会导致数据丢失，为了防止操作成功，请在使用ALTER TABLE之前启用严格的SQL模式（请参阅第5.1.11节，“服务器SQL模式”）。

If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.如果使用CHANGE或MODIFY来缩短列上存在索引的列，并且得到的列长度小于索引长度，MySQL会自动缩短索引。

For columns renamed by CHANGE or RENAME COLUMN, MySQL automatically renames these references to the renamed column:对于通过CHANGE或RENAME COLUMN重命名的列，MySQL会自动将这些引用重命名为重命名列：

For columns renamed by CHANGE or RENAME COLUMN, MySQL does not automatically rename these references to the renamed column:对于通过CHANGE或RENAME COLUMN重命名的列，MySQL不会自动将这些引用重命名为重命名列：

To reorder columns within a table, use FIRST and AFTER in CHANGE or MODIFY operations.若要对表中的列重新排序，请在CHANGE或MODIFY操作中使用FIRST和LAST。

ALTER ... SET DEFAULT or ALTER ... DROP DEFAULT specify a new default value for a column or remove the old default value, respectively. ALTER ... SET DEFAULT或ALTER ... DROP DEFAULT分别为列指定新的默认值或删除旧的默认值。If the old default is removed and the column can be NULL, the new default is NULL. 如果删除了旧的默认值，并且列可以为NULL，则新的默认值为NULL。If the column cannot be NULL, MySQL assigns a default value as described in Section 11.6, “Data Type Default Values”.如果列不能为NULL，MySQL将按照第11.6节，“数据类型默认值”中的说明分配一个默认值。

As of MySQL 8.0.23, ALTER ... SET VISIBLE and ALTER ... SET INVISIBLE enable column visibility to be changed. 从MySQL 8.0.23开始，ALTER ... SET VISIBLE和ALTER ... SET INVISIBLE使列可见性可以更改。See Section 13.1.20.10, “Invisible Columns”.请参阅第13.1.20.10节，“隐形列”。

Primary Keys and Indexes主键和索引

DROP PRIMARY KEY drops the primary key. DROP PRIMARY KEY删除主键。If there is no primary key, an error occurs. 如果没有主键，则会发生错误。For information about the performance characteristics of primary keys, especially for InnoDB tables, see Section 8.3.2, “Primary Key Optimization”.有关主键（尤其是InnoDB表）性能特征的信息，请参阅第8.3.2节，“主键优化”。

If the sql_require_primary_key system variable is enabled, attempting to drop a primary key produces an error.如果启用了系统变量sql_require_primary_key，则尝试删除主键会产生错误。

If you add a UNIQUE INDEX or PRIMARY KEY to a table, MySQL stores it before any nonunique index to permit detection of duplicate keys as early as possible.如果向表中添加UNIQUE INDEX或PRIMARY KEY，MySQL会将其存储在任何非唯一索引之前，以允许尽早检测重复键。

DROP INDEX removes an index. DROP INDEX删除索引。This is a MySQL extension to standard SQL. 这是标准SQL的MySQL扩展。See Section 13.1.27, “DROP INDEX Statement”. 请参阅第13.1.27节，“DROP INDEX语句”。To determine index names, use SHOW INDEX FROM tbl_name.要确定索引名称，请使用SHOW INDEX FROM tbl_name。

Some storage engines permit you to specify an index type when creating an index. 某些存储引擎允许您在创建索引时指定索引类型。The syntax for the index_type specifier is USING type_name. index_type说明符的语法是USING type_name。For details about USING, see Section 13.1.15, “CREATE INDEX Statement”. 有关USING的详细信息，请参阅第13.1.15节，“CREATE INDEX语句”。The preferred position is after the column list. 首选位置在列列表之后。Expect support for use of the option before the column list to be removed in a future MySQL release.在将来的MySQL版本中删除列列表之前，希望支持使用该选项。

index_option values specify additional options for an index. index_option值指定索引的其他选项。USING is one such option. USING是一种选择。For details about permissible index_option values, see Section 13.1.15, “CREATE INDEX Statement”.有关允许index_option值的详细信息，请参阅第13.1.15节，“CREATE INDEX语句”。

RENAME INDEX old_index_name TO new_index_name renames an index. This is a MySQL extension to standard SQL. 这是标准SQL的MySQL扩展。The content of the table remains unchanged. 该表的内容保持不变。old_index_name must be the name of an existing index in the table that is not dropped by the same ALTER TABLE statement. old_index_name必须是表中未被同一ALTER TABLE语句删除的现有索引的名称。new_index_name is the new index name, which cannot duplicate the name of an index in the resulting table after changes have been applied. new_index_name是新的索引名称，在应用更改后，它不能与结果表中的索引名称重复。Neither index name can be PRIMARY.两个索引名都不能是PRIMARY。

If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). 如果在MyISAM表上使用ALTER TABLE，则所有非唯一索引都将在单独的批处理中创建（与REPAIR TABLE一样）。This should make ALTER TABLE much faster when you have many indexes.当您有许多索引时，这将使ALTER TABLE更快。

For MyISAM tables, key updating can be controlled explicitly. 对于MyISAM表，可以显式控制密钥更新。Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. 使用ALTER TABLE ... DISABLE KEYS以通知MySQL停止更新非唯一索引。Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. 然后使用ALTER TABLE ... ENABLE KEYS以重新创建缺少的索引。MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. MyISAM使用一种比逐个插入密钥快得多的特殊算法来实现这一点，因此在执行批量插入操作之前禁用密钥应该会带来相当大的加速。Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.使用ALTER TABLE ... DISABLE KEYS除了前面提到的权限外，还需要INDEX权限。

While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.虽然禁用了非唯一索引，但对于SELECT和EXPLAIN等语句，它们将被忽略，否则将使用它们。

After an ALTER TABLE statement, it may be necessary to run ANALYZE TABLE to update index cardinality information. 在ALTER TABLE语句之后，可能需要运行ANALYZE TABLE来更新索引基数信息。See Section 13.7.7.22, “SHOW INDEX Statement”.请参阅第13.7.7.22节，“SHOW INDEX语句”。

The ALTER INDEX operation permits an index to be made visible or invisible. ALTER INDEX操作允许使索引可见或不可见。An invisible index is not used by the optimizer. 优化器不使用不可见索引。Modification of index visibility applies to indexes other than primary keys (either explicit or implicit). 索引可见性的修改适用于主键以外的索引（显式或隐式）。This feature is storage engine neutral (supported for any engine). 此功能为存储引擎空档（支持任何引擎）。For more information, see Section 8.3.12, “Invisible Indexes”.有关更多信息，请参阅第8.3.12节，“不可见索引”。

Foreign Keys and Other Constraints外键和其他约束

The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB and NDB storage engines, which implement ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...). InnoDB和NDB存储引擎支持FOREIGN KEY（外键）子句和REFERENCES子句，它们实现了ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...)。See Section 13.1.20.5, “FOREIGN KEY Constraints”. 请参阅第13.1.20.5节，“外键约束”。For other storage engines, the clauses are parsed but ignored.对于其他存储引擎，这些子句将被解析但忽略。

For ALTER TABLE, unlike CREATE TABLE, ADD FOREIGN KEY ignores index_name if given and uses an automatically generated foreign key name. 对于ALTER TABLE，与CREATE TABLE不同，ADD FOREIGN KEY会忽略给定的index_name，并使用自动生成的外键名称。As a workaround, include the CONSTRAINT clause to specify the foreign key name:作为一种解决方法，包括CONSTRAINT子句以指定外键名称：

ADD CONSTRAINT name FOREIGN KEY (....) ...
Important重要

MySQL silently ignores inline REFERENCES specifications, where the references are defined as part of the column specification. MySQL默认忽略内联REFERENCES规范，其中引用被定义为列规范的一部分。MySQL accepts only REFERENCES clauses defined as part of a separate FOREIGN KEY specification.MySQL只接受定义为分开的FOREIGN KEY规范文档一部分的REFERENCES子句。

Note注意

Partitioned InnoDB tables do not support foreign keys. 分区的InnoDB表不支持外键。This restriction does not apply to NDB tables, including those explicitly partitioned by [LINEAR] KEY. 此限制不适用于NDB表，包括那些由[LINEAR] KEY显式分区的表。For more information, see Section 24.6.2, “Partitioning Limitations Relating to Storage Engines”.有关更多信息，请参阅第24.6.2节，“与存储引擎相关的分区限制”。

MySQL Server and NDB Cluster both support the use of ALTER TABLE to drop foreign keys:MySQL服务器和NDB集群都支持使用ALTER TABLE删除外键：

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE but not for ALTER TABLE ... ALGORITHM=COPY.ALTER TABLE ... ALGORITHM=INPLACE支持在同一ALTER TABLE语句中添加和删除外键，但ALTER TABLE ... ALGORITHM=COPY不支持这么做。

The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. 服务器禁止更改可能导致引用完整性丢失的外键列。A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward. 解决方法是在更改列定义之前使用ALTER TABLE ... DROP FOREIGN KEY，然后使用ALTER TABLE ... ADD FOREIGN KEY。Examples of prohibited changes include:禁止更改的示例包括：

ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string “tbl_name_ibfk_” to reflect the new table name. ALTER TABLE tbl_name RENAME new_tbl_name更改内部生成的外键约束名称和用户定义的外键约束名称（以字符串“tbl_name_ibfk_”开头），以反映新表名称。InnoDB interprets foreign key constraint names that begin with the string “tbl_name_ibfk_” as internally generated names.InnoDB将以字符串“tbl_name_ibfk_”开头的外键约束名称解释为内部生成的名称。

Prior to MySQL 8.0.16, ALTER TABLE permits only the following limited version of CHECK constraint-adding syntax, which is parsed and ignored:在MySQL 8.0.16之前，ALTER TABLE只允许以下有限版本的CHECK约束添加语法，该语法被解析并忽略：

ADD CHECK (expr)

As of MySQL 8.0.16, ALTER TABLE permits CHECK constraints for existing tables to be added, dropped, or altered:从MySQL 8.0.16开始，ALTER TABLE允许添加、删除或更改现有表的CHECK约束：

The DROP CHECK and ALTER CHECK clauses are MySQL extensions to standard SQL.DROP CHECK和ALTER CHECK子句是标准SQL的MySQL扩展。

As of MySQL 8.0.19, ALTER TABLE permits more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint name:从MySQL 8.0.19开始，ALTER TABLE允许使用更通用（和SQL标准）的语法删除和更改任何类型的现有约束，其中约束类型由约束名称确定：

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. SQL标准指定所有类型的约束（主键、唯一索引、外键、检查）都属于同一名称空间。In MySQL, each constraint type has its own namespace per schema. 在MySQL中，每个约束类型在每个模式中都有自己的名称空间。Consequently, names for each type of constraint must be unique per schema, but constraints of different types can have the same name. 因此，每种类型的约束的名称在每个模式中都必须是唯一的，但不同类型的约束可以具有相同的名称。When multiple constraints have the same name, DROP CONSTRAINT and ADD CONSTRAINT are ambiguous and an error occurs. 当多个约束具有相同名称时，DROP CONSTRAINT和ADD CONSTRAINT将不明确，并发生错误。In such cases, constraint-specific syntax must be used to modify the constraint. 在这种情况下，必须使用特定于约束的语法来修改约束。For example, use DROP PRIMARY KEY or DROP FOREIGN KEY to drop a primary key or foreign key.例如，使用DROP PRIMARY KEY或DROP FOREIGN KEY来删除主键或外键。

If a table alteration causes a violation of an enforced CHECK constraint, an error occurs and the table is not modified. 如果表更改导致违反强制CHECK约束，则会发生错误，并且不会修改该表。Examples of operations for which an error occurs:发生错误的操作示例：

ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated and user-defined CHECK constraint names that begin with the string “tbl_name_chk_” to reflect the new table name. ALTER TABLE tbl_name RENAME new_tbl_name更改内部生成的CHECK约束名称和用户定义的CHECK约束名称（以字符串“tbl_name_chk_”开头），以反映新表名称。MySQL interprets CHECK constraint names that begin with the string “tbl_name_chk_” as internally generated names.MySQL将以字符串“tbl_name_chk_”开头的检查约束名称解释为内部生成的名称。

Changing the Character Set更改字符集

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:要将表格默认字符集和所有字符列（CHAR、VARCHAR、TEXT）更改为新的字符集，请使用如下语句：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

The statement also changes the collation of all character columns. 该语句还更改所有字符列的排序规则。If you specify no COLLATE clause to indicate which collation to use, the statement uses default collation for the character set. 如果未指定COLLATE子句来指示要使用的排序规则，则语句将使用字符集的默认排序规则。If this collation is inappropriate for the intended table use (for example, if it would change from a case-sensitive collation to a case-insensitive collation), specify a collation explicitly.如果此排序规则不适合预期的表用途（例如，如果它将从区分大小写的排序规则更改为不区分大小写的排序规则），请显式指定排序规则。

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET changes the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. 对于数据类型为VARCHAR或其中一种TEXT类型的列，CONVERT TO CHARACTER SET会根据需要更改数据类型，以确保新列的长度足以存储与原始列相同数量的字符。For example, a TEXT column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. 例如，TEXT列有两个长度字节，用于存储列中值的字节长度，上限为65535。For a latin1 TEXT column, each character requires a single byte, so the column can store up to 65,535 characters. 对于latin1文本列，每个字符需要一个字节，因此该列最多可以存储65535个字符。If the column is converted to utf8, each character might require up to three bytes, for a maximum possible length of 3 × 65,535 = 196,605 bytes. 如果将列转换为utf8，则每个字符可能需要最多三个字节，最大可能长度为365535=196605字节。That length does not fit in a TEXT column's length bytes, so MySQL converts the data type to MEDIUMTEXT, which is the smallest string type for which the length bytes can record a value of 196,605. 该长度不适合TEXT列的长度字节，因此MySQL将数据类型转换为MEDIUMTEXT，这是长度字节可以记录值为196,605的最小字符串类型。Similarly, a VARCHAR column might be converted to MEDIUMTEXT.类似地，VARCHAR列可能会转换为MEDIUMTEXT。

To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET. 若要避免对刚才描述的类型进行数据类型更改，请不要使用CONVERT TO CHARACTER SET。Instead, use MODIFY to change individual columns. 相反，请使用MODIFY来更改各个列。For example:例如：

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;

If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). 如果指定CONVERT TO CHARACTER SET binary，CHAR、VARCHAR和TEXT列将转换为相应的二进制字符串类型（BINARY、VARBINARY、BLOB）。This means that the columns no longer have a character set and a subsequent CONVERT TO operation does not apply to them.这意味着这些列不再具有字符集，后续的CONVERT TO操作将不适用于它们。

If charset_name is DEFAULT in a CONVERT TO CHARACTER SET operation, the character set named by the character_set_database system variable is used.如果在CONVERT TO CHARACTER SET操作中charset_name是DEFAULT，则使用系统变量character_set_database命名的字符集。

Warning警告

The CONVERT TO operation converts column values between the original and named character sets. CONVERT TO操作在原始字符集和命名字符集之间转换列值。This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). 如果在一个字符集中有一列（如latin1），但存储的值实际上使用了其他一些不兼容的字符集（如utf8），则这不是您想要的。In this case, you have to do the following for each such column:在这种情况下，您必须对每个此类列执行以下操作：

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.这样做的原因是，当您转换到BLOB列或从BLOB列转换时，没有转换。

To change only the default character set for a table, use this statement:要仅更改表的默认字符集，请使用以下语句：

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

The word DEFAULT is optional. 单词DEFAULT是可选的。The default character set is the character set that is used if you do not specify the character set for columns that you add to a table later (for example, with ALTER TABLE ... ADD column).默认字符集是在不为以后添加到表中的列指定字符集时使用的字符集（例如，使用ALTER TABLE ... ADD column）。

When the foreign_key_checks system variable is enabled, which is the default setting, character set conversion is not permitted on tables that include a character string column used in a foreign key constraint. 启用系统变量foreign_key_checks（默认设置）时，不允许在包含外键约束中使用的字符串列的表上进行字符集转换。The workaround is to disable foreign_key_checks before performing the character set conversion. 解决方法是在执行字符集转换之前禁用foreign_key_checks。You must perform the conversion on both tables involved in the foreign key constraint before re-enabling foreign_key_checks. 在重新启用foreign_key_checks之前，必须对外键约束中涉及的两个表执行转换。If you re-enable foreign_key_checks after converting only one of the tables, an ON DELETE CASCADE or ON UPDATE CASCADE operation could corrupt data in the referencing table due to implicit conversion that occurs during these operations (Bug #45290, Bug #74816).如果在仅转换其中一个表后重新启用foreign_key_checks，则ON DELETE CASCADE或ON UPDATE CASCADE操作可能会由于这些操作期间发生的隐式转换而损坏引用表中的数据（Bug#45290，Bug#74816）。

Importing InnoDB Tables导入InnoDB表

An InnoDB table created in its own file-per-table tablespace can be imported from a backup or from another MySQL server instance using DISCARD TABLEPACE and IMPORT TABLESPACE clauses. 可以使用DISCARD TABLEPACE子句和IMPORT TABLESPACE子句从备份或其他MySQL服务器实例导入在每个表空间的自己的文件中创建的InnoDB表。See Section 15.6.1.3, “Importing InnoDB Tables”.请参阅第15.6.1.3节，“导入InnoDB表”。

Row Order for MyISAM TablesMyISAM表的行顺序

ORDER BY enables you to create the new table with the rows in a specific order. ORDER BY使您能够以特定顺序创建包含行的新表。This option is useful primarily when you know that you query the rows in a certain order most of the time. 当您知道大部分时间都是按特定顺序查询行时，此选项非常有用。By using this option after major changes to the table, you might be able to get higher performance. 通过在对表进行重大更改后使用此选项，您可能能够获得更高的性能。In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.在某些情况下，如果表是按您以后要排序的列排序的，那么MySQL的排序可能会更容易。

Note注意

The table does not remain in the specified order after inserts and deletes.插入和删除后，表不会保持指定的顺序。

ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. ORDER BY语法允许为排序指定一个或多个列名，每个列名后面可以可选地后跟ASC或DESC，分别表示升序或降序排序顺序。The default is ascending order. 默认值为升序。Only column names are permitted as sort criteria; arbitrary expressions are not permitted. 仅允许列名作为排序标准；不允许使用任意表达式。This clause should be given last after any other clauses.本子句应排在任何其他子句之后。

ORDER BY does not make sense for InnoDB tables because InnoDB always orders table rows according to the clustered index.ORDER BY对于InnoDB表没有意义，因为InnoDB总是根据聚集索引对表行进行排序。

When used on a partitioned table, ALTER TABLE ... ORDER BY orders rows within each partition only.在分区表上使用时，ALTER TABLE ... ORDER BY仅在每个分区内按顺序排序行。

Partitioning Options分区选项

partition_options signifies options that can be used with partitioned tables for repartitioning, to add, drop, discard, import, merge, and split partitions, and to perform partitioning maintenance.partition_options表示可与分区表一起用于重新分区、添加、删除、放弃、导入、合并和拆分分区以及执行分区维护的选项。

It is possible for an ALTER TABLE statement to contain a PARTITION BY or REMOVE PARTITIONING clause in an addition to other alter specifications, but the PARTITION BY or REMOVE PARTITIONING clause must be specified last after any other specifications. ALTER TABLE语句可以在其他ALTER规范之外包含PARTITION BY或>REMOVE PARTITIONING子句，但PARTITION BY或>REMOVE PARTITIONING子句必须在任何其他规范之后最后指定。The ADD PARTITION, DROP PARTITION, DISCARD PARTITION, IMPORT PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, EXCHANGE PARTITION, ANALYZE PARTITION, CHECK PARTITION, and REPAIR PARTITION options cannot be combined with other alter specifications in a single ALTER TABLE, since the options just listed act on individual partitions.ADD PARTITION、DROP PARTITION、DISCARD PARTITION、IMPORT PARTITION、COALESCE PARTITION、REORGANIZE PARTITION、EXCHANGE PARTITION、ANALYZE PARTITION、CHECK PARTITION和REPAIR PARTITION选项不能与单个ALTER TABLE中的其他alter规范组合，因为刚才列出的选项作用于各个分区。

For more information about partition options, see Section 13.1.20, “CREATE TABLE Statement”, and Section 13.1.9.1, “ALTER TABLE Partition Operations”. 有关分区选项的更多信息，请参阅第13.1.20节，“CREATE TABLE语句”和第13.1.9.1节，“ALTER TABLE分区操作”。For information about and examples of ALTER TABLE ... EXCHANGE PARTITION statements, see Section 24.3.3, “Exchanging Partitions and Subpartitions with Tables”.有关ALTER TABLE ... EXCHANGE PARTITION语句的信息和示例，请参阅第24.3.3节，“用表交换分区和子分区”。

13.1.9.1 ALTER TABLE Partition Operations
13.1.9.2 ALTER TABLE and Generated Columns
13.1.9.3 ALTER TABLE Examples