ALTER TABLE

语法

ALTER [ONLINE] [IGNORE] TABLE tbl_name
    [WAIT n | NOWAIT]
    alter_specification [, alter_specification] ...

alter_specification:
    table_option ...
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
  | ALTER [COLUMN] col_name DROP DEFAULT
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DROP CONSTRAINT constraint_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | FORCE
  | partition_options
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | REMOVE PARTITIONING

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value | (expression)]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
  | data_type [GENERATED ALWAYS]  AS   ( <expression> )  {VIRTUAL | PERSISTENT}
      [UNIQUE] [UNIQUE KEY] [COMMENT 'string']

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

index_type:
    USING {BTREE | HASH | RTREE}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | CLUSTERING={YES| NO}

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

ADD COLUMN       [IF NOT EXISTS]
ADD INDEX        [IF NOT EXISTS]
ADD FOREIGN KEY  [IF NOT EXISTS]
ADD PARTITION    [IF NOT EXISTS]
CREATE INDEX     [IF NOT EXISTS]

DROP COLUMN      [IF EXISTS]
DROP INDEX       [IF EXISTS]
DROP FOREIGN KEY [IF EXISTS]
DROP PARTITION   [IF EXISTS]
CHANGE COLUMN    [IF EXISTS]
MODIFY COLUMN    [IF EXISTS]
DROP INDEX       [IF EXISTS]

描述

ALTER TABLE 可以让你改变已存在表的结构。例如，可以添加或删除字段、创建和销毁索引、改变已存在字段的类型、重命名字段或重命名表自身。你还可以修改表的注释信息和表的存储引擎。

如果有其他连接正在使用表，将激活一个 metadata lock ，这使得ALTER语句会一直等待 直到锁被释放。非事务(non-transactional)表也同样如此。

当向一个(或多个)包含重复值的字段创建 UNIQUE 索引，将会报错并终止ALTER语句。可以指定 IGNORE 选项来忽略已存在的重复值，禁止报错并强制创建 UNIQUE 索引。这在某列(或某几列)应该具有UNIQUE属性时但却包含了重复值时很有用；但是，它无法控制已经存在和已经被删除的行。另外还需注意， ALTER TABLE ... EXCHANGE PARTITION 语句接受 IGNORE 选项，但会忽略它。

关于表选项的详细信息，见 CREATE TABLE 。

该语句同样可用来重命名一个表。详细信息见 RENAME TABLE 。

当要创建索引时，在创建过程中存储引擎会使用一个可配置的缓冲区。增大缓冲区大小可以加速索引的创建。 Aria 和 MyISAM 分别根据变量 aria_sort_buffer_size 或 myisam_sort_buffer_size 定义的值大小来分配缓冲区，它同样用于 REPAIR TABLE。 而InnoDB/XtraDB 会根据变量innodb_sort_buffer_size 定义的值大小分配3个缓冲区。

Index Type

不同存储引擎所允许的索引类型的详细信息见 Storage Engine Index Types 。

CLUSTERING={YES|NO} 仅对 Tokudb 有效。

CREATE INDEX 和 DROP INDEX 同样可用来添加或删除索引。

Character Sets 和 Collations

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name

设置 character sets and collations 的详细信息见 Setting Character Sets and Collations 。

ADD COLUMN [IF NOT EXISTS] (col_name column_definition,...)

向表中添加一列。语法和 CREATE TABLE 中的相同。如果使用了 IF NOT EXISTS ，当字段已存在时不会添加列。这在脚本中修改表结构很有用。

FIRST和AFTER字句会影响数据文件datafile中字段的物理顺序。使用FIRST可以将字段添加到表的最左边的位置(即作为第一列)。或者使用AFTER使得新添加的列在任意指定的字段之后。注意，直到目前为止，字段的物理位置顺序通常是无关紧要的。

DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]

从表中删除列。如果使用了 IF EXISTS ，当字段不存在时不会报错。如果字段是某个或某些索引的一部分，删除字段将会从索引中将其删除，除非你在同一时刻创建一个同名的新字段。如果索引中的所有字段都被删除了，则索引会自动被删除。如果在视图或触发器中引用了某个字段，将在下次访问视图或触发器时报错。

从MariaDB 10.2.8开始，从多列复合的UNIQUE约束中删除某个字段是不被允许的，例如：

CREATE TABLE a (
  a int,
  b int,
  primary key (a,b)
);

ALTER TABLE x DROP COLUMN a;
[42000][1072] Key column 'A' doesn't exist in table

ALTER TABLE x DROP COLUMN a;
Query OK, 0 rows affected (0.46 sec)

DESC x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

RESTRICT 和 CASCADE 使得从其他数据库系统移植数据变得更简单，但在MariaDB中，它们没有任何作用。

MODIFY COLUMN

可用来修改字段类型。被修改的列的位置顺序和修改前位置相同，且该列上的所有索引都继续保留。注意，当modifiy字段时，必须重新指定新字段的所有属性。

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY((a));
ALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT;

CHANGE COLUMN

工作方式和 MODIFY COLUMN 类似，但可以修改字段名称。被修改的列的位置顺序和修改前位置相同，且该列上的所有索引都继续保留。

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a));
ALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT;

ENABLE/ DISABLE KEYS

DISABLE KEYS 将禁用存储引擎支持的所有非unique索引 (至少是MyISAM和Aria)。这可以加速 speed up inserts向空表插入数据的过程。

ENABLE KEYS 将启用所有被禁用的索引。

ENGINE/FORCE 重建表

ALTER TABLE 可以强制MariaDB进行表重建(re-build)。在MariaDB 10.0以前，这只能通过设置ENGINE为原值来实现。在MariaDB 10.0中，可以使用FORCE选项。例如，对于一个InnoDB表，可以使用下面的语句进行表重建：

ALTER TABLE tab_name ENGINE = InnoDB;

从MariaDB 10.0开始，这等价于：

ALTER TABLE tab_name FORCE;

对于InnoDB存储引擎，ALTER TABLE将在 innodb_file_per_table 设置为ON时回收未使用的空间(例如，之前删除行后遗留下来的空间)。如果该变量的值为OFF，在ALTER TABLE后将不会回收未使用的空间，但随后新插入数据时可以重用这些空间。

IMPORT TABLESPACE

这用于导入由 FLUSH TABLES FOR EXPORT 创建的InnoDB表。

导入的过程如下：

CREATE TABLE t…; /* using the output from SHOW CREATE TABLE */
ALTER TABLE t DISCARD TABLESPACE; /* effectively corrupts the database! */
/* copy the file t.ibd to the file system */
ALTER TABLE t IMPORT TABLESPACE;

其他存储引擎无需使用ALTER TABLE ... IMPORT。这些数据被拷贝后可以立刻被访问。

ALGORITHM / ALTER TABLE何时会拷贝所有数据?

在MariaDB 10.0之前，ALTER TABLE操作表时会创建该表的临时副本，这会导致操作大表时速度缓慢。从MariaDB 10.0开始，ALTER TABLE的许多操作都可以直接在原地(in-place)操作，不再需要创建表的临时副本。

随着时间的推移，可能越来越多的操作都不再需要拷贝全表数据。以下是目前不需要表拷贝动作的ALTER TABLE行为：

• 修改字段名称。
• 扩大整型数据类型的显示宽度，例如INT(2)-->INT(3)。
• 修改表注释。
• 向enum的列表尾部添加值。
• 重命名表。

如果要执行多个ALTER TABLE操作，并且其中可能有一个或多个操作要求重建表，那么可以很方便地将这些操作组合在单个ALTER TABLE语句中，以便只执行一次重建操作。

从MariaDB 10.0开始，ALTER TABLE开始支持ALGORITHM字句，该字句可以使用3种值：

• ALGORITHM=DEFAULT (未指定ALGORITHM子句时的默认值)
• ALGORITHM=COPY
• ALGORITHM=INPLACE

设置ALGORITHM=COPY时，即使是那些没必要拷贝表数据的操作也会进行copy。这会导致大量表数据的拷贝。

设置ALGORITHM=INPLACE时，将采用in-place技术(原地执行操作)，它会禁止表数据拷贝。如果某操作要求拷贝表数据，将会报如下错误：

CREATE TABLE t1 (a INT, e ENUM ('red','green'));
ALTER TABLE t1 MODIFY e EMUM('red','green','blue'), ALGORITHM=INPLACE;
-> Query OK, 0 rows affected (0.11 sec)
-> Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE t1 ADD c INT, ALGORITHM=INPLACE;
-> ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

默认的行为(ALGORITHM=DEFAULT，或未设置ALGORITHM子句)通常仅在需要拷贝时进行表数据拷贝。可以通过设置系统变量old_alter_table 的值为ON(默认为OFF)来改变它的行为，这种情况下将使用pre-MySQL 5.0的拷贝算法进行表拷贝。

LOCK

不同的操作、不同的存储引擎，ALTER TABLE采用的锁策略也不同。在某些情况下完全不需要任何锁，某些情况下仅需要读锁，某些情况下又需要写锁。LOCK子句可以指定一个固定的锁策略。它会强制使用该锁策略(即使指定的锁策略可能比操作正常需求的锁策略更严格)，但如果存储引擎上的某个操作要求比指定的策略更严格的策略，将生成错误信息。LOCK子句接受的值包括：

• DEFAULT: 采用所允许的最高并发级别的锁。
• NONE: 不采用任何锁，这可能会经常性地产生错误信息。
• SHARED: 采用读锁。
• EXCLUSIVE: 采用写锁。

不指定LOCK子句时，默认LOCK=DEFAULT。

此外，可以使用ALTER ONLINE TABLE确保ALTER TABLE子句不会阻塞任何正在并发的操作(不使用任何锁)，这等价于LOCK=NONE。

当设置ALGORITHM=INPLACE算法时，某些情况下会需要一些临时文件，这些临时文件创建在 tmpdir 系统变量指定的临时目录内。

处理进度报告Progress Reports

从MariaDB 5.3开始，可以在支持进度报告协议的客户端上获取ALTER TABLE的处理进度。例如，从e mysql client客户端：

ALTER TABLE test ENGINE=Aria;
Stage: 1 of 2 'copy to tmp table'    46% of stage

The progress report is also shown in SHOW PROCESSLIST 以及 information_schema.processlist 表同样也可以显示处理进度。

权限说明Privileges

ALTER TABLE要求至少有ALTER权限。重命名表还需要DROP，CREATE以及INSERT权限(因为重命名表是重建表的过程，需要拷贝整个表数据到临时副本，并使用副本填充新表)。

示例

添加一个字段：

ALTER TABLE t1 ADD x INT;

删除一个字段：

ALTER TABLE t1 DROP x;

修改一个字段的类型：

ALTER TABLE t1 MODIFY x bigint unsigned;

修改一个字段的名称和类型：

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

整合多个子句到单个ALTER TABLE语句中，使用逗号分隔：

ALTER TABLE t1 DROP x, ADD x2 INT,  CHANGE y y2 INT;

修改存储引擎：

ALTER TABLE t1 ENGINE = InnoDB;

强制重建表(如果上面的例子中存储引擎已经是InnoDB，也将会重建表)：

ALTER TABLE t1 FORCE;

See Also

• CREATE TABLE
• DROP TABLE
• Character Sets and Collations
• SHOW CREATE TABLE
• ALTER TABLE in SQL99