萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql 命令修改表結構ALTER TABLE 句法

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

 

copyright © 萬盛學電腦網 all rights reserved