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)
MariaDB starting with 10.0.2

引入了IF EXISTSIF NOT EXISTS 子句,包括:

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]

当在子句中使用了 IF EXISTSIF NOT EXISTS,当不满足存在和不存在的条件时不会报错。但会产生一个warning信息,并且ALTER将会移动到下一个子句(或结束)。

这是在 MDEV-318 中实现的。

MariaDB starting with 10.0.4

MariaDB 10.0.4中引入了 ALTER TABLE ... EXCHANGE PARTITION

MariaDB starting with 10.2.1

MariaDB 10.2.1中引入了 DEFAULT 表达式和 DROP CONSTRAINT 子句

描述

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

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

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

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

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

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

Index Type

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

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

CREATE INDEXDROP 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

MariaDB starting with 10.3.0

WAIT/NOWAIT

设置锁等待超时时间. 见 WAIT and NOWAIT

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

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

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

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

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

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

MariaDB starting with 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

原因是删除字段a的过程中将导致新的约束要求字段b中的所有值都是唯一的。要删除UNIQUE索引中的字段,需要显式指定DROP PRIMARY KEY以及ADD PRIMARY KEY。在MariaDB 10.2.7及之前的版本,都可以直接删除字段并应用新的约束,如下:

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    |       |
+-------+---------+------+-----+---------+-------+

RESTRICTCASCADE 使得从其他数据库系统移植数据变得更简单,但在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的拷贝算法进行表拷贝。

MariaDB starting with 10.0.11

MariaDB 10.0.11开始,Online ALTER TABLE同样可以操作已分区表。

LOCK

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

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

不指定LOCK子句时,默认LOCK=DEFAULT

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

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

注意,如果使用了COPY算法,那么 innodb_file_per_tableinnodb_file_format 变量的当前值会在InnoDB表重建时被提交。

处理进度报告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权限。重命名表还需要DROPCREATE以及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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.