MySQL Reference Manual for version 4.1.0-alpha.


6.5 資料定義: CREATEDROPALTER

6.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]

or

CREATE [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 DEFAULT

table_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 列類型

6.5.3.1 隱式的列定義變化

在某些情況下,MySQL 隱式地改變一個在 CREATE TABLE 給定的列的規約。(這在 ALTER TABLE 中也可能發生。):

如果你希望知道在你建立或改變了你的表後, 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 的一些用法。我們以一個按如下方式建立一個表 t1 開始:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

為了將表 t1 重命名為 t2

mysql> ALTER TABLE t1 RENAME t2;

為了將列 aINTEGER 改變為 TINYINT NOT NULL(列名不變),並將列 bCHAR(10) 改變為 CHAR(20) ,同時也將 b 重命名為 c

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

加入一個名為 dTIMESTAMP 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;

加入一個名為 cAUTO_INCREMENT 整型列:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

注意,我們索引了 c,因為 AUTO_INCREMENT 列必須被索引,同樣我們宣告列 cNOT 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 時,你不能有任何鎖定的表或活動的事務。你同樣也必須有對原初表的 ALTERDROP 權限,以及對新表的 CREATEINSERT 權限。

如果在多表更名中,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,...) 格式的列清單建立一個多列索引。索引值由給定的列值連線而成。

對於 CHARVARCHAR 列,使用 col_name(length) 句法,可以只用一個列的部分來建立索引。(對於 BLOBTEXT 列,長度是必須的。)這裡的語句顯示使用 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 索引只能索引 VARCHARTEXT 列,而且只能應用於 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 句法