13.1.8 ALTER TABLE 语法

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    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 CHECK (expr)
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | 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 {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
  | UPGRADE PARTITIONING

key_part:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

table_options:
    table_option [[,] table_option] ...

table_option:
    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
  | 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}
  | 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更改 table 的结构。对于 example,您可以添加或删除列,创建或销毁索引,更改现有列的类型,或重命名列或 table 本身。您还可以更改特性,例如用于 table 或 table comment 的存储引擎。

  • 要使用ALTER TABLE,table 需要改变创建插入权限。重命名 table 需要在新 table 上的旧 table,改变创建插入上使用改变下降

  • 在 table name 之后,指定要进行的更改。如果给出 none,ALTER TABLE什么也不做。

  • 许多允许的更改的语法类似于创建 TABLE语句的子句。 column_definition子句对ADDCHANGE使用与创建 TABLE相同的语法。有关更多信息,请参阅第 13.1.18 节,“CREATE TABLE 语法”

  • 单词COLUMN是可选的,可以省略。

  • 单个ALTER TABLE语句中允许多个ADDALTERDROPCHANGE子句,以逗号分隔。这是标准 SQL 的 MySQL 扩展,每个ALTER TABLE语句只允许每个子句中的一个。对于 example,要在单个语句中删除多个列,请执行以下操作:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

ALTER TABLE语句还有其他几个方面,本节中的以下主题对此进行了描述:

Table 选项

table_options表示可在创建 TABLE语句中使用的 table 选项,例如ENGINEAUTO_INCREMENTAVG_ROW_LENGTHMAX_ROWSROW_FORMATTABLESPACE

有关所有 table 选项的说明,请参阅第 13.1.18 节,“CREATE TABLE 语法”。但是,ALTER TABLE在作为 table 选项给出时会忽略DATA DIRECTORYINDEX DIRECTORYALTER TABLE仅允许它们作为分区选项,并且,从 MySQL 5.7.17 开始,要求您具有FILE特权。

将 table 选项与ALTER TABLE一起使用可以方便地改变单个 table 特性。例如:

  • 如果t1当前不是InnoDB table,则此语句将其存储引擎更改为InnoDB
ALTER TABLE t1 ENGINE = InnoDB;
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
  • 要为 file-per-table 表空间中的InnoDB table 启用或禁用加密:
ALTER TABLE t1 ENCRYPTION='Y';
ALTER TABLE t1 ENCRYPTION='N';

必须安装密钥环插件并将其配置为使用ENCRYPTION选项。有关更多信息,请参阅第 14.6.3.8 节,“InnoDB Data-at-Rest 加密”

  • 要重置当前 auto-increment value:
ALTER TABLE t1 AUTO_INCREMENT = 13;

您无法将计数器重置为小于或等于当前正在使用的 value 的 value。对于InnoDBMyISAM,如果 value 小于或等于当前AUTO_INCREMENT列中的最大 value,则 value 将重置为当前最大AUTO_INCREMENT列 value 加 1。

  • 要更改默认的 table 字符集:
ALTER TABLE t1 CHARACTER SET = utf8;

另见更改字符集

  • 添加(或更改)table comment:
ALTER TABLE t1 COMMENT = 'New table comment';
  • 使用ALTER TABLETABLESPACE选项在现有的一般表空间file-per-table表空间和系统表空间之间移动InnoDB表。见使用 ALTER TABLE 在表空间之间移动表

  • ALTER TABLE ... TABLESPACE操作总是会导致完整的 table 重建,即使TABLESPACE属性没有从之前的 value 更改。

  • ALTER TABLE ... TABLESPACE语法不支持将 table 从临时表空间移动到持久表空间。

  • ALTER TABLE ... TABLESPACE支持的DATA DIRECTORY子句不受ALTER TABLE ... TABLESPACE支持,如果指定则会被忽略。

  • 有关TABLESPACE选项的功能和限制的更多信息,请参阅创建 TABLE

  • MySQL NDB Cluster 7.5.2 和更高版本支持设置NDB_TABLE选项以控制 table 的分区平衡(片段计数类型),read-from-any-replica 功能,完全复制或这些的任意组合,作为ALTER TABLE语句的 table comment 的一部分,其方式与创建 TABLE,如此 example 所示:

ALTER TABLE t1 COMMENT = "NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";

请记住ALTER TABLE ... COMMENT ...丢弃 table 的任何现有 comment。有关其他信息和示例,请参阅设置 NDBTABLE 选项

要验证 table 选项是否按预期更改,请使用显示创建 TABLE,或查询INFORMATIONSCHEMA.TABLES table。

性能和空间要求

使用以下算法之一处理ALTER TABLE操作:

  • COPY:对原始 table 的副本执行操作,table 数据逐行从原始 table 复制到新 table。不允许并发 DML。

  • INPLACE:操作避免复制 table 数据,但可以重建 table。在操作的准备和执行阶段,可以简要地对 table 进行独占元数据锁定。通常,支持并发 DML。

ALGORITHM子句是可选的。如果省略ALGORITHM子句,MySQL 将ALGORITHM=INPLACE用于存储引擎,ALTER TABLE子句支持它。否则,使用ALGORITHM=COPY

指定ALGORITHM子句需要操作将指定的算法用于支持它的子句和存储引擎,否则将失败并返回错误。指定ALGORITHM=DEFAULT与省略ALGORITHM子句相同。

ALTER TABLE使用COPY算法的操作等待修改 table 的其他操作完成。对 table 副本应用更改后,将复制数据,删除原始 table,并将 table 副本重命名为原始 table 的 name。执行ALTER TABLE操作时,原始 table 可由其他会话读取(很快就会注明 exception)。在操作开始后启动的对 table 的更新和写入将停止,直到新的 table 准备就绪,然后自动重定向到新的 table。 table 的临时副本在原始 table 的数据库目录中创建,除非它是操作,将 table 移动到驻留在不同目录中的数据库。

前面提到的 exception 是ALTER TABLE块在准备安装 table .frm文件的新 version,丢弃旧文件,并从 table 和 table 定义缓存中清除过时的 table 结构时读取(不仅仅是写入) 。此时,它必须获得独占锁。为此,它等待当前的 readers 完成,并阻止新的读写操作。

使用COPY算法的ALTER TABLE操作可防止并发 DML 操作。仍然允许并发查询。也就是说,table-copying 操作总是至少包含LOCK=SHARED的并发限制(允许查询但不包括 DML)。您可以通过指定LOCK=EXCLUSIVE来进一步限制支持LOCK子句的操作的并发性,从而阻止 DML 和查询。有关更多信息,请参阅并发控制

要强制将COPY算法用于否则不会使用它的ALTER TABLE操作,请启用old_altertable系统变量或指定ALGORITHM=COPY。如果old_alter_table设置与具有DEFAULT以外的_val的ALGORITHM子句之间存在冲突,则ALGORITHM子句优先。

对于InnoDB表,在位于共享表空间的 table 上使用COPY算法的ALTER TABLE操作可以增加表空间使用的空间量。此类操作需要与 table plus 索引中的数据一样多的额外空间。对于驻留在共享表空间中的 table,操作期间使用的额外空间不会释放回操作系统,因为它是驻留在file-per-table表空间中的 table。

有关联机 DDL 操作的空间要求的信息,请参阅第 14.13.3 节,“在线 DDL 空间要求”

ALTER TABLE使用INPLACE算法的操作包括:

  • InnoDB 在线 DDL feature 支持ALTER TABLE操作。见第 14.13.1 节,“在线 DDL 操作”

  • 重命名 table。 MySQL 重命名与 table tbl_name对应的 files 而不进行复制。 (您也可以使用重命名 TABLE语句重命名表。请参阅第 13.1.33 节,“RENAME TABLE 语法” .)专门为重命名的 table 授予的权限不会迁移到新的 name。必须手动更改它们。

  • 仅修改 table 元数据的操作。这些操作是立即的,因为服务器只改变 table .frm文件,而不是触及 table 内容。 Metadata-only 操作包括:

  • 重命名列。

  • 更改列的默认 value(导航台表除外)。

  • 通过将新枚举或集成员添加到有效成员值列表的末尾来修改ENUM列的定义,因为数据类型的存储大小不会更改 long。对于 example,将成员添加到具有 8 个成员的列会将每个 value 所需的存储空间从 1 个字节更改为 2 个字节;这需要 table 副本。在列表中间添加成员会导致重新编号现有成员,这需要 table 副本。

  • 重命名索引。

  • InnoDB导航台表添加或删除辅助索引。见InnoDB 快速索引创建

  • 对于导航台表,在 variable-width 列上添加和删除索引的操作。这些操作在线进行,没有 table 复制,并且在大多数持续时间内不会阻止并发 DML 操作。见第 21.5.14 节,“在 NDB Cluster 中使用 ALTER TABLE 进行在线操作”

ALTER TABLE将 MySQL 5.5 时间列升级为 5.6 格式,用于ADD COLUMNCHANGE COLUMNMODIFY COLUMNADD INDEXFORCE操作。使用INPLACE算法无法完成此转换,因为必须重建 table,因此在这些情况下指定ALGORITHM=INPLACE会导致错误。如有必要,请指定ALGORITHM=COPY

如果用于按KEY分区 table 的多列索引上的ALTER TABLE操作更改了列的 order,则只能使用ALGORITHM=COPY执行。

WITHOUT VALIDATIONWITH VALIDATION子句影响ALTER TABLE是否对虚拟生成列修改执行 in-place 操作。见第 13.1.8.2 节,“ALTER TABLE 和生成的列”

NDB Cluster 以前使用ONLINEOFFLINE关键字支持在线ALTER TABLE操作。不再支持这些关键字;它们的使用会导致语法错误。 MySQL NDB Cluster 7.5(及更高版本)支持使用与标准 MySQL 服务器相同的ALGORITHM=INPLACE语法进行在线操作。有关详细信息,请参阅第 21.5.14 节,“在 NDB Cluster 中使用 ALTER TABLE 进行在线操作”

带有DISCARD ... PARTITION ... TABLESPACEIMPORT ... PARTITION ... TABLESPACEALTER TABLE不会创建任何临时表或临时分区 files。

ALTER TABLEADD PARTITIONDROP PARTITIONCOALESCE PARTITIONREBUILD PARTITIONREORGANIZE PARTITION不会创建临时表(与导航台表一起使用时除外);但是,这些操作可以创建临时分区 files。

RANGELIST分区的ADDDROP操作是立即操作或几乎是如此。 HASHKEY分区的ADDCOALESCE操作在所有分区之间复制数据,除非使用LINEAR HASHLINEAR KEY;尽管ADDCOALESCE操作是按分区执行的,但这实际上与创建新的 table 相同。 REORGANIZE操作仅复制更改的分区,不触及未更改的分区。

对于MyISAM表,可以通过将myisam_sort_buffer_size系统变量设置为高值来加速索引 re-creation(更改 process 的最慢部分)。

并发控制

对于支持它的ALTER TABLE操作,您可以使用LOCK子句来控制 table 在更改时的并发读取和写入的 level。为此子句指定 non-default value 使您可以在 alter 操作期间要求一定量的并发访问或排他性,并在请求的锁定程度不可用时停止操作。 LOCK子句的参数是:


LOCK =默认


 Maximum level of concurrency for the given  `ALGORITHM`  clause (if any) and  `ALTER TABLE`  operation: Permit concurrent reads and writes if supported. If not, permit concurrent reads if supported. If not, enforce exclusive access.

- ```java
LOCK = NONE

如果支持,则允许并发读写。否则,会发生错误。


LOCK =共享


 If supported, permit concurrent reads but block writes. Writes are blocked even if concurrent writes are supported by the storage engine for the given  `ALGORITHM`  clause (if any) and  `ALTER TABLE`  operation. If concurrent reads are not supported, an error occurs.

- ```java
LOCK = EXCLUSIVE

实施独家访问。即使存储引擎为给定的ALGORITHM子句(如果有)和ALTER TABLE操作支持并发 reads/writes,也会执行此操作。

添加和删除列

使用ADD将新列添加到 table,使用DROP删除现有列。 DROP col_name是标准 SQL 的 MySQL 扩展。

要在 table 行中的特定位置添加列,请使用FIRSTAFTER col_name。默认是最后添加列。

如果 table 只包含一列,则无法删除该列。如果您打算删除 table,请改用DROP TABLE语句。

如果从 table 中删除列,则列也将从它们所属的任何索引中删除。如果删除构成索引的所有列,则也会删除索引。

重命名,重新定义和重新排序列

CHANGEMODIFYALTER子句允许更改现有列的名称和定义。它们具有以下比较特征:

  • CHANGE :

  • 可以重命名列并更改其定义,或两者。

  • 具有比MODIFY更多的能力,但是以牺牲一些操作的便利性为代价。如果不重命名,CHANGE需要将列命名两次。

  • 使用FIRSTAFTER,可以重新排序列。

  • MODIFY :

  • 可以更改列定义但不能更改其 name。

  • CHANGE更方便更改列定义而不重命名。

  • 使用FIRSTAFTER,可以重新排序列。

  • ALTER:仅用于更改列默认值 value。

CHANGE是标准 SQL 的 MySQL 扩展。 MODIFY是 Oracle 兼容性的 MySQL 扩展。

要更改列以更改其 name 和定义,请使用CHANGE,指定旧名称和新名称以及新定义。对于 example,要将INT NOT NULL列从a重命名为b并将其定义更改为使用BIGINT数据类型,同时保留NOT NULL属性,请执行以下操作:

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;

要更改列定义但不更改其 name,请使用CHANGEMODIFY。使用CHANGE时,语法需要两个列名,因此必须指定两次相同的 name 以保持 name 不变。对于 example,要更改列b的定义,请执行以下操作:

ALTER TABLE t1 CHANGE b b INT NOT NULL;

更改定义更方便MODIFY而不更改 name,因为它只需要列 name 一次:

ALTER TABLE t1 MODIFY b INT NOT NULL;

要更改列 name 但不更改其定义,请使用CHANGE。语法需要列定义,因此要保持定义不变,必须重新指定列当前具有的定义。对于 example,要将INT NOT NULL列从b重命名为a,请执行以下操作:

ALTER TABLE t1 CHANGE b a INT NOT NULL;

对于使用CHANGEMODIFY的列定义更改,定义必须包括应用于新列的数据类型和所有属性,而不是索引属性(如PRIMARY KEYUNIQUE)。原始定义中存在但未为新定义指定的属性不会继续使用。假设列col1被定义为INT UNSIGNED DEFAULT 1 COMMENT 'my column'并且您按如下方式修改列,打算仅将INT更改为BIGINT

ALTER TABLE t1 MODIFY col1 BIGINT;

该语句将数据类型从INT更改为BIGINT,但它也会删除UNSIGNEDDEFAULTCOMMENT属性。为了保留它们,声明必须明确包含它们:

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

对于使用CHANGEMODIFY的数据类型更改,MySQL 尝试尽可能将现有列值转换为新类型。

警告
此转换可能导致数据更改。例如,如果缩短 string 列,则可能会截断值。要防止操作成功,如果转换为新数据类型会导致数据丢失,请在使用ALTER TABLE之前启用严格 SQL 模式(请参阅第 5.1.10 节,“服务器 SQL 模式”)。
如果使用CHANGEMODIFY缩短列上存在索引的列,并且生成的列长度小于索引长度,则 MySQL 会自动缩短索引。

对于CHANGE重命名的列,MySQL 会自动将这些 references 重命名为重命名的列:

  • 引用旧列的索引,包括索引和禁用的MyISAM索引。

  • 引用旧列的外键。

对于CHANGE重命名的列,MySQL 不会自动将这些 references 重命名为重命名的列:

  • 生成的列和分区表达式,用于引用重命名的列。必须使用CHANGE在与重命名列的语句相同的ALTER TABLE语句中重新定义此类表达式。

  • 引用重命名列的视图和存储程序。您必须手动更改这些 objects 的定义以引用新列 name。

要重新排序 table 中的列,请在CHANGEMODIFY操作中使用FIRSTAFTER

ALTER ... SET DEFAULTALTER ... DROP DEFAULT为列指定新的默认 value 或分别删除旧的默认 value。如果删除旧的默认值并且列可以是NULL,则新的默认值为NULL。如果列不能NULL,则 MySQL 会按照第 11.7 节,“数据类型默认值”中的描述分配默认的 value。

主键和索引

DROP PRIMARY KEY放下首要的关键。如果没有主 key,则会发生错误。有关主键的 performance 特性的信息,尤其是InnoDB表,请参阅第 8.3.2 节,“主 Key 优化”

如果将UNIQUE INDEXPRIMARY KEY添加到 table,MySQL 会在任何非唯一索引之前存储它,以允许尽早检测重复键。

DROP INDEX删除索引。这是标准 SQL 的 MySQL 扩展。见第 13.1.25 节,“DROP INDEX 语法”。要确定索引名称,请使用SHOW INDEX FROM tbl_name

某些存储引擎允许您在创建索引时指定索引类型。 index_type说明符的语法是USING type_name。有关USING的详细信息,请参阅第 13.1.14 节,“CREATE INDEX 语法”。首选位置在列列表之后。在将来的 MySQL 版本中,将删除在列列表之前使用该选项的支持。

index_option values 指定索引的其他选项。有关允许的index_option值的详细信息,请参阅第 13.1.14 节,“CREATE INDEX 语法”

RENAME INDEX old_index_name TO new_index_name重命名索引。这是标准 SQL 的 MySQL 扩展。 table 的内容保持不变。 old_index_name必须是 table 中未被同一ALTER TABLE语句删除的现有索引的 name。 new_index_name是新索引 name,在应用更改后,不能复制生成的 table 中索引的 name。 index name 都不能是PRIMARY

如果在MyISAM table 上使用ALTER TABLE,则所有非唯一索引都在单独的批处理中创建(与维修表一样)。当你有很多索引时,这应该会使ALTER TABLE更快。

对于MyISAM表,可以显式控制 key 更新。使用ALTER TABLE ... DISABLE KEYS告诉 MySQL 停止更新非唯一索引。然后使用ALTER TABLE ... ENABLE KEYS到 re-create 缺少索引。 MyISAM使用一种比逐个插入密钥快得多的特殊算法,因此在执行批量插入操作之前禁用密钥应该会带来相当大的加速。除了前面提到的特权之外,使用ALTER TABLE ... DISABLE KEYS还需要指数特权。

虽然禁用了非唯一索引,但是对于选择说明这样的 statements 会忽略它们,否则会使用它们。

ALTER TABLE语句之后,可能需要 run 分析 TABLE来更新索引基数信息。见第 13.7.5.22 节,“SHOW INDEX 语法”

外键和其他限制

InnoDBNDB子句由InnoDBNDB存储引擎支持,它们实现ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...)。见第 1.8.3.2 节,“FOREIGN KEY 约束”;有关InnoDB的特定信息,请参阅第 14.6.1.5 节,“InnoDB 和 FOREIGN KEY 约束”。对于其他存储引擎,将解析子句但忽略这些子句。

CHECK约束子句被解析但被所有存储引擎忽略。见第 13.1.18 节,“CREATE TABLE 语法”。接受但忽略语法子句的原因是兼容性,以便更容易从其他 SQL 服务器 port code,以及使用 references 创建表的 run applications。见第 1.8.2 节,“MySQL 与标准 SQL 的区别”

对于ALTER TABLE,与创建 TABLE不同,ADD FOREIGN KEY如果给定则忽略index_name并使用自动生成的外部 key name。要解决此问题,请包含CONSTRAINT子句以指定外部 key name:

ADD CONSTRAINT name FOREIGN KEY (....) ...

重要
MySQL 默默地忽略内联REFERENCES规范,其中 references 被定义为列规范的一部分。 MySQL 只接受REFERENCES子句定义为单独的FOREIGN KEY规范的一部分。
注意
分区的InnoDB表不支持外键。此限制不适用于NDB表,包括由[LINEAR] KEY明确分区的表。有关更多信息,请参阅第 22.6.2 节,“分区与存储引擎相关的限制”

MySQL Server 和 NDB Cluster 都支持使用ALTER TABLE删除外键:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

ALTER TABLE ... ALGORITHM=INPLACE中支持在同一ALTER TABLE语句中添加和删除外部 key,但不支持ALTER TABLE ... ALGORITHM=COPY

服务器禁止更改可能导致参照完整性丢失的外部 key 列。解决方法是在更改列定义之前使用ALTER TABLE ... DROP FOREIGN KEY,然后使用ALTER TABLE ...添加外国 KEY。禁止更改的示例包括:

  • 对可能不安全的外部 key 列的数据类型的更改。例如,允许将VARCHAR(20)更改为VARCHAR(30),但将其更改为VARCHAR(1024)并不是因为它会更改 store 单个值所需的长度字节数。

  • 禁止在 non-strict 模式下将NULL列更改为NOT NULL,以防止将NULL值转换为默认的非NULL值,而在引用的 table 中没有相应的值。在严格模式下允许该操作,但如果需要任何此类转换,则会返回错误。

    ALTER TABLE tbl_name RENAME new_tbl_name更改内部生成的外部 key 约束名称和 user-defined 外部 key 约束名称,以 string“tbl_name ibfk”开头以反映新的 table name。 InnoDB将以 string“tbl_name ibfk”开头的外部 key 约束名称解释为内部生成的名称。

更改字符集

要将 table 缺省字符集和所有字符列(CHARVARCHAR文本)更改为新字符集,请使用如下语句:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

该语句还会更改所有字符列的排序规则。如果未指定COLLATE子句来指示要使用的归类,则该语句将使用字符集的默认归类。如果此排序规则不适合预期的 table 使用(例如,如果它将从 case-sensitive 排序规则更改为 case-insensitive 排序规则),请明确指定排序规则。

对于数据类型为VARCHAR文本类型之一的列,CONVERT TO CHARACTER SET会根据需要更改数据类型,以确保新列 long 足以 store 存储与原始列一样多的字符。对于 example,文本列有两个长度字节,用于存储列中 byte-length 的值,最多为 65,535。对于latin1 文本列,每个字符都需要一个字节,因此该列最多可以存储 65,535 个字符。如果该列转换为utf8,则每个字符最多可能需要三个字节,最大可能长度为 3×65,535 = 196,605 字节。该长度不适合文本列的长度字节,因此 MySQL 将数据类型转换为MEDIUMTEXT,这是最小的 string 类型,其长度字节可以记录值 196,605。同样,VARCHAR列可能会转换为MEDIUMTEXT

要避免更改上述类型的数据类型,请不要使用CONVERT TO CHARACTER SET。而是使用MODIFY来更改单个列。例如:

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

如果指定CONVERT TO CHARACTER SET binary,则CHARVARCHAR文本列将转换为其对应的二进制 string 类型(BINARYVARBINARYBLOB)。这意味着列不再具有字符集,后续CONVERT TO操作将不适用于它们。

如果CONVERT TO CHARACTER SETCONVERT TO CHARACTER SET操作中为DEFAULT,则使用由character_set_database系统变量命名的字符集。

警告
CONVERT TO操作转换原始字符和命名字符 sets 之间的列值。如果您在一个字符集中有一个列(如latin1),则这不是您想要的,但存储的值实际上使用其他一些不兼容的字符集(如utf8)。在这种情况下,您必须为每个此类列执行以下操作:

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

这样做的原因是当您转换为BLOB列或从BLOB列转换时没有转换。
要仅更改 table 的默认字符集,请使用以下语句:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

单词DEFAULT是可选的。如果您未为稍后添加到 table 的列指定字符集(对于 example,使用ALTER TABLE ... ADD column),则默认字符集是使用的字符集。

启用foreignkey_checks系统变量(默认设置)时,不允许对包含外部 key 约束中使用的字符 string 列的表进行字符集转换。解决方法是在执行字符集转换之前禁用foreignkey_checks。您必须在 re-enabling foreignkey_checks之前对外部 key 约束中涉及的两个表执行转换。如果在仅转换其中一个表后 re-enable foreignkey_checks,则由于在这些操作期间发生的隐式转换,或ON UPDATE CASCADE操作可能会破坏引用 table 中的数据(Bug#45290,Bug#74816)。

丢弃和 Importing InnoDB 表空间

可以使用DISCARD TABLESPACEIMPORT TABLESPACE选项丢弃和导入在其自己的file-per-table表空间中创建的InnoDB table。这些选项可用于从备份中导入 file-per-table 表空间或将 file-per-table 表空间从一个数据库服务器复制到另一个数据库服务器。见部分 14.6.3.7,“将表空间复制到另一个实例”

MyISAM 表的行 Order

ORDER BY使您可以使用特定 order 中的行创建新的 table。当您知道在大多数 time 中查询某个 order 中的行时,此选项很有用。通过在对 table 进行重大更改后使用此选项,您可能可以获得更高的 performance。在某些情况下,如果 table 由您想要稍后对其进行排序的列进行排序,则可能会使 MySQL 更容易排序。

注意
插入和删除后 table 不会保留在指定的 order 中。
ORDER BY语法允许为排序指定一个或多个列名,每个列名可选地后跟ASCDESC以分别表示升序或降序 sort order。默认值为升序 order。只允许列名作为排序标准;不允许使用任意表达式。该条款应在任何其他条款之后最后给出。

ORDER BY对于InnoDB表没有意义,因为InnoDB总是根据聚集索引命令 table 行。

在分区 table 上使用时,ALTER TABLE ... ORDER BY仅在每个分区内排序行。

分区选项

partition_options表示可以与分区表一起使用的选项,用于重新分区,添加,删除,丢弃,import,合并和拆分分区,以及执行分区维护。

ALTER TABLE语句可能包含PARTITION BYREMOVE PARTITIONING子句以及其他更改规范,但必须在任何其他规范之后最后指定PARTITION BYREMOVE PARTITIONING子句。 ADD PARTITIONDROP PARTITIONDISCARD PARTITIONIMPORT PARTITIONCOALESCE PARTITIONREORGANIZE PARTITIONEXCHANGE PARTITIONANALYZE PARTITIONCHECK PARTITIONREPAIR PARTITION选项不能与单个ALTER TABLE中的其他更改规范组合,因为刚刚列出的选项作用于单个分区。

有关分区选项的更多信息,请参阅第 13.1.18 节,“CREATE TABLE 语法”第 13.1.8.1 节,“ALTER TABLE 分区操作”。有关ALTER TABLE ... EXCHANGE PARTITION statements 的信息和示例,请参阅第 22.3.3 节,“与表交换分区和子分区”

在 MySQL 5.7.6 之前,分区InnoDB表使用MyISAM和其他不提供自己的分区处理程序的存储引擎使用的通用ha_partition分区处理程序;在 MySQL 5.7.6 及更高版本中,这些表是使用InnoDB存储引擎自己的(或“本机”)分区处理程序创建的。从 MySQL 5.7.9 开始,您可以使用ALTER TABLE ... UPGRADE PARTITIONING将在 MySQL 5.7.6 或更早版本(即使用ha_partition创建)中创建的InnoDB table 升级到InnoDB本机分区处理程序。 (Bug#76734,Bug#20727344)此ALTER TABLE语法不接受任何其他选项,并且只能在 time 上的单个 table 上使用。您还可以在 MySQL 5.7.9 或更高版本中使用mysql_upgrade将旧的分区InnoDB表升级到本机分区处理程序。

Updated at: 7 months ago
ALTER SERVER 语法Table of contentALTER TABLE 分区操作