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 [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)} |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] | CONVERTTO 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_nameTO new_col_name |RENAME {INDEX |KEY } old_index_nameTO 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_namesINTO (partition_definitions) |EXCHANGE PARTITION partition_nameWITH 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: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: (seeCREATE TABLE options )
ALTER TABLE
更改表的结构。例如,您可以添加或删除列,创建或销毁索引,更改现有列的类型或重命名列或表本身。您还可以更改特征,例如用于表或表注释的存储引擎。
- 要使用
ALTER TABLE
,你需要ALTER
,CREATE
和INSERT
权限表。重命名表需要ALTER
和DROP
对旧表,ALTER
,CREATE
,和INSERT
对新表。 - 在表名之后,指定要进行的更改。如果没有给出,
ALTER TABLE
则不执行任何操作。 - 许多允许的更改的语法类似于该
CREATE TABLE
语句的子句。column_definition
条款使用相同的语法ADD
和CHANGE
作为CREATE TABLE
。有关更多信息,请参见“ CREATE TABLE语句”。 - 该词
COLUMN
是可选的,并且可以省略,除了RENAME COLUMN
(以区分列重命名操作和RENAME
表重命名操作)。 多
ADD
,ALTER
,DROP
,和CHANGE
条款,允许在一个单一的ALTER TABLE
声明中,用逗号隔开。这是对标准SQL的MySQL扩展,每个ALTER TABLE
语句仅允许每个子句之一。例如,要在单个语句中删除多个列,请执行以下操作:ALTER TABLE t2DROP COLUMN c,DROP COLUMN d;- 如果存储引擎不支持尝试的
ALTER TABLE
操作,则可能会产生警告。这样的警告可以用显示SHOW WARNINGS
。请参见“ SHOW WARNINGS语句”。有关故障排除的信息ALTER TABLE
,请参见第B.4.6.1节“ ALTER TABLE的问题”。 - 有关生成的列的信息,请参见“ ALTER TABLE和生成的列”。
- 有关用法示例,请参见“ ALTER TABLE示例”。
InnoDB
在MySQL 8.0.17和更高版本中,使用key_part
规范可以采用形式,支持在JSON列上添加多值索引。有关多值索引的创建和使用以及对多值索引的限制和限制的详细信息,请参阅“多值索引”。(CAST json_path AS type ARRAY)
- 使用
mysql_info()
C API函数,您可以找出所复制的行数ALTER TABLE
。参见“ mysql_info()”。
该ALTER TABLE
语句还有其他几个方面,在本节的以下主题下进行描述:
- 表格选项
- 性能和空间要求
- 并发控制
- 添加和删除列
- 重命名,重新定义和重新排序列
- 主键和索引
- 外键和其他约束
- 更改字符集
- 导入InnoDB表
- MyISAM表的行顺序
- 分区选项
表格选项
table_options
意味着可以在可以使用的一种表选项CREATE TABLE
语句,比如ENGINE
,AUTO_INCREMENT
,AVG_ROW_LENGTH
,MAX_ROWS
,ROW_FORMAT
,或TABLESPACE
。
有关所有表选项的说明,请参见“ CREATE TABLE语句”。但是,ALTER TABLE
忽略DATA DIRECTORY
和INDEX DIRECTORY
作为表选项给定时。ALTER TABLE
只允许它们作为分区选项,并要求您具有FILE
特权。
通过使用表选项,ALTER TABLE
可以方便地更改单个表的特征。例如:
如果
t1
当前不是InnoDB
表,则此语句将其存储引擎更改为InnoDB
:ALTER TABLE t1ENGINE = InnoDB;- 有关将表切换到
InnoDB
存储引擎的注意事项,请参见“将表从MyISAM转换为InnoDB”。 - 指定
ENGINE
子句时,ALTER TABLE
将重建表。即使表已经具有指定的存储引擎,也是如此。 - 在现有表上运行会执行“ null ”操作,该操作可用于对表进行碎片整理,如“对表进行碎片整理”中所述。在表上运行执行相同的功能。
ALTER TABLE tbl_name ENGINE=INNODB
InnoDB
ALTER TABLE
InnoDB
ALTER TABLE tbl_name FORCE
InnoDB
ALTER TABLE tbl_name ENGINE=INNODB
并使用在线DDL。有关更多信息,请参见“ InnoDB和在线DDL”。ALTER TABLE tbl_name FORCE
- 尝试更改表的存储引擎的结果受所需存储引擎是否可用以及
NO_ENGINE_SUBSTITUTION
SQL模式的设置的影响,如“服务器SQL模式”中所述。 - 为防止数据意外丢失,
ALTER TABLE
不能将表的存储引擎更改为MERGE
或BLACKHOLE
。
- 有关将表切换到
要将
InnoDB
表更改为使用压缩的行存储格式:ALTER TABLE t1ROW_FORMAT =COMPRESSED ;该
ENCRYPTION
子句启用或禁用表的页面级数据加密InnoDB
。必须安装并配置密钥环插件才能启用加密。如果
table_encryption_privilege_check
启用了变量,TABLE_ENCRYPTION_ADMIN
则需要特权才能使用其ENCRYPTION
设置与默认模式加密设置不同的子句。在MySQL 8.0.16之前,该
ENCRYPTION
子句仅在更改每个表文件表空间中的表时才受支持。从MySQL 8.0.16开始,ENCRYPTION
驻留在常规表空间中的表也支持该子句。对于驻留在常规表空间中的表,表和表空间的加密必须匹配。
如果未明确指定
ENCRYPTION
子句,则不允许通过将表移至其他表空间或更改存储引擎来更改表加密。从MySQL 8.0.16开始,如果表使用了不支持加密的存储引擎,则指定的
ENCRYPTION
子句的值不能是'N'
或''
不允许。以前,该条款已被接受。ENCRYPTION
也不允许尝试使用不支持加密的存储引擎在启用了加密的架构中创建不包含子句的表。有关更多信息,请参见“ InnoDB静态数据加密”。
重置当前的自动增量值:
ALTER TABLE t1AUTO_INCREMENT = 13;您不能将计数器重置为小于或等于当前使用的值。对于
InnoDB
和MyISAM
,如果值小于或等于AUTO_INCREMENT
列中当前的最大值,则将该值重置为当前最大AUTO_INCREMENT
列值加1。更改默认表字符集:
ALTER TABLE t1CHARACTER SET = utf8;另请参阅更改字符集。
要添加(或更改)表注释:
ALTER TABLE t1COMMENT = 'New table comment';ALTER TABLE
与TABLESPACE
选项一起使用时,可以InnoDB
在现有的常规表空间,每个表文件的表空间和系统表空间之间移动表。请参见使用ALTER TABLE在表空间之间移动表。ALTER TABLE ... TABLESPACE
即使该TABLESPACE
属性未更改其先前的值,操作也会始终导致整个表的重建。ALTER TABLE ... TABLESPACE
语法不支持将表从临时表空间移动到持久表空间。DATA DIRECTORY
受所支持的子句CREATE TABLE ... TABLESPACE
不受所支持ALTER TABLE ... TABLESPACE
,并且如果指定则将被忽略。- 有关该
TABLESPACE
选项的功能和限制的更多信息,请参见CREATE TABLE
。
MySQL NDB Cluster 8.0支持设置
NDB_TABLE
选项来控制表的分区平衡(碎片计数类型),从任何副本读取功能,完全复制或这些的任意组合,ALTER TABLE
以相同的方式作为语句的表注释的一部分如CREATE TABLE
本例所示:ALTER TABLE t1COMMENT = "NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";请记住,
ALTER TABLE ... COMMENT ...
该表将丢弃任何现有注释。有关其他信息和示例,请参见设置NDB_TABLE选项。
要验证是否按预期更改了表选项,请使用SHOW CREATE TABLE
或查询INFORMATION_SCHEMA.TABLES
表。
性能和空间要求
ALTER TABLE
使用以下算法之一处理操作:
COPY
:对原始表的副本执行操作,并将表数据从原始表逐行复制到新表。不允许并发DML。INPLACE
:操作避免复制表数据,但可以在适当位置重建表。在操作的准备和执行阶段可以简短地获取表上的独占元数据锁定。通常,支持并发DML。INSTANT
:操作只能修改数据字典中的元数据。在准备和执行期间,不会在表上获取任何独占元数据锁,并且表数据不受影响,从而使操作立即进行。允许并发DML。(在MySQL 8.0.12中引入)
该ALGORITHM
子句是可选的。如果ALGORITHM
省略该子句,则MySQL将使用支持它的ALGORITHM=INSTANT
存储引擎和ALTER TABLE
子句。否则,ALGORITHM=INPLACE
使用。如果ALGORITHM=INPLACE
不支持,ALGORITHM=COPY
则使用。
指定ALGORITHM
子句要求操作将指定的算法用于支持子句的子句和存储引擎,否则将失败并显示错误。指定ALGORITHM=DEFAULT
与省略ALGORITHM
子句相同。
ALTER TABLE
使用该COPY
算法的操作等待修改表的其他操作完成。将更改应用于表副本后,将数据复制过来,删除原始表,然后将表副本重命名为原始表的名称。ALTER TABLE
执行该操作时,其他会话可以读取原始表(不久后会注意到)。在ALTER TABLE
操作开始之后开始的表的更新和写入将暂停,直到准备好新表,然后自动将其重定向到新表。该表的临时副本在原始表的数据库目录中创建,除非它是RENAME TO
将表移动到位于其他目录中的数据库的操作。
前面提到的一个例外是,ALTER TABLE
在准备好从表和表定义高速缓存中清除过时的表结构时,块读取(而不仅仅是写入)。此时,它必须获得一个排他锁。为此,它等待当前的读取器完成,并阻止新的读取和写入。
ALTER TABLE
使用该COPY
算法的操作可防止并发DML操作。仍然允许并发查询。也就是说,表复制操作始终至少包括的并发限制LOCK=SHARED
(允许查询但不包括DML)。您可以LOCK
通过指定来进一步限制支持该子句的操作的并发性LOCK=EXCLUSIVE
,以防止DML和查询。有关更多信息,请参见并发控制。
要强制将COPY
算法用于ALTER TABLE
原本不会使用的操作,请指定ALGORITHM=COPY
或启用old_alter_table
系统变量。如果old_alter_table
设置与ALGORITHM
值以外DEFAULT
的ALGORITHM
子句之间存在冲突,则该子句优先。
对于InnoDB
表,对驻留在共享表空间中的表ALTER TABLE
使用COPY
算法的操作可能会增加表空间使用的空间量。此类操作需要与表中的数据以及索引一样多的额外空间。对于驻留在共享表空间中的表,操作期间使用的额外空间不会释放回操作系统,就像驻留在每表文件表空间中的表一样。
有关在线DDL操作的空间要求的信息,请参见“在线DDL空间要求”。
ALTER TABLE
支持该INPLACE
算法的操作包括:
ALTER TABLE
InnoDB
在线DDL功能支持的操作。请参见“在线DDL操作”。- 重命名表。MySQL重命名与表相对应的文件,
tbl_name
但不进行复制。(您也可以使用该RENAME TABLE
语句来重命名表。请参见“ RENAME TABLE语句”。)专为重命名表授予的特权不会迁移到新名称。必须手动更改它们。 仅修改表元数据的操作。因为服务器不触摸表内容,所以这些操作是立即进行的。仅元数据操作包括:
- 重命名列。在NDB Cluster 8.0.18和更高版本中,此操作也可以在线执行。
- 更改列的默认值(
NDB
表除外)。 - 只要数据类型的存储大小不变,就可以通过在有效成员值列表的末尾添加新的枚举或设置成员来修改
ENUM
or或SET
column 的定义。例如,将成员添加到具有8个成员的列中,将每个值所需的存储从1字节更改为2字节;这需要一个表副本。在列表中间添加成员会导致对现有成员重新编号,这需要表副本。SET
- 更改空间列的定义以删除
SRID
属性。(添加或更改SRID
属性确实需要重建,并且无法就地完成,因为服务器必须验证所有值都具有指定的SRID值。) 从MySQL 8.0.14开始,在满足以下条件时更改列字符集:
- 列数据类型是
CHAR
,VARCHAR
,一个TEXT
类型,或ENUM
。 - 字符集更改为从
utf8mb3
到utf8mb4
,或任何字符集更改为binary
。 - 列上没有索引。
- 列数据类型是
从MySQL 8.0.14开始,在满足以下条件时更改生成的列:
- 对于
InnoDB
表,这些语句将修改生成的存储列,但不更改其类型,表达式或可为空性。 - 对于非
InnoDB
表,这些语句修改生成的存储列或虚拟列,但不更改其类型,表达式或可为空性。
此类更改的一个示例是对列注释的更改。
- 对于
- 重命名索引。
- 为
InnoDB
和NDB
表添加或删除辅助索引。请参见“在线DDL操作”。 - 对于
NDB
表,是在可变宽度列上添加和删除索引的操作。这些操作在线发生,无需复制表,也不会在大部分时间内阻止并发DML操作。请参见“在NDB群集中使用ALTER TABLE进行在线操作”。 - 通过
ALTER INDEX
操作修改索引可见性。 DEFAULT
如果修改后的列不包含在生成的列表达式中,则包含修改后的列的表的列修改将依赖于带有值的列。例如,NULL
可以在不重建表的情况下就地更改单独列的属性。
ALTER TABLE
支持该INSTANT
算法的操作包括:
- 添加一列。此功能称为“即时
ADD COLUMN
”。有限制条件。请参见“在线DDL操作”。 - 添加或删除虚拟列。
- 添加或删除列默认值。
- 修改
ENUM
或SET
列的定义。与上述相同的限制适用于ALGORITHM=INSTANT
。 - 更改索引类型。
- 重命名表。与上述相同的限制适用于
ALGORITHM=INSTANT
。
有关支持的操作的更多信息ALGORITHM=INSTANT
,请参见“在线DDL操作”。
ALTER TABLE
升级的MySQL 5.5的时间列到5.6格式ADD COLUMN
,CHANGE COLUMN
,MODIFY COLUMN
,ADD INDEX
,和FORCE
操作。使用该INPLACE
算法无法完成此转换,因为必须重建表,因此ALGORITHM=INPLACE
在这些情况下指定会导致错误。ALGORITHM=COPY
如有必要,请指定。
如果ALTER TABLE
对用于通过对表进行分区的多列索引的操作KEY
更改了列的顺序,则只能使用来执行ALGORITHM=COPY
。
WITHOUT VALIDATION
和WITH VALIDATION
条款影响是否ALTER TABLE
执行用于就地操作虚拟生成的列修改。请参见“ ALTER TABLE和Generated Columns”。
NDB Cluster 8.0支持ALGORITHM=INPLACE
使用与标准MySQL Server 相同的语法进行联机操作。有关更多信息,请参见“在NDB群集中使用ALTER TABLE进行在线操作”。
ALTER TABLE
带有DISCARD ... PARTITION ... TABLESPACE
或IMPORT ... PARTITION ... TABLESPACE
不创建任何临时表或临时分区文件。
ALTER TABLE
与ADD PARTITION
,DROP PARTITION
,COALESCE PARTITION
,REBUILD PARTITION
,或REORGANIZE PARTITION
不创建的临时表(当用于除了NDB
表);但是,这些操作可以并且确实会创建临时分区文件。
ADD
或DROP
用于操作RANGE
或LIST
分区直接操作或接近。ADD
或COALESCE
用于操作HASH
或KEY
分区中的所有分区之间复制数据,除非LINEAR HASH
或LINEAR KEY
使用;这实际上与创建新表相同,尽管ADD
or COALESCE
操作是逐个分区执行的。REORGANIZE
操作仅复制已更改的分区,而不会触及未更改的分区。
对于MyISAM
表,可以通过将myisam_sort_buffer_size
系统变量设置为较高的值来加快索引的重新创建(更改过程中最慢的部分)。
并发控制
对于ALTER TABLE
支持它的操作,可以使用该LOCK
子句来控制在更改表时对表进行的并发读取和写入的级别。通过为该子句指定非默认值,可以使您在alter操作期间要求一定数量的并发访问或排他性,如果请求的锁定程度不可用,则可以中止操作。
仅LOCK = DEFAULT
允许使用的操作ALGORITHM=INSTANT
。其他LOCK
子句参数不适用。
该LOCK
子句的参数为:
LOCK =DEFAULT 给定
ALGORITHM
子句(如果有)和ALTER TABLE
操作的最大并发级别:如果支持,则允许并发读写。如果不是,则允许并发读取(如果支持)。如果不是,请强制执行独占访问。LOCK =NONE 如果支持,则允许并发读取和写入。否则,将发生错误。
LOCK = SHARED如果支持,则允许并发读取但阻止写入。即使存储引擎为给定的
ALGORITHM
子句(如果有)和ALTER TABLE
操作支持并发写操作,也会阻止写操作。如果不支持并发读取,则会发生错误。LOCK = EXCLUSIVE强制执行独占访问。即使存储引擎支持给定
ALGORITHM
子句(如果有)和ALTER TABLE
操作并发读取/写入,也可以执行此操作。
添加和删除列
使用ADD
新列添加到表,DROP
删除现有列。是标准SQL的MySQL扩展。DROP col_name
要在表格行内的特定位置添加列,请使用FIRST
或。默认为最后添加该列。AFTER col_name
如果表仅包含一列,则不能删除该列。如果要删除表,请使用该DROP TABLE
语句。
如果从表中删除了列,则这些列也将从它们所属的任何索引中删除。如果组成索引的所有列均被删除,则索引也将被删除。如果使用CHANGE
或MODIFY
缩短列中存在索引的列,并且结果列长度小于索引长度,则MySQL自动缩短索引。
对于ALTER TABLE ... ADD
,如果该列具有使用不确定函数的表达式默认值,则该语句可能会产生警告或错误。有关详细信息,请参见“使用GTID复制的限制”。
重命名,重新定义和重新排序列
在CHANGE
,MODIFY
,RENAME COLUMN
,和ALTER
条款允许的名称和现有列的定义被改变。它们具有以下比较特征:
CHANGE
:- 可以重命名列并更改其定义,或同时更改两者。
- 具有比
MODIFY
或更大的功能RENAME COLUMN
,但以某些操作的便利性为代价。CHANGE
如果不重命名,则需要两次命名该列;如果仅重命名,则需要重新指定列定义。 - 使用
FIRST
或AFTER
可以重新排序列。
MODIFY
:- 可以更改列定义,但不能更改其名称。
CHANGE
与更改列定义而不重命名相比更方便。- 使用
FIRST
或AFTER
可以重新排序列。
RENAME COLUMN
:- 可以更改列名,但不能更改其定义。
- 比重
CHANGE
命名列而不更改其定义更方便。
ALTER
:仅用于更改列默认值。
CHANGE
是标准SQL的MySQL扩展。MODIFY
并且RENAME COLUMN
是与Oracle兼容的MySQL扩展。
要更改列以更改其名称和定义,请使用CHANGE
,指定旧名称和新名称以及新定义。例如,要将INT NOT NULL
列从重命名a
为b
并更改其定义以BIGINT
在保留NOT NULL
属性的同时使用数据类型,请执行以下操作:
ALTER TABLE t1CHANGE a b BIGINT NOT NULL;
要更改列定义但不更改其名称,请使用CHANGE
或MODIFY
。使用时CHANGE
,语法需要两个列名称,因此必须两次指定相同的名称,以保持名称不变。例如,要更改column的定义b
,请执行以下操作:
ALTER TABLE t1CHANGE b b INT NOT NULL;
MODIFY
更改定义而不更改名称更方便,因为它只需要一次列名:
ALTER TABLE t1MODIFY b INT NOT NULL;
要更改列名但不更改其定义,请使用CHANGE
或RENAME COLUMN
。使用CHANGE
,语法需要列定义,因此要使定义保持不变,您必须重新指定列当前具有的定义。例如,要将INT NOT NULL
列从重命名b
为a
,请执行以下操作:
ALTER TABLE t1CHANGE b a INT NOT NULL;
RENAME COLUMN
在不更改定义的情况下更方便地更改名称,因为它只需要新旧名称即可:
ALTER TABLE t1RENAME COLUMN bTO a;
通常,您不能将列重命名为表中已经存在的名称。但是,有时不是这种情况,例如交换名称或在一个循环中移动它们时。如果表具有名为a
,b
和的列c
,则这些是有效的操作:
-- swap a and bALTER TABLE t1RENAME COLUMN aTO b,RENAME COLUMN bTO a; -- "rotate" a, b, c through a cycleALTER TABLE t1RENAME COLUMN aTO b,RENAME COLUMN bTO c,RENAME COLUMN cTO a;
对于使用CHANGE
或进行的列定义更改MODIFY
,定义必须包括数据类型和应应用于新列的所有属性,而不是诸如PRIMARY KEY
或的索引属性UNIQUE
。原始定义中存在但未为新定义指定的属性不会继续。假设将一列col1
定义为,INT UNSIGNED DEFAULT 1 COMMENT 'my column'
然后按如下所示修改该列,仅打算将其更改INT
为BIGINT
:
ALTER TABLE t1MODIFY col1 BIGINT;
这种说法从改变数据类型INT
来BIGINT
,但它也下降了UNSIGNED
,DEFAULT
和COMMENT
属性。要保留它们,该语句必须明确包括它们:
ALTER TABLE t1MODIFY col1 BIGINTUNSIGNED DEFAULT 1COMMENT 'my column';
对于使用CHANGE
或进行的数据类型更改MODIFY
,MySQL尝试尽可能将现有的列值转换为新的类型。
警告此转换可能会导致数据更改。例如,如果您缩短字符串列,则值可能会被截断。如果在转换为新数据类型会导致数据丢失的情况下阻止操作成功执行,
ALTER TABLE
请在使用前启用严格的SQL模式(请参见“服务器SQL模式”)。
如果使用CHANGE
或MODIFY
缩短列中存在索引的列,并且结果列长度小于索引长度,则MySQL自动缩短索引。
对于用CHANGE
或RENAME COLUMN
重命名的列,MySQL自动将这些引用重命名为重命名的列:
- 引用旧列的索引,包括不可见索引和禁用
MyISAM
索引。 - 引用旧列的外键。
对于用CHANGE
或RENAME COLUMN
重命名的列,MySQL不会自动将这些引用重命名为重命名的列:
- 生成的列和分区表达式引用了重命名的列。您必须使用
CHANGE
与重新ALTER TABLE
命名列的语句相同的语句来重新定义此类表达式。 - 引用重命名列的视图和存储程序。您必须手动更改这些对象的定义以引用新的列名。
要内的表,使用重新排序列FIRST
,并AFTER
在CHANGE
或MODIFY
操作。
ALTER ... SET DEFAULT
或ALTER ... DROP DEFAULT
为列指定新的默认值,或分别删除旧的默认值。如果删除了旧的默认值,并且该列可以是NULL
,则新的默认值是NULL
。如果该列不能为NULL
,则MySQL会如“数据类型默认值”中所述分配默认值。
主键和索引
DROP PRIMARY KEY
删除主键。如果没有主键,则会发生错误。有关主键(尤其是InnoDB
表)的性能特征的信息,请参见“主键外键优化”。
如果sql_require_primary_key
启用了系统变量,则尝试删除主键会产生错误。
如果在表中添加UNIQUE INDEX
或PRIMARY KEY
,MySQL会将其存储在任何非唯一索引之前,以允许尽早检测重复键。
DROP INDEX
删除索引。这是标准SQL的MySQL扩展。请参见“ DROP INDEX语句”。要确定索引名称,请使用。SHOW INDEX FROM tbl_name
一些存储引擎允许您在创建索引时指定索引类型。index_type
说明符的语法为。有关的详细信息,请参见“ CREATE INDEX语句”。首选位置在列列表之后。在将来的MySQL版本中,将删除对列列表之前的选项的使用支持。USING type_name
USING
index_option
值指定索引的其他选项。USING
是这样的选择之一。有关允许index_option
值的详细信息,请参见“ CREATE INDEX语句”。
RENAME INDEX old_index_name TO new_index_name
重命名索引。这是标准SQL的MySQL扩展。该表的内容保持不变。old_index_name
必须是同一ALTER TABLE
语句未删除的表中现有索引的名称。new_index_name
是新的索引名称,在应用更改后,该名称不能与结果表中的索引名称重复。索引名称都不能为PRIMARY
。
如果您ALTER TABLE
在MyISAM
表上使用,则所有非唯一索引都是在单独的批处理中创建的(与一样REPAIR TABLE
)。ALTER TABLE
当您有许多索引时,这应该会更快。
对于MyISAM
表,可以显式控制键更新。使用ALTER TABLE ... DISABLE KEYS
要告诉MySQL停止更新非唯一索引。然后用于ALTER TABLE ... ENABLE KEYS
重新创建丢失的索引。MyISAM
用一种比逐个插入密钥快得多的特殊算法来做到这一点,因此在执行批量插入操作之前禁用密钥应该可以大大提高速度。除了前面提到ALTER TABLE ... DISABLE KEYS
的INDEX
特权外,使用还需要特权。
虽然禁用了非唯一索引,但是对于诸如SELECT
和EXPLAIN
否则会使用它们的语句,它们将被忽略。
一个后ALTER TABLE
声明,可能需要运行ANALYZE TABLE
到更新索引集的信息。请参见“ SHOW INDEX语句”。
该ALTER INDEX
操作允许使索引可见或不可见。优化器不使用不可见索引。索引可见性的修改适用于除主键(显式或隐式)以外的索引。此功能与存储引擎无关(任何引擎都支持)。有关更多信息,请参见“不可见索引”。
外键和其他约束
在FOREIGN KEY
和REFERENCES
条款由支持InnoDB
和NDB
存储引擎,它实现。请参见“外键约束”。对于其他存储引擎,这些子句会被解析但会被忽略。ADD[CONSTRAINT[symbol]] FOREIGN KEY[index_name](...)REFERENCES ...(...)
对于ALTER TABLE
,与不同CREATE TABLE
,ADD FOREIGN KEY
忽略index_name
如果给出,则使用自动生成的外键名称。解决方法是,包括该CONSTRAINT
子句以指定外键名称:
ADD CONSTRAINT nameFOREIGN KEY (....) ...
重要MySQL默默地忽略了内联
REFERENCES
规范,其中引用被定义为列规范的一部分。MySQL仅接受REFERENCES
定义为单独FOREIGN KEY
规范一部分的子句。
注意分区
InnoDB
表不支持外键。此限制不适用于NDB
表,包括由明确分区的表[LINEAR] KEY
。有关更多信息,请参见“与存储引擎有关的分区限制”。
MySQL Server和NDB Cluster都支持使用ALTER TABLE
删除外键:
ALTER TABLE tbl_nameDROP FOREIGN KEY fk_symbol;
ALTER TABLE
支持ALTER TABLE ... ALGORITHM=INPLACE
但不支持在同一语句中添加和删除外键ALTER TABLE ... ALGORITHM=COPY
。
服务器禁止更改外键列,这可能会导致丢失引用完整性。解决方法是ALTER TABLE ... DROP FOREIGN KEY
在更改列定义之前和ALTER TABLE ... ADD FOREIGN KEY
之后使用。禁止更改的示例包括:
- 更改外键列的数据类型可能是不安全的。例如,允许更改
VARCHAR(20)
为VARCHAR(30)
,但不允许更改为,VARCHAR(1024)
因为这会更改存储单个值所需的长度字节数。 - 禁止将
NULL
列更改NOT NULL
为非严格模式,以防止将NULL
值转换为默认非NULL
值,在参考表中没有默认值。在严格模式下允许该操作,但是如果需要任何此类转换,则会返回错误。
ALTER TABLE tbl_name RENAME new_tbl_name
改变了以字符串内部生成的外键约束名称和用户定义的外键约束的名字“tbl_name
_ibfk_ ”,以反映新表名。InnoDB
将以字符串“tbl_name
_ibfk_ ”开头的外键约束名称解释为内部生成的名称。
在MySQL 8.0.16之前,ALTER TABLE
仅允许以下有限版本的CHECK
约束添加语法被解析和忽略:
ADD CHECK (expr)
从MySQL 8.0.16开始,ALTER TABLE
允许CHECK
添加,删除或更改现有表的约束:
添加一个新的
CHECK
约束:ALTER TABLE tbl_nameADD CONSTRAINT [symbol]CHECK (expr) [[NOT]ENFORCED ];约束语法元素的含义与相同
CREATE TABLE
。请参见“检查约束”。删除一个
CHECK
名为的现有约束symbol
:ALTER TABLE tbl_nameDROP CHECK symbol;更改是否强制执行
CHECK
名为的现有约束symbol
:ALTER TABLE tbl_nameALTER CHECK symbol [NOT]ENFORCED ;
在DROP CHECK
和ALTER CHECK
条款是MySQL的扩展标准SQL。
从MySQL 8.0.19开始,ALTER TABLE
允许使用更通用(和SQL标准)的语法来删除和更改任何类型的现有约束,其中约束类型由约束名称确定:
删除一个名为的现有约束
symbol
:ALTER TABLE tbl_nameDROP CONSTRAINT symbol;如果
sql_require_primary_key
启用了系统变量,则尝试删除主键会产生错误。更改是否
symbol
强制执行名为的现有约束:ALTER TABLE tbl_nameALTER CONSTRAINT symbol [NOT]ENFORCED ;只有
CHECK
约束可以更改为不强制执行。所有其他约束类型始终被强制执行。
SQL标准指定所有类型的约束(主键,唯一索引,外键,检查)都属于同一名称空间。在MySQL中,每个约束类型在每个模式都有自己的名称空间。因此,每种约束类型的名称在每个架构中必须唯一,但是不同类型的约束可以具有相同的名称。当多个约束具有相同的名称,DROP CONSTRAINT
并且ADD CONSTRAINT
是不明确的,就会发生错误。在这种情况下,必须使用特定于约束的语法来修改约束。例如,使用DROP PRIMARY KEY
或DROP FOREIGN KEY删除主键或外键。
如果表更改导致违反强制CHECK
约束,则会发生错误并且表不会被修改。发生错误的操作示例:
- 尝试将
AUTO_INCREMENT
属性添加到CHECK
约束中使用的列中。 - 尝试添加强制
CHECK
约束或强制CHECK
现有行违反约束条件的非强制约束。 - 尝试修改,重命名或删除
CHECK
约束中使用的列,除非该约束也被删除在同一条语句中。例外:如果CHECK
约束仅引用单个列,则删除该列会自动删除该约束。
ALTER TABLE tbl_name RENAME new_tbl_name
更改内部生成的和用户定义的CHECK
约束名称,这些约束名称以字符串“tbl_name
_chk_ ”开头以反映新的表名称。MySQL CHECK
将以字符串“tbl_name
_chk_ ”开头的约束名称解释为内部生成的名称。
更改字符集
要更改默认的表字符集和所有字符列(CHAR
,VARCHAR
,TEXT
)到一个新的字符集,可使用如下语句:
ALTER TABLE tbl_name CONVERTTO CHARACTER SET charset_name;
该语句还更改所有字符列的排序规则。如果您没有指定任何COLLATE
子句来指示要使用哪种排序规则,则该语句将默认排序规则用于字符集。如果此排序规则不适合预期的表使用(例如,如果它将从区分大小写的排序规则更改为不区分大小写的排序规则),则显式指定排序规则。
对于具有的数据类型的列VARCHAR
或一个TEXT
类型,CONVERT TO CHARACTER SET
改变数据类型必须确保新列是足够长的时间来存储尽可能多的字符原始列。例如,一TEXT
列有两个长度的字节,它们存储列中值的字节长度,最大为65,535。对于一latin1
TEXT
列,每个字符都需要一个字节,因此该列最多可以存储65,535个字符。如果将列转换为utf8
,则每个字符最多可能需要三个字节,最大可能的长度为3×65,535 = 196,605字节。该长度不适合TEXT
列的长度字节,因此MySQL将数据类型转换为MEDIUMTEXT
,这是长度字节可以记录196,605的最小字符串类型。同样,VARCHAR
列可能会转换为MEDIUMTEXT
。
为避免数据类型更改为刚刚描述的类型,请勿使用CONVERT TO CHARACTER SET
。而是使用MODIFY
更改单个列。例如:
ALTER TABLE tMODIFY latin1_text_col TEXTCHARACTER SET utf8;ALTER TABLE tMODIFY latin1_varchar_col VARCHAR(M)CHARACTER SET utf8;
如果您指定CONVERT TO CHARACTER SET binary
的CHAR
,VARCHAR
和TEXT
列转换为它们相应的二进制字符串类型(BINARY
,VARBINARY
,BLOB
)。这意味着这些列将不再具有字符集,并且后续CONVERT TO
操作将不适用于它们。
如果charset_name
是DEFAULT
在一个CONVERT TO CHARACTER SET
操作中,由命名的字符集character_set_database
被用于系统变量。
警告该
CONVERT TO
操作在原始字符集和命名字符集之间转换列值。如果您在一个字符集(例如)中有一列,那么这不是您想要的,latin1
但是存储的值实际上使用其他不兼容的字符集(例如utf8
)。在这种情况下,您必须为每个此类列执行以下操作:ALTER TABLE t1CHANGE c1 c1 BLOB;ALTER TABLE t1CHANGE c1 c1 TEXTCHARACTER SET utf8;
之所以可行,是因为您在与BLOB
列之间进行转换时不进行转换。
要仅更改表的默认字符集,请使用以下语句:
ALTER TABLE tbl_nameDEFAULT CHARACTER SET charset_name;
这个词DEFAULT
是可选的。默认字符集是如果不为稍后添加到表中的列指定字符集时使用的字符集(例如,使用ALTER TABLE ... ADD column
)。
当foreign_key_checks
启用了系统变量,这是默认设置,字符集转换不包括外键约束中使用的字符串列的表允许的。解决方法是foreign_key_checks
在执行字符集转换之前禁用。重新启用之前,必须对涉及外键约束的两个表都执行转换foreign_key_checks
。如果foreign_key_checks
仅在转换一个表之后重新启用,则由于这些操作期间发生隐式转换,所以ON DELETE CASCADE
or ON UPDATE CASCADE
操作可能会破坏引用表中的数据(错误#45290,错误#74816)。
导入InnoDB表
一个InnoDB
在自己创建的表文件的每个表的表空间可以从备份或使用另一个MySQL服务器实例导入DISCARD TABLEPACE
和IMPORT TABLESPACE
条款。请参见“导入InnoDB表”。
MyISAM表的行顺序
ORDER BY
使您可以按特定顺序创建包含行的新表。该选项主要在您知道大多数情况下以某种顺序查询行时才有用。通过在对该表进行重大更改之后使用此选项,可能会获得更高的性能。在某些情况下,如果表按要稍后排序的列排序,则对于MySQL排序可能会更容易。
注意插入和删除后,表未按指定顺序保留。
ORDER BY
语法允许指定一个或多个列名进行排序,每个列名都可以分别跟随ASC
或DESC
指示升序或降序。默认为升序。只允许使用列名作为排序条件;不允许使用任意表达式。该条款应在其他任何条款之后最后给出。
ORDER BY
对InnoDB
表没有意义,因为InnoDB
总是根据聚簇索引对表行进行排序。
在分区表上使用时,ALTER TABLE ... ORDER BY
仅对每个分区内的行进行排序。
分区选项
partition_options
表示可与分区表一起使用的选项,用于重新分区,添加,删除,丢弃,导入,合并和拆分分区以及执行分区维护。
这是可能的ALTER TABLE
语句包含一个PARTITION BY
或REMOVE PARTITIONING
子句中,除其他更改规格,但PARTITION BY
还是REMOVE PARTITIONING
子句必须最后任何其他规格后指定。的ADD PARTITION
,DROP PARTITION
,DISCARD PARTITION
,IMPORT PARTITION
,COALESCE PARTITION
,REORGANIZE PARTITION
,EXCHANGE PARTITION
,ANALYZE PARTITION
,CHECK PARTITION
,和REPAIR PARTITION
选项不能与其他单一更改规格组合ALTER TABLE
,因为选择刚上市就单个分区的行为。
有关分区选项的更多信息,请参见“ CREATE TABLE语句”和“ ALTER TABLE分区操作”。有关ALTER TABLE ... EXCHANGE PARTITION
语句的信息和示例,请参见“用表交换分区和子分区”。