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 EXISTS
和 IF 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]
Contents
- 语法
- 描述
- Index Type
- Character Sets 和 Collations
- WAIT/NOWAIT
- ADD COLUMN [IF NOT EXISTS] (col_name column_definition,...)
- DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]
- MODIFY COLUMN
- CHANGE COLUMN
- ENABLE/ DISABLE KEYS
- ENGINE/FORCE 重建表
- IMPORT TABLESPACE
- ALGORITHM / ALTER TABLE何时会拷贝所有数据?
- LOCK
- 处理进度报告Progress Reports
- 权限说明Privileges
- 示例
- See Also
当在子句中使用了 IF EXISTS
和 IF 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
。
当要创建索引时,在创建过程中存储引擎会使用一个可配置的缓冲区。增大缓冲区大小可以加速索引的创建。 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约束中删除某个字段是不被允许的,例如:
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 | | +-------+---------+------+-----+---------+-------+
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的拷贝算法进行表拷贝。
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_table 和 innodb_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权限。重命名表还需要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;