Mysql ALTER TABLE 句法
时间：2009-01-02 来源：militala
Mysql ALTER TABLE 句法
6.5.4 ALTER TABLE 句法
添加唯一
ALTER table justmusic add unique (musicsource)
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 开始。
添加唯一
ALTER table justmusic add unique (musicsource)
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 开始。
相关阅读 更多 +
