MySQL 4.1.0 中文參考手冊 --- 6.5 數據定義： CREATE、DROP、ALTER

MySQL 4.1.0 中文參考手冊 --- 犬犬(心帆)翻譯 MySQL Reference Manual for version 4.1.0-alpha.

6.5 數據定義： CREATE、DROP、ALTER6.5.1 CREATE DATABASE 句法


CREATE DATABASE [IF NOT EXISTS] db_name

CREATE DATABASE 以給定名字創建一個數據庫。允許的數據庫名規則在章節 6.1.2 數據庫、表、索引、列和別名 中被給出。 如果數據庫已經存在，并且你沒有指定 IF NOT EXISTS，這時會產生一個錯誤。

在 MySQL 中，數據庫以包含數據庫表對應文件的目錄實現的。因為數據庫在初始創建時沒有表，所以 CREATE DATABASE 語句只在 MySQL 數據目錄下創建一個目錄。

你也可以使用 mysqladmin 創建一個數據庫。查看章節 4.8 MySQL 客戶端腳本和實用程序。
6.5.2 DROP DATABASE 句法


DROP DATABASE [IF EXISTS] db_name

DROP DATABASE 移除數據庫是的所有表并刪除數據庫。如果你在一個符號鏈接(symbolic link)數據庫上執行一個 DROP DATABASE，鏈接與原始數據庫均會被刪除。要非常小心地使用這個命令！

DROP DATABASE 返回從數據庫目錄下刪除的文件數目。通常，它是表的數目的三倍，因為第張表通常對應于一個 “.MYD” 文件、一個 “.MYI” 文件和一個 “.frm” 文件。

DROP DATABASE 命令從給定的數據庫目錄下移除以下列為擴展名的所有文件：
擴展名 擴展名 擴展名 Ext.BAK .DAT .HSH .ISD.ISM .ISM .MRG .MYD.MYI .db .frm
所有包含兩個數字的子目錄(RAID 目錄)也同樣被刪除。

在 MySQL 3.22 或以后的版本中，你可以使用關鍵詞 IF EXISTS 以防止如果數據庫不存在時發生錯誤。

你也可以使用 mysqladmin 移除數據庫。查看章節 4.8 MySQL 客戶端腳本和實用程序。
6.5.3 CREATE TABLE 句法


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)][table_options] [select_statement]orCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] or PRIMARY KEY (index_col_name,...) or KEY [index_name] (index_col_name,...) or INDEX [index_name] (index_col_name,...) or UNIQUE [INDEX] [index_name] (index_col_name,...) or FULLTEXT [INDEX] [index_name] (index_col_name,...) or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] or CHECK (expr)type: TINYINT[(length)] [UNSIGNED] [ZEROFILL] or SMALLINT[(length)] [UNSIGNED] [ZEROFILL] or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] or INT[(length)] [UNSIGNED] [ZEROFILL] or INTEGER[(length)] [UNSIGNED] [ZEROFILL] or BIGINT[(length)] [UNSIGNED] [ZEROFILL] or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] or CHAR(length) [BINARY] or VARCHAR(length) [BINARY] or DATE or TIME or TIMESTAMP or DATETIME or TINYBLOB or BLOB or MEDIUMBLOB or LONGBLOB or TINYTEXT or TEXT or MEDIUMTEXT or LONGTEXT or ENUM(value1,value2,value3,...) or SET(value1,value2,value3,...)index_col_name: col_name [(length)]reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE reference_option]reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULTtable_options: TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }or AUTO_INCREMENT = #or AVG_ROW_LENGTH = #or CHECKSUM = {0 | 1}or COMMENT = "string"or MAX_ROWS = #or MIN_ROWS = #or PACK_KEYS = {0 | 1 | DEFAULT}or PASSWORD = "string"or DELAY_KEY_WRITE = {0 | 1}or ROW_FORMAT= { default | dynamic | fixed | compressed }or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#or UNION = (table_name,[table_name...])or INSERT_METHOD= {NO | FIRST | LAST }or DATA DIRECTORY="absolute path to directory"or INDEX DIRECTORY="absolute path to directory"select_statement: [IGNORE | REPLACE] SELECT ... (Some legal select statement)

CREATE TABLE 以給定的名字在當前數據庫創建一個表。允許的表名規則在章節 6.1.2 數據庫、表、索引、列和別名 中被給出。如果沒有當前數據庫或表已經存在，一個錯誤將會發生。

在 MySQL 3.22 或以后的版本中，表名可以被指定為 db_name.tbl_name。不管有沒有當前數據庫，它也能正常工作。

從 MySQL 3.23 開始，在創建一個表時，你可以使用關鍵詞 TEMPORARY。它的名字被限止在當前連接中，當連接關閉時，臨時表會自動地被刪除。這就意味著，兩個不同的連接可以使用同一個臨時表名而不會與另一個沖突，也不會與同名現有的表相沖突(現有表將被隱藏，只到臨時表被刪除)。從 MySQL 4.0.2 開始，為了能創建臨時表，你必須有 CREATE TEMPORARY TABLES 權限。

在 MySQL 3.23 或以后的版本中，你可以使用關鍵詞 IF NOT EXISTS，因而如果表已存在，錯誤也不會發生。注意，它并不驗證表結構是否一致。

在 MySQL 4.1 中你可以使用 LIKE 來基于一個表定義創建另一個表。to create a table based on a table definition in another table. In MySQL 4.1 中，你同樣也可以為一個被生成的列指定類型：

CREATE TABLE foo (a tinyint not null) SELECT b+1 AS @#a@# FROM bar;

第張表 tbl_name 由數據庫目錄下的一些文件表示。對于 MyISAM 類型的表，你將得到：
文件 用途 tbl_name.frm 表定義 (form) 文件tbl_name.MYD 數據文件tbl_name.MYI 索引文件
對于各種列類型的性質的更多信息，查看章節 6.2 列類型：
如果既沒有指定 NULL 也沒有指定 NOT NULL，列被視為指定了 NULL 。

一個整型列可以有附加屬性 AUTO_INCREMENT。當你插入一個 NULL 值(推薦)或 0 到一個 AUTO_INCREMENT 列，該列將被設置到 value+1，在這里，value 是表中當前列的最大值。AUTO_INCREMENT 序列以 1 開始。查看章節 8.1.3.130 mysql_insert_id()。如果你一個 AUTO_INCREMENT 列中包含最大值的行，對于 ISAM 或 BDB 表，該值會被重新使用，但是對于一個 MyISAM 或 InnoDB 表，卻不會被重用。如果你以 AUTOCOMMIT 模式執行 DELETE FROM table_name (沒有一個 WHERE 子句) 刪除表中的所有記錄行，對于所有的表序列均重新開始。注意：每個表只能有一個 AUTO_INCREMENT 列，并且必須被索引。MySQL 3.23 同樣也只工作于 AUTO_INCREMENT 列只支持正值。插入一個負值將被當作插入一個很大的正值。這是為了避免數字從正到負“包裝”的精度問題，也是為了確保不會意外地得到一個包含 0 的 AUTO_INCREMENT 列。在 MyISAM 和 BDB 表中，你可以指定 AUTO_INCREMENT 多列索引中的第二個列。查看章節 3.5.9 使用 AUTO_INCREMENT.為了使 MySQL 兼容某些 ODBC 應用程序，你可以用下列查詢找出最后被插入的記錄行：
SELECT * FROM tbl_name WHERE auto_col IS NULL
如果 MySQL 二進制日志被使用，CREATE TABLE 將自動地提交當前 InnoDB 事務。

NULL 值對于 TIMESTAMP 列的處理不同于其它的列類型。你不能在一個 TIMESTAMP 列中 存儲一個文字 NULL；將列設置為 NULL 將設置它為當前的日期和時間。因為 TIMESTAMP 列的行為就是這樣，列的 NULL 和 NOT NULL 屬性不以常態方式影響它，如果你指定它們，將被忽略。另一方面，為了使 MySQL 客戶端更容易地使用 TIMESTAMP 列，服務器報告這樣的列被賦值為 NULL 值(這是真的)，即使 TIMESTAMP 實際上決不會包含一個 NULL 值。當你使用 DESCRIBE tbl_name 得到有關你的表的描述時，你就會明白這點。注意，設置一個 TIMESTAMP 列為 0 不等同于設置它為 NULL，因為 0 是一個有效的 TIMESTAMP 值。

DEFAULT 值必須是一個常量，不可以是一個函數或一個表達式。如果一個列沒有指定 DEFAULT 值，MySQL 將自動地賦于一個，規則如下：如果列可以接受 NULL 作為一個值，缺省值為 NULL。如果列被定義為 NOT NULL，缺省值取決于列的類型：對于沒有聲明 AUTO_INCREMENT 屬性的數字類型，缺省值為 0。對于一個 AUTO_INCREMENT 列，缺省值為序列中的下一個值。

對于非 TIMESTAMP 的日期和時間類型，缺省值是該類型適當的零值。對于表中的第一個 TIMESTAMP 列，缺省值為當前的日期和時間。查看章節 6.2.2 Date 和 Time 類型。

對于非 ENUM 的字符串類型，缺省值是空字符串。對于 ENUM，缺省值為第一個枚舉值。

缺省值必須是常量。這意味著，例如，對于一個日期列，你不能將一個像 NOW() 或 CURRENT_DATE 的函數設置為缺省值。

KEY 是 INDEX 的同義詞。

在 MySQL 中，一個 UNIQUE 鍵只能有不同的值。如果你試圖以匹配一個現有行的鍵添加新行，將產生一個錯誤。

PRIMARY KEY 是一個唯一 KEY，它還有一個額外的約束，所有鍵列必須被定義為 NOT NULL。在 MySQL 中，該被命名為 PRIMARY。一張表只能有一個 PRIMARY KEY。如果在你的表中沒有一個 PRIMARY KEY，而某些應用程序要求 PRIMARY KEY，MySQL 將返回第一個沒有任何 NULL 列的 UNIQUE 鍵，做為 PRIMARY KEY。

一個 PRIMARY KEY 可以是一個多列索引。然而，你不能在一個列規格說明中使用 PRIMARY KEY 鍵屬性來創建一個多列索引。這樣做將僅僅標記單個列做為主鍵。你必須使用 PRIMARY KEY(index_col_name, ...) 句法。

如果 PRIMARY 或 UNIQUE 鍵只由一個列組成，并且列類型是整型，你可以用 _rowid 引用它。(在版本 3.23.11 中新加入)。

如果你不為一個索引指派一個名字，索引名將被指派為與第一個 index_col_name 相同的名字，以一個可選后綴 (_2,_3, ...) 使它唯一。使用 SHOW INDEX FROM tbl_name 可以從一個表中查看索引名。查看章節 4.5.6.1 檢索有關數據庫、表、列和索引的信息。

只有 MyISAM、InnoDB 和 BDB 表類型支持在可以有 NULL 值的列上索引。在其它情況下，你必須聲明這個列為 NOT NULL 或者得到一個錯誤結果。

使用 col_name(length) 句法，你可以一個索引只使用一個 CHAR 或 VARCHAR 列的一部分。這可以使索引文件更小一點。查看章節 5.4.4 列索引。

只有 MyISAM 表類型運動在 BLOB 和 TEXT 列上索引。當在一個 BLOB 或 TEXT 列上放置一個索引時，你必須總是指定索引的長度：
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
當你對一個 TEXT 或 BLOB 列使用 ORDER BY 或 GROUP BY 時，只有最前面 的 max_sort_length 字節被使用。查看章節 6.2.3.2 BLOB 和 TEXT 類型。

在 MySQL 3.23.23 或更新的版本中，你也可以創建特殊的 FULLTEXT 索引。他們被用于全文搜索。只有 MyISAM 表類型支持 FULLTEXT 索引。他只能從 CHAR、VARCHAR 和 TEXT 列建立。索引總是建立在整個列上；部分索引是不支持的。詳細操作請查看章節 6.8 MySQL 全文搜索。

在 MySQL 3.23.44 或更新的版本中，InnoDB 表支持外鍵約束檢查。查看章節 7.5 InnoDB 表。注意，InnoDB 中的 FOREIGN KEY 句法比上面介紹的句法有更多的限制。InnoDB 不允許 index_name 被指定，參考表的列總是必須明確命名。從 4.0.8 開始，InnoDB 在外鍵上支持 ON DELETE 和 ON UPDATE 動作。精確句法查看 InnoDB 手冊章節。查看章節 7.5 InnoDB 表。對于其它的表類型，MySQL 服務器對 CREATE TABLE 命令中的 FOREIGN KEY、CHECK 和 REFERENCES 句法作語法分析，但是沒有更進一步的行為。查看章節 1.8.4.5 外鍵。

每個 NULL 列占據額外的一個比特，取舍到最接近的字節。

最大記錄的字節長度可以按下面的計算得出：
row length = 1 + (sum of column lengths) + (number of NULL columns + 7)/8 + (number of variable-length columns)
table_options 和 SELECT 選項只在 MySQL 3.23 和以后的版本中被實現。不同的表類型為：

表類型 含義 BDB 或 BerkeleyDB 以頁鎖定的事務安全型表。查看章節 7.6 BDB 或 BerkeleyDB 表HEAP 這個表的數據只存放在內存中。查看章節 7.4 HEAP 表ISAM 最初的存儲引擎。查看章節 7.3 ISAM 表InnoDB 以行鎖定的事務安全型表。查看章節 7.5 InnoDB 表MERGE 做為一個表使用的 MyISAM 表的收集品。查看章節 7.2 MERGE 表MRG_MyISAM MERGE 表的別名MyISAM 用于代替 ISAM 的新的輕便型二進制存儲引擎。查看章節 7.1 MyISAM 表

查看章節 7 MySQL 表類型。如果一個表類型被指定，而那個特殊類型是不可用的，MySQL 將選擇最接近于你所指定類型的表類型。例如，如果 TYPE=BDB 被指定，而當前版本的 MySQL 不支持 BDB 表，該將會做為 MyISAM 表代替被創建。其它的表選項是用于優化表行為的。在大多數情況下，你不必指定他們中的任何一個。選項對所有表均適用，如果不適用則另外說明：

選項 含義 AUTO_INCREMENT 你想要為你的表設定的下一個 AUTO_INCREMENT 值。(MyISAM)AVG_ROW_LENGTH 你的表的平均行長度的近似值。你只需為有變長記錄的大表設置它CHECKSUM 如果你希望 MySQL 對所有的記錄行維持一個檢驗和(這將使表在更新時變得更慢，但是使得更容易地發現損壞的表)，設置它為 1。(MyISAM)COMMENT 對于你的表的一個 60 個字符的注釋MAX_ROWS 你計劃在表中存儲的最大記錄行數目MIN_ROWS 你計劃在表中存儲的最小記錄行數目PACK_KEYS 如果你希望有更小的索引，設置它為 1。這通常使的更新更加得慢，面讀取列快 (MyISAM, ISAM)。設置它為 0，將禁用所有鍵壓縮。設置它為 DEFAULT (MySQL 4.0)，將告訴存儲引擎僅僅壓縮長的 CHAR/VARCHAR 列PASSWORD 以一個密碼加密 `.frm@# 文件。在 MySQL 標準版中，這個選項不做任何事DELAY_KEY_WRITE 如果希望延遲鍵表更新，直到該表被關閉，設置它為 1。(MyISAM).ROW_FORMAT 定義記錄行如何被存儲。目前，這個選項只能工作于 MyISAM 表，它支持 DYNAMIC 和 FIXED 行格式。查看章節 7.1.2 MyISAM 表格式

當你使用一個 MyISAM 表時，MySQL 使用 max_rows * avg_row_length 的乘積來最終表將有多大。如果你不指定上面的任何選項，一個表的最大民族教育將是 4G (或 2G ，如果你的操作系統僅支持 2G 的最大文件)。原因是僅僅是為了控制指針范圍使索引更小和更快，如果您真的不需要大文件。如果你不使用 PACK_KEYS，缺省僅僅壓縮字符串，不壓縮數字。如果你使用 PACK_KEYS=1，數字也將被很好地壓縮。當壓縮二進制數字鍵時，MySQL 將使用 prefix 壓縮。這就意味著，如果有許多同樣的數字，你才能得到很大的益處。Prefix 壓縮意味著每個鍵都需要一個額外的字節來指示前一個鍵有多少字節與下一個鍵相同(注意，行指針被以高元組第一次序(high-byte-first-order)直接地存儲在鍵后，以改善壓縮)。這就意味著，如果在一個記錄行上有連續兩行的相等鍵，下面所有“相同”的鍵通常只占用 2 個字節(包括記錄行指針)。與通常情況下相比，下面的“相同”鍵將占用 storage_size_for_key + pointer_size (通常 4) 個字節。另一方面，如果所有的鍵都是不同的，你將在每個鍵上損失 1 字節，如果該鍵不是一個可以有 NULL 值的鍵。(在這種情況下，壓縮后鍵的長度將存儲在用于鍵是否為 NULL 的位元組中。)

如果你在一個 CREATE 語句中指定一個 SELECT，MySQL 為 SELECT 中的所有元素創建新的字段。例如：
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> TYPE=MyISAM SELECT b,c FROM test2;
這將創建一個有三個列的 MyISAM 表，a、b 和 c 。請注意，SELECT 語句中的列被添加到表的右邊，而不是重疊在上面?？聪旅娴睦樱?BR>mysql> SELECT * FROM foo;+---+| n |+---+| 1 |+---+mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> SELECT * FROM bar;+------+---+| m | n |+------+---+| NULL | 1 |+------+---+1 row in set (0.00 sec)
對于表 foo 中每個記錄行，一個以從表 foo 來的值和新列的缺省值組成的記錄行被插入到表 bar 中。CREATE TABLE ... SELECT 不會為了自動地創建索引。這是故意這樣做的，是為了該命令盡可能地靈活。如果你希望在創建表時同時創建索引，你必須在 SELECT 語句之前指定它們：
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
如果在拷貝數據到表中時發生任何錯誤，數據將被自動地刪除。為了確保更新日志/二進制日志可用于重建最初的表，在 CREATE TABLE ... SELECT 過程中，MySQL 不允許并發的插入。

在不支持大文件的操作系統上，RAID_TYPE 選項可以幫助你打破 MyISAM 數據文件(非索引文件)的 2G/4G 限止。注意，這個選項在支持大文件的文件系統上不推薦使用！將 RAID 目錄放在不同的物理磁盤上，可以通過 I/O 瓶頸得到更高的速度。RAID_TYPE 可以在任何操作系統上工作，只要你配置 MySQL --with-raid。目前 RAID_TYPE 只允許 STRIPED (1 和 RAID0 是它的別名)。如果你對一個 MyISAM 表指定 RAID_TYPE=STRIPED ，MyISAM 將在數據庫目錄下創建 RAID_CHUNKS 子目錄，并命名為 00, 01, 02 。在每個目錄中，MyISAM 將創建一個 table_name.MYD。當將數據寫入數據文件中時，RAID 處理器將映射第一個 RAID_CHUNKSIZE *1024 字節到第一個文件中，下一個 RAID_CHUNKSIZE *1024 字節到下一個文件中，等等。

UNION 被用于當你希望將多個同樣的表收集為一個時。它僅僅與 MERGE 表一起配合使用。查看章節 7.2 MERGE 表。目前，在你將幾個表映射為一個 MERGE 表時，你需要有對這些表的 SELECT、UPDATE 和DELETE 權限。所有被映射的表必須與 MERGE 表在同一個數據庫中。

如果你希望向一個 MERGE 表中插入數據，你不得不用 INSERT_METHOD 指定記錄行插入到哪一個表中。查看章節 7.2 MERGE 表。這個選項在 MySQL 4.0.0 中被引入。

在創建表時，PRIMARY 鍵必須放在第一位，然后是所有 UNIQUE 鍵，再后是普通鍵。這可以幫助 MySQL 優化程序區分哪個鍵優先使用，同時更快地檢測出重復的 UNIQUE 鍵。

通過使用 DATA DIRECTORY="directory" 或 INDEX DIRECTORY="directory"，你可以指定存儲引擎在什么地方存放它的表和索引文件。注意，目錄必須以一個完整路徑指定(不是相對路徑)。這僅僅工作于 MySQL 4.0 中的 MyISAM 表，并且你沒有使用 --skip-symlink 選項。查看章節 5.6.1.2 對表使用符號鏈接。6.5.3.1 隱式的列定義變化


在某些情況下，MySQL 隱式地改變一個在 CREATE TABLE 給定的列的規約。(這在 ALTER TABLE 中也可能發生。)：
長度不超過四個字節的 VARCHAR 列被改變為 CHAR。

如果在一個表中有任何一個列是變長的，則結果是整個記錄行也是變長的。因此，如果一個表中包含任何變長的列(VARCHAR、TEXT 或 BLOB)，所有長于 3 個字符的 CHAR 列將被改變為 VARCHAR 列。這在任何方面都不影響你如何使用該列；在 MySQL 中，VARCHAR 只是存儲字符的另一個不同的方法。MySQL 執行這個轉換，是因為它節省空間，并且使表操作更快。查看章節 7 MySQL 表類型。

TIMESTAMP 的顯示尺寸必須是在 2 到 14 范圍之內的偶數。如果指定顯示尺寸為 0 或超過 14，尺寸被強制設為 14。從 1 到 13 范圍內的奇數值尺寸將被強制為下一個更大的偶數。

你不能在一個 TIMESTAMP 列中存儲一個文字 NULL；將一個 NULL 值賦給它將設置它為當前的日期和時間。因為 TIMESTAMP 列的行為就是這樣，列的 NULL 和 NOT NULL 屬性不以常態方式影響它，如果你指定它，將被忽略。DESCRIBE tbl_name 總是報告一個 TIMESTAMP 列被賦于了 NULL 值。

MySQL 將其它 SQL 數據庫供應商使用的列類型映射到 MySQL 類型。查看章節 6.2.5 使用來自其它的數據庫引擎的列類型。
如果你希望知道在你創建或改變了你的表后， MySQL 是否使用了不同于你所指定的列類型，你可以發出一個 DESCRIBE tbl_name 語句。

如果你使用 myisampack 壓縮一個表，其它的某些列類型可能會發生改變。查看章節 7.1.2.3 壓縮表的特征。
6.5.4 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 開始。

查看章節 A.6.1 ALTER TABLE 的問題。
6.5.5 RENAME TABLE 句法


RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]

更名是以原子方式(atomically)執行，這就意味著，當更名正在運行時，其它的任何線程均不能該表。這使得以一個空表替換一個表成為可能。

CREATE TABLE new_table (...);RENAME TABLE old_table TO backup_table, new_table TO old_table;

改名是從左到右執行的，這就意味著，如果你希望交換兩個表名，你不得不這樣做：

RENAME TABLE old_table TO backup_table, new_table TO old_table, backup_table TO new_table;

只要兩個數據庫在同一個磁盤上，你也可以從一個數據庫更名到另一個數據庫：

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

當你執行 RENAME 時，你不能有任何鎖定的表或活動的事務。你同樣也必須有對原初表的 ALTER 和 DROP 權限，以及對新表的 CREATE 和 INSERT 權限。

如果在多表更名中，MySQL 遭遇到任何錯誤，它將對所有被更名的表進行倒退更名，將每件事物退回到最初狀態。

RENAME TABLE 在 MySQL 3.23.23 中被加入。
6.5.6 DROP TABLE 句法


DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

DROP TABLE 移除一個或多個表。所有的數據和表定義均被 移除，所以，一定要小心地使用這個命令！

在 MySQL 3.22 或更新的版本中，你可以使用關鍵詞 IF EXISTS 防止表不存在時發生錯誤。在 4.1 中，當使用 IF EXISTS 時，對于所有不存在的表，你將得到一個 NOTE。查看章節 4.5.6.9 SHOW WARNINGS | ERRORS。

RESTRICT and CASCADE 被允許是為了更容易的移植。目前，他們不起任何作用。

注意：DROP TABLE 將自動地提交當前活動的事務(除非你使用的是MySQL 4.1 ，并且使用了 TEMPORARY 關鍵詞)。

選項 TEMPORARY 在 4.0 中被忽略。在 4.1 中，這人選項按如下所示工作：
只移除臨時表。不結束一個運行著的事務。不會被檢查訪問權限。
使用 TEMPORARY 是一個很好的安全方式，它可以防止你意外地移除一個真實的表。
6.5.7 CREATE INDEX 句法




CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )

CREATE INDEX 句法在 MySQL 3.22 以前的版本中不做任何事情。在 3.22 或以后的版本中，CREATE INDEX 被映射到一個 ALTER TABLE 語句來創建索引。查看章節 6.5.4 ALTER TABLE 句法。

通常，在用 CREATE TABLE 創建表本身時你就創建表的所有索引。查看章節 6.5.3 CREATE TABLE 句法。CREATE INDEX 允許你在一個現有表上添加索引。

(col1,col2,...) 格式的列列表創建一個多列索引。索引值由給定的列值連接而成。

對于 CHAR 和 VARCHAR 列，使用 col_name(length) 句法，可以只用一個列的部分來創建索引。(對于 BLOB 和 TEXT 列，長度是必須的。)這里的語句顯示使用 name 列的前 10 個字符創建一個索引：

mysql> CREATE INDEX part_of_name ON customer (name(10));

因為，大多數名字通常在前 10 個字符是不一樣的，這個索引不應該比以整個 name 創建的索引慢。同樣，使用部分列值創建的索引文件會更小一點，這將節省很多磁盤空間，也可以加速 INSERT 操作！

注意，如果你存在使用的是 MySQL 3.23.2 或更新的版本并且是 MyISAM 表類型，這時你才能在一個可以有 NULL 值的列上創建索引，以及在一個 BLOB/TEXT列上創建索引。

關于 MySQL 如何使用索引的更多信息，查看章節 5.4.3 MySQL 如何使用索引。

FULLTEXT 索引只能索引 VARCHAR 和 TEXT 列，而且只能應用于 MyISAM 表。FULLTEXT 索引在 MySQL 3.23.23 和更新的版本中可以使用。查看章節 6.8 MySQL 全文搜索。
6.5.8 DROP INDEX 句法


DROP INDEX index_name ON tbl_name

DROP INDEX 從表 tbl_name 移除一個名為 index_name 的索引。在 MySQL 3.22 先前的版本中不做任何事情。在 3.22 或以后的版本中，DROP INDEX 被映射到一個 ALTER TABLE 語句來移除索引。查看章節 6.5.4 ALTER TABLE 句法。
 

原文轉自：http://www.kjueaiud.com