ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options
  | 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

ALTER TABLE用于更改原有表的结构。例如,您可以增加或删减列,创建或取消索引,更改原有列的类型,或重新命名列或表。您还可以更改表的评注和表的类型。

允许进行的变更中,许多子句的语法与CREATE TABLE中的子句的语法相近。其中包括table_options修改,选项有ENGINE, AUTO_INCREMENTAVG_ROW_LENGTH等。请见13.1.5节,“CREATE TABLE语法”

存储引擎不支持有些操作,如果进行这些操作,会出现警告。使用SHOW WARNINGS可以显示出这些警告。请参见13.5.4.22节,“SHOW WARNINGS语法”

如果您使用ALTER TABLE更改列规约,但是DESCRIBE tbl_name提示您列规约并没有改变,则可能是因为MySQL忽略了您所做的更改。忽略更改的原因见13.1.5.1节,“沉寂的列规格变更”。例如,如果您试图把VARCHAR列更改为CHAR列,此时,如果表包含其它长度可变的列,则MySQL仍会使用VARCHAR

ALTER TABLE运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行ALTER TABLE时,其它用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。

注意,如果您在执行ALTER TABLE时使用除了RENAME以外的选项,则MySQL会创建一个临时表。即使数据并不需要进行复制(例如当您更改列的名称时),MySQL也会这么操作。对于MyISAM表,您可以通过把myisam_sort_buffer_size系统变量设置到一个较高的值,来加快重新创建索引(该操作是变更过程中速度最慢的一部分)的速度。

·         要使用ALTER TABLE,您需要获得表的ALTER, INSERTCREATE权限。

·         IGNOREMySQL相对于标准SQL的扩展。如果在新表中有重复关键字,或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。并且,对错误值进行修正,使之尽量接近正确值。

·         您可以在一个ALTER TABLE语句里写入多个ADD, ALTER, DROPCHANGE子句,中间用逗号分开。这是MySQL相对于标准SQL的扩展。在标准SQL中,每个ALTER TABLE语句中每个子句只允许使用一次。例如,在一个语句中取消多个列:

·                mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

·         CHANGE col_name, DROP col_nameDROP INDEXMySQL相对于标准SQL的扩展。

·         MODIFYOracleALTER TABLE的扩展。

·         COLUMN只是自选项目,可以忽略。

·         如果您使用ALTER TABLE tbl_name RENAME TO new_tbl_name并且没有其它选项,则MySQL只对与table tbl_name相对应的文件进行重命名。不需要创建一个临时表。(您也可以使用RENAME TABLE语句对表进行重命名。请参见13.1.9节,“RENAME TABLE语法”。)

·         column_definition子句使用与CREATE TABLE中的ADDCHANGE子句相同的语法。注意,此语法包括列名称,而不只是列类型。请参见13.1.5节,“CREATE TABLE语法”

·         您可以使用CHANGE old_col_name column_definition子句对列进行重命名。重命名时,需给定旧的和新的列名称和列当前的类型。例如:要把一个INTEGER列的名称从a变更到b,您需要如下操作:

·                mysql> ALTER TABLE t1 CHANGE a b INTEGER;

如果您想要更改列的类型而不是名称, CHANGE语法仍然要求旧的和新的列名称,即使旧的和新的列名称是一样的。例如:

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

您也可以使用MODIFY来改变列的类型,此时不需要重命名:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

·         如果您使用CHANGEMODITY缩短列长时,列中存在有索引,并且缩短后的列长小于索引长度,则MySQL会自动缩短索引的长度。

·         当您使用CHANGEMODIFY更改列的类型时,MySQL会尽量把原有的列值转化为新的类型。

·         您可以使用FIRSTAFTER col_name在一个表行中的某个特定位置添加列。默认把列添加到最后。您也可以在CHANGEMODIFY语句中使用FIRSTAFTER

·         AFTER COLUMN用于指定列的新默认值,或删除旧的默认值。如果旧的默认值被删除同时列值为NULL,则新的默认值为NULL。如果列值不能为NULLMySQL会指定一个默认值,请参见13.1.5节,“CREATE TABLE语法”

·         DROP INDEX用于取消索引。这是MySQL相对于标准SQL的扩展。请参见13.1.7节,“DROP INDEX语法”

·         如果列从表中被取消了,则这些列也从相应的索引中被取消。如果组成一个索引的所有列均被取消,则该索引也被取消。

·         如果一个表只包含一列,则此列不能被取消。如果您想要取消表,应使用DROP TABLE

·         DROP PRIMAY DEY用于取消主索引。注释:在MySQL较早的版本中,如果没有主索引,则DROP PRIMARY KEY会取消表中的第一个UNIQUE索引。在MySQL 5.1中不会出现这种情况。如果在MySQL 5.1中对没有主键的表使用DROP PRIMARY KEY,则会出现错误信息。

如果您向表中添加UNIQUE KEYPRIMARY KEY,则UNIQUE KEYPRIMARY KEY会被储存在非唯一索引之前,这样MySQL就可以尽早地检查出重复关键字。

·         ORDER BY用于在创建新表时,让各行按一定的顺序排列。注意,在插入和删除后,表不会仍保持此顺序。当您知道多数情况下您会按照特定的顺序查询各行时,可以使用这个选项;在对表进行了大的改动后,通过使用此选项,您可以提高查询效率。在有些情况下,如果表按列排序,对于MySQL来说,排序可能会更简单。

·         如果您对一个MyISAM表使用ALTER TABLE,则所有非唯一索引会被创建到一个单独的批里(和REPAIR TABLE相同)。当您有许多索引时,这样做可以使ALTER TABLE的速度更快。

这项功能可以明确激活。ALTER TABLE...DISABLE KEYSMySQL停止更新MyISAM表中的非唯一索引。然后使用ALTER TABLE ... ENABLE KEYS重新创建丢失的索引。进行此操作时,MySQL采用一种特殊的算法,比一个接一个地插入关键字要快很多。因此,在进行成批插入操作前先使关键字禁用可以大大地加快速度。使用ALTER TABLE ... DISABLE KEYS除了需要获得以前提到的权限以外,还需要获得INDEX权限。

·         Innodb存储引擎支持FOREIGN KEYREFERENCES子句。Innodb存储引擎执行ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...)。请参见15.2.6.4节,“FOREIGN KEY约束”。对于其它存储引擎,这些子句会被分析,但是会被忽略。对于所有的存储引擎,CHECK子句会被分析,但是会被忽略。请参见13.1.5节,“CREATE TABLE语法”。接受这些子句但又忽略子句的原因是为了提高兼容性,以便更容易地从其它SQL服务器中导入代码,并运行应用程序,创建带参考数据的表。请参见1.8.5节,“MySQL与标准SQL的差别”

·         InnoDB支持使用ALTER TABLE来取消外键:

·                ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

要了解更多信息,请参见15.2.6.4节,“FOREIGN KEY约束”

·         ALTER TABLE忽略DATA DIRECTORYINDEX DIRECTORY表选项。

·         如果您想要把表默认的字符集和所有字符列(CHAR, VARCHAR, TEXT)改为新的字符集,应使用如下语句:

·                ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

警告:前面的操作转换了字符集之间的列类型。如果您有一列使用一种字符集(如latin1),但是存储的值实际上使用了其它的字符集(如utf8),这种情况不是您想要的。此时,您必须对这样的列进行以下操作。

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

这种方法能够实现此功能的原因是,当您转换到BLOB列或从BLOB列转换过来时,并没有发生转换。

如果您指定CONVERT TO CHARACTER SET为二进制,则TEXT列被转换到相应的二进制字符串类型(BINARY, VARBINARY, BLOB)。这意味着这些列将不再有字符集,接下来的CONVERT TO操作也将不适用于这些列。

要仅仅改变一个表的默认字符集,应使用此语句:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

词语DEFAULT为自选项。如果您在向表中添加一个新列时(例如,使用ALTER TABLE...ADD column)没有指定字符集,则此时使用的字符集为默认字符集。

警告:ALTER TABLE...DEFAULT CHARACTER SETALTER TABLE...CHARACTER SET是等价的,只用于更改默认的表字符集。

·         如果InnoDB表在创建时,使用了.ibd文件中的自己的表空间,则这样的文件可以被删除和导入。使用此语句删除.ibd文件:

·                ALTER TABLE tbl_name DISCARD TABLESPACE;

此语句用于删除当前的.ibd文件,所以应首先确认您有一个备份。如果在表空间被删除后尝试打开表格,则会出现错误。

要把备份的.ibd文件还原到表中,需把此文件复制到数据库目录中,然后书写此语句:

ALTER TABLE tbl_name IMPORT TABLESPACE;

15.2.6.6节,“使用按表的表空间”

·         使用mysql_info() C API函数,您可以了解有多少记录已被复制,以及(当使用IGNORE时)有多少记录由于重复关键字的原因已被删除。请参见25.2.3.34节,“mysql_info()”

·         ALTER TABLE也可以用于对带分区的表进行重新分区,功能包括添加、取消、合并和拆分各分区,还可以用于进行分区维护。

对带分区的表使用partition_options子句和ALTER TABLE可以对表进行重新分区,使用时依据partition_options定义的分区方法。本子句以PARTITION BY为开头,然后使用与用于CREATE TABLEpartition_options子句一样的语法和规则(要了解详细信息,请参见13.1.5节,“CREATE TABLE语法”)。注释:MySQL 5.1服务器目前接受此语法,但是不实际执行;等MySQL 5.1开发出来后,将执行此语法。

用于ALTER TABLE ADD PARTITIONpartition_definition子句支持用于CREATE TABLE语句的partition_definition子句的同样名称的选项。(要了解语法和介绍,请参见13.1.5节,“CREATE TABLE语法”。)例如,假设您有一个按照以下方式创建的带分区的表:

CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);    

您可以在表中增加一个新的分区p3,该分区用于储存小于2002的值。添加方法如下:

ALTER TABLE t1 ADD PARTITION p3 VALUES LESS THAN (2002);

注释:您不能使用ALTER TABLE向一个没有进行分区的表添加分区。

DROP PARTITION用于取消一个或多个RANGELIST分区。此命令不能用于HASHKEY 分区;用于这两个分区时,应使用COALESCE PARTITION(见后)。如果被取消的分区其名称列于partition_names清单中,则储存在此分区中的数据也被取消。例如,如果以前已定义的表t1,您可以采用如下方法取消名称为p0p1的分区:

ALTER TABLE DROP PARTITION p0, p1;

ADD PARTITIONDROP PARTITION目前不支持IF [NOT] EXISTS。也不可能对一个分区或一个已分区的表进行重命名。如果您希望对一个分区进行重命名,您必须取消分区,再重新建立;如果您希望对一个已分区的表进行重新命名,您必须取消所有分区,然后对表进行重命名,再添加被取消的分区。

COALESCE PARTITION可以用于使用HASHKEY进行分区的表,以便使用number来减少分区的数目。例如,假设您使用下列方法创建了表t2

CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH(YEAR(started))
PARTITIONS (6);

您可以使用以下命令,把t2使用的分区的数目由6个减少到4个:

ALTER TABLE t2 COALESCE PARTITION 2;

包含在最后一个number分区中的数据将被合并到其余的分区中。在此情况下,分区4和分区5将被合并到前4个分区中(编号为0123的分区)。

如果要更改部分分区,但不更改所有的分区,您可以使用REORGANIZE PARTITION。这个命令有多种使用方法:

o        把多个分区合并为一个分区。通过把多个分区的名称列入partition_names清单,并为partition_definition提供一个单一的定义,可以实现这个功能。

o        把一个原有的分区拆分为多个分区。通过为partition_names命名一个分区,并提供多个partition_definitions,可以实现这个功能。

o        更改使用VALUES LESS THAN定义的分区子集的范围或更改使用VALUES IN定义的分区子集的值清单。

注释:对于没有明确命名的分区,MySQL会自动提供默认名称p0, p1, p2等。

要了解有关ALTER TALBE...REORANIZE PARTITION命令的详细信息,请参见18.3节,“分区管理”

·         多个附加子句用于提供分区维护和修补功能。这些功能与用于非分区表的功能类似。这些功能由CHECK TABLEREPAIR TABLE等命令(这些命令不支持用于分区表)执行。这些子句包括ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITIONREPAIR PARTITION.每个选项均为一个partition_names子句,包括一个或多个分区名称。需要更改的表中必须已存在这些分区。多个分区名称用逗号分隔。要了解更多信息,或要了解举例说明,请参见18.3.3节,“分区维护”

以下例子展示了ALTER TABLE的使用。首先展示表t1。表t1采用如下方法创建:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

把表t1重新命名为t2

mysql> ALTER TABLE t1 RENAME t2;

把列aINTERGER更改为TINYINT NOT NULL(名称保持不变),并把列bCHAR(10)更改为CHAR(20),同时把列b重新命名为列c

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

添加一个新的TIMESTAMP列,名称为d

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

在列d和列a中添加索引:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);

删除列c

mysql> ALTER TABLE t2 DROP COLUMN c;

添加一个新的AUTO_INCREMENT整数列,名称为c

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     ADD PRIMARY KEY (c);

注意我们为c编制了索引(作为PRIMARY KEY),因为AUTO_INCREMENT列必须编制索引。同时我们定义cNOT NULL,因为主键列不能为NULL

当您添加一个AUTO_INCREMENT列时,列值被自动地按序号填入。对于MyISAM表,您可以在ALTER TABLE之前执行SET INSERT_ID=value来设置第一个序号,也可以使用AUTO_INCREMENT=value表选项来设置。请参见13.5.3节,“SET语法”

如果值大于AUTO_INCREMENT列中的最大值,则您可以使用用于InnoDB表的ALTER TALBE...AUTO_INCREMENT=value表选项,来为新行设置序号。如果值小于列中当前的最大值,不会出现错误信息,当前的序列值也不改变。

使用MyISAM表时,如果您不更改AUTO_INCREMENT列,则序列号不受影响。如果您取消一个AUTO_INCREMENT列,然后添加另一个AUTO_INCREMENT列,则序号重新排列,从1开始。

A.7.1节,“与ALTER TABLE有关的问题”