mysql 命令修改表结构ALTER TABLE 句法

www.111cn.net 更新:2009-11-11 编辑:kp12345 来源:转载

ALTER TABLE 句法

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

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name]
            (index_col_name,...) [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
               [FIRST | AFTER column_name]
  or    MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    DISABLE KEYS
  or    ENABLE KEYS
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col
  or    table_options
ALTER TABLE 允许你改变一个现有表的结构。例如,你可以添加或删除列,创建或撤销索引,更改现有列的类型或将列或表自身更名。你也可以改变表的注释和表的类型。查看章节 6.5.3 CREATE TABLE 句法。

如果你使用 ALTER TABLE 来改变一个列规约,但是 DESCRIBE tbl_name 显示你的列并没有被修改,这有可能是因为章节 6.5.3.1 隐式的列定义变化 描述的一个原因,使 MySQL 忽略了你的修改。例如,如果你尝试将一个 VARCHAR 列更改为 CHAR,而如果在这个表中包含其它的变长列,MySQL 将仍然使用 VARCHAR。

ALTER TABLE 通过建立原初表的一个临时副本来工作。更改在副本上执行,然后原初表将被删除,临时表被换名。这样做使所有的修改自动地转向到没有任何更新失败的新表。当 ALTER TABLE 执行时,原初表可被其它客户端读取。更新与写入被延迟到新的表准备好。

注意,如果你以除 RENAME 之外的其它选项使用 ALTER TABLE ,MySQL 将总是创建一个临时表,即使数据并不确实需要被复制(就像当你改变一个列名时)。我们计划不久来修正它,但是通常人们是不经常执行 ALTER TABLE的,所以在我们的 TODO 上,这个修正并不是急于处理的。对于 MyISAM 表,你可以将变量 myisam_sort_buffer_size 设置和高一点,以加速索引的重建部分(这是重建进程中最慢的部分)。

为了使用 ALTER TABLE,你需要在这个表上有 ALTER、INSERT 和 CREATE 权限。

IGNORE 是 MySQL 对 ANSI SQL92 的扩展。它用于控制当在新表中的唯一键上出现重复值时,ALTER TABLE 如何工作。如果 IGNORE 没有被指定,副本将被放弃并回退。如果 IGNORE 被指定,那么在唯一键上重复的记录行只有第一个记录行被使用;其它的均被删除。

你可以在单个的 ALTER TABLE 语句中发出多个 ADD、ALTER、DROP 和 CHANGE 子句。这是 MySQL 对 ANSI SQL92 的扩展,ANSI SQL92 只允许在每个 ALTER TABLE 语句中一个子句。

CHANGE col_name、DROP col_name 和 DROP INDEX 是 MySQL 对 ANSI SQL92 的扩展。

MODIFY is an Oracle extension to ALTER TABLE.
可选词 COLUMN 只是一个无用词组,可被忽略。

如果你使用 ALTER TABLE tbl_name RENAME TO new_name,并没有任何其它的选项,MySQL 将简单地重命名与表 tbl_name 的文件。这不需要创建临时表。查看章节 6.5.5 RENAME TABLE 句法。

create_definition 子句使用与 CREATE TABLE 相同的 ADD 和 CHANGE 句法。注意,这些句法不仅包含列类型,还要包含列名。查看章节 6.5.3 CREATE TABLE 句法。

你可以使用一个 CHANGE old_col_name create_definition 子句来重命名一个列。为了这样做,你必须指定旧的和新的列名,以及列当前的类型。例如,为了将一个 INTEGER 列 a 重命名为 b,你必须这样做:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
如果你希望改变一个列的类型而不是列名,CHANGE 句法仍然需要有两个列名,即使它们是一样的。例如:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
然后,到 MySQL 3.22.16a 时,你也可以使用 MODIFY 来改变一个列的类型而不需要重命名它:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
如果你使用 CHANGE 或 MODIFY 缩短一个列,而该列上存在一个取列部分值的索引(举例来说,如果你有一个索引在一个 VARCHAR 列的前 10 个字符上),那么,你将不能使列短于索引的字符数目。

当你使用 CHANGE 或 MODIFY 改变一个列类型时,MySQL 将尝试尽可能地将数据转换到新的类型。

在 MySQL 3.22 或更新的版本中,你可以使用 FIRST 或 ADD ... AFTER col_name 在一个表中的某个特定位置添加一列。缺省是增加到最后一列。从 MySQL 4.0.1 开始,你也可以在 CHANGE 或 MODIFY 中使用关键词 FIRST 和 AFTER 。

ALTER COLUMN 可以为一列指定一个新的缺省值或删除老的缺省值。如果老的缺省值被移除且列可以被设为 NULL,新的缺省值将是 NULL。如果该列不允许有 NULL值,MySQL 以章节 6.5.3 CREATE TABLE 句法 中的描述方式为该列赋于一个缺省值。

DROP INDEX 移除一个索引。这是 MySQL 对 ANSI SQL92 的一个扩展。查看章节 6.5.8 DROP INDEX 句法。

如果列被从一个表中移除,列也将从任何有它为组成部分的索引中被移除。如果组成一个索引的所有列均被移除了,那么,该索引也将被移除。

如果一个表只包含一个列,那么该列不能被移除。如果你本就打算移除该表,请使用 DROP TABLE 代替。

DROP PRIMARY KEY 移除主索引。如果这样的索引不存在,它将移除表中的第一个 UNIQUE 索引。(如果没有 PRIMARY KEY 被明确指定,MySQL 将第一个 UNIQUE 键标记为 PRIMARY KEY ) 如果你添加一个 UNIQUE INDEX 或 PRIMARY KEY 到一个表中,它将被存储在任何非 UNIQUE 索引之前,因而,MySQL 可以尽可能地检测出重复键。

ORDER BY 允许你以指定的记录行顺序创建一个新表。注意,在插入与删除后,该表将不会保留这个顺序。在某些情况下,如果表在你以后希望排序的列上是有序的,这将使得 MySQL 排序时更加得容易。当你知道你主要查询的行以一个确定的次序时,这将是很有用的。在对表进行过大的改变后,通过使用这个选项,你可能会得到更高的性能。

如果你在一个 MyISAM 表上使用 ALTER TABLE ,所有非唯一的索引将以一个分批方式创建(就像 REPAIR 一样)。当你有很多索引时,这可能使 ALTER TABLE 更快一点。

从 MySQL 4.0 开始,上面的特性可明确地激活。ALTER TABLE ... DISABLE KEYS 使 MySQL 停止更新 MyISAM 表的非唯一索引。然后 ALTER TABLE ... ENABLE KEYS 可以被用来重建丢失的索引。因为 MySQL 以特殊的算法执行它,这将比一个接一个地插入索引要快得多,禁用键可以很大程序上的加速一个大批量的插入。

使用 C API 函数 mysql_info(),你可以找出有多少记录被拷贝,以及(当 IGNORE 被使用时)有多少记录因唯一键值重复而被删除。

FOREIGN KEY、CHECK 和 REFERENCES 子句实际上不做任何事情,除了对于 InnoDB 类型的表,它支持 ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...)。注意,InnoDB 不允许一个 index_name 被指定。查看章节 7.5 InnoDB 表。对于其它类型的表,这个句法仅仅为了兼容而提供,以更容易地从其它 SQL 服务器移植代码和更容易地运行以引用创建表的应用程序。查看章节 1.8.4 MySQL 与 ANSI SQL92 相比不同的差别。
这里是一个例子,显示了 ALTER TABLE 的一些用法。我们以一个按如下方式创建一个表 t1 开始:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
为了将表 t1 重命名为 t2:

mysql> ALTER TABLE t1 RENAME t2;
为了将列 a 从 INTEGER 改变为 TINYINT NOT NULL(列名不变),并将列 b 从 CHAR(10) 改变为 CHAR(20) ,同时也将 b 重命名为 c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
添加一个名为 d 的 TIMESTAMP c列:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;
在列 d 上增加一个索引,将列 a 设为主键:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
移除列 c:

mysql> ALTER TABLE t2 DROP COLUMN c;
添加一个名为 c 的 AUTO_INCREMENT 整型列:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);
注意,我们索引了 c,因为 AUTO_INCREMENT 列必须被索引,同样我们声明列 c 为 NOT NULL,因为被索引的列不能有 NULL。

当你添加一个 AUTO_INCREMENT 列时,列值会自动地以序列值填充。通过在 ALTER TABLE 或使用 AUTO_INCREMENT = # 表选项之前执行 SET INSERT_ID=# ,你可以设置第一个序列数字。查看章节 5.5.6 SET 句法。

对于 MyISAM 表,如果你不改变 AUTO_INCREMENT 列,序列值将不会被影响。如果你移除一个AUTO_INCREMENT 列,并添加另一个 AUTO_INCREMENT 列,值将再次从 1 开始。