翻译:MariaDB ALTER TABLE语句

翻译:MariaDB ALTER TABLE语句,mariadbalter

本文是MariaDB官方ALTER TABLE手册的翻译,99%的内容是按照手册给的内容进行翻译的。

原文地址:

我提交到MariaDB官方手册的译文:

本文是MariaDB官方ALTER TABLE手册的翻译,99%的内容是按照手册给的内容进行翻译的。

Syntax

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 10.0.2开始,ALTER TABLE还支持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]

当使用了IF EXISTS或IF NOT EXISTS时,当满足存在或不存在的条件时,查询将不会产生任何错误。

原文地址:https://mariadb.com/kb/en/library/alter-table/

基本描述

ALTER TABLE语句可以改变已存在表的结构。例如,可以增减字段、创建或销毁索引、修改字段类型、重命名字段或重命名表自身。还可以改变表的注释以及表的存储引擎。

如果有某个连接正在使用表,将会激活一个元数据锁,这使得ALTER语句会一直等待直到该锁被释放。这也适用于非事务表

当在已存在重复值的某字段(或某几个字段)上建立UNIQUE索引时,将生成一个错误信息,然后alter语句终止。可以指定IGNORE选项忽略字段中的重复值,以禁止该错误并强制创建UNIQUE索引。但它将无法控制已存在的行。还需要注意,alter table ... exchange partition语句虽然接受IGNORE选项,但会忽略它。

ALTER TABLE可以重命名表,详细信息见我的另一篇译文RENAME TABLE。

当创建索引时,存储引擎将在处理过程中使用可配置的buffer。增大buffer可以加速索引的创建。Aria和MyISAM存储引擎分别根据aria_sort_buffer_sizemyisam_sort_buffer_size指定的值大小来分配buffer,在REPAIR TABLE时也同样会使用其值来分配buffer空间。InnoDB/XtraDB存储引擎使用innodb_sort_buffer_size的值来分配3个具有该值大小的buffer空间。

注:innodb sort buffer的大小影响索引创建速度,还影响online DDL操作时记录并发写的临时日志文件数量。在MariaDB 10.0以前,innodb_sort_buffer_size的大小是固定不可配置的,其值为1M。设置的值越大,在排序时合并结果的次数和阶段就越少,速度就越快。当CREATE TABLE或ALTER TABLE创建新的索引时,将分配3个具有该值大小的buffer空间,还包括缓冲区中的行的指针。该变量为全局变量,单位为字节,默认值为1048576(即1M),有效范围为65536-67108864(即64k-64M)。

我提交到MariaDB官方手册的译文:https://mariadb.com/kb/zh-cn/library/alter-table/

索引类型Index Type

各存储引擎允许的索引类型如下:

Storage Engine Permitted Indexes
Aria BTREE, RTREE
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB BTREE, HASH

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

CREATE INDEXDROP INDEX同样也可以添加或删除索引。

Syntax

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 10.0.2开始,ALTER TABLE还支持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]

当使用了IF EXISTS或IF NOT EXISTS时,当满足存在或不存在的条件时,查询将不会产生任何错误。

字符集和排序规则Character Sets and Collations

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

基本描述

ALTER TABLE语句可以改变已存在表的结构。例如,可以增减字段、创建或销毁索引、修改字段类型、重命名字段或重命名表自身。还可以改变表的注释以及表的存储引擎。

如果有某个连接正在使用表,将会激活一个元数据锁,这使得ALTER语句会一直等待直到该锁被释放。这也适用于非事务表

当在已存在重复值的某字段(或某几个字段)上建立UNIQUE索引时,将生成一个错误信息,然后alter语句终止。可以指定IGNORE选项忽略字段中的重复值,以禁止该错误并强制创建UNIQUE索引。但它将无法控制已存在的行。还需要注意,alter table ... exchange partition语句虽然接受IGNORE选项,但会忽略它。

ALTER TABLE可以重命名表,详细信息见我的另一篇译文RENAME TABLE。

当创建索引时,存储引擎将在处理过程中使用可配置的buffer。增大buffer可以加速索引的创建。Aria和MyISAM存储引擎分别根据aria_sort_buffer_sizemyisam_sort_buffer_size指定的值大小来分配buffer,在REPAIR TABLE时也同样会使用其值来分配buffer空间。InnoDB/XtraDB存储引擎使用innodb_sort_buffer_size的值来分配3个具有该值大小的buffer空间。

注:innodb sort buffer的大小影响索引创建速度,还影响online DDL操作时记录并发写的临时日志文件数量。在MariaDB 10.0以前,innodb_sort_buffer_size的大小是固定不可配置的,其值为1M。设置的值越大,在排序时合并结果的次数和阶段就越少,速度就越快。当CREATE TABLE或ALTER TABLE创建新的索引时,将分配3个具有该值大小的buffer空间,还包括缓冲区中的行的指针。该变量为全局变量,单位为字节,默认值为1048576(即1M),有效范围为65536-67108864(即64k-64M)。

WAIT/NOWAIT

从MariaDB 10.3.0开始引入WAIT和NOWAIT选项,用于设置某些语句的锁等待超时时长。详细信息见我的另一篇译文:MariaDB wait/nowait。

索引类型Index Type

各存储引擎允许的索引类型如下:

Storage Engine Permitted Indexes
Aria BTREE, RTREE
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB BTREE, HASH

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

CREATE INDEXDROP INDEX同样也可以添加或删除索引。

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

向表中添加一个字段。语法同CREATE TABLE。如果使用了IF NOT EXISTS,则待添加的列已存在时不会被创建。这在脚本中想要修改表时非常有用。

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

字符集和排序规则Character Sets and Collations

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

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

删除字段a的过程中将导致新的约束(删除字段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中,它们没有任何作用。

WAIT/NOWAIT

从MariaDB 10.3.0开始引入WAIT和NOWAIT选项,用于设置某些语句的锁等待超时时长。详细信息见我的另一篇译文:MariaDB wait/nowait。

MODIFY COLUMN

可以用来修改字段类型。modify操作不会改变字段顺序,也不会影响索引中的字段元素。注意,当modifiy字段时,必须重新指定新字段的所有属性,但因为不会影响索引中的内容,所以无需再指定约束类属性。

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

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

向表中添加一个字段。语法同CREATE TABLE。如果使用了IF NOT EXISTS,则待添加的列已存在时不会被创建。这在脚本中想要修改表时非常有用。

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

CHANGE COLUMN

change和modify工作方式基本相同,除了change可以改变字段名称。同样不会改变字段顺序和索引中的字段元素。

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

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

删除字段a的过程中将导致新的约束(删除字段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中,它们没有任何作用。

ENABLE/ DISABLE KEYS

DISABLE KEYS将无视表中存储引擎(至少是MyISAM和Aria)支持的所有非unique索引。这可以加速向空表插入数据。

ENABLE KEYS用来启用所有被DISABLE的KEYS。

MODIFY COLUMN

可以用来修改字段类型。modify操作不会改变字段顺序,也不会影响索引中的字段元素。注意,当modifiy字段时,必须重新指定新字段的所有属性,但因为不会影响索引中的内容,所以无需再指定约束类属性。

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

ENGINE/FORCE For Re-building a Table

太阳集团所有网址16877,ALTER TABLE可以强制MariaDB重建(rebuild)表。在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后将不会回收未使用的空间,但新插入数据时可以重新使用这些空间。

CHANGE COLUMN

change和modify工作方式基本相同,除了change可以改变字段名称。同样不会改变字段顺序和索引中的字段元素。

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

本文由太阳集团所有网址16877发布于太阳集团城网址送彩金,转载请注明出处:翻译:MariaDB ALTER TABLE语句

您可能还会对下面的文章感兴趣: