mysql 命令修改表結構ALTER TABLE 句法

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 開始。