目录
ALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ... alter_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
ALTER DATABASE用于更改数据库的全局特性。这些特性储存在数据库目录中的db.opt文件中。要使用ALTER DATABASE,您需要获得数据库ALTER权限。
CHARACTER SET子句用于更改默认的数据库字符集。COLLATE子句用于更改默认的数据库整序。在第10章:字符集支持中对字符集和整序名称进行了讨论。
数据库名称可以忽略,此时,语句对应于默认数据库。也可以使用ALTER SCHEMA。
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD INDEX [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP INDEX index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | table_options | partition_options | ADD PARTITION partition_definition | DROP PARTITION partition_names | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | ANALYZE PARTITION partition_names | CHECK PARTITION partition_names | OPTIMIZE PARTITION partition_names | REBUILD PARTITION partition_names | REPAIR PARTITION partition_names
ALTER TABLE用于更改原有表的结构。例如,您可以增加或删减列,创建或取消索引,更改原有列的类型,或重新命名列或表。您还可以更改表的评注和表的类型。
允许进行的变更中,许多子句的语法与CREATE TABLE中的子句的语法相近。其中包括table_options修改,选项有ENGINE, AUTO_INCREMENT和AVG_ROW_LENGTH等。请见13.1.5节,“CREATE TABLE语法”。
存储引擎不支持有些操作,如果进行这些操作,会出现警告。使用SHOW WARNINGS可以显示出这些警告。请参见13.5.4.22节,“SHOW WARNINGS语法”。
如果您使用ALTER TABLE更改列规约,但是DESCRIBE tbl_name提示您列规约并没有改变,则可能是因为MySQL忽略了您所做的更改。忽略更改的原因见13.1.5.1节,“沉寂的列规格变更”。例如,如果您试图把VARCHAR列更改为CHAR列,此时,如果表包含其它长度可变的列,则MySQL仍会使用VARCHAR。
ALTER TABLE运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行ALTER TABLE时,其它用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。
注意,如果您在执行ALTER TABLE时使用除了RENAME以外的选项,则MySQL会创建一个临时表。即使数据并不需要进行复制(例如当您更改列的名称时),MySQL也会这么操作。对于MyISAM表,您可以通过把myisam_sort_buffer_size系统变量设置到一个较高的值,来加快重新创建索引(该操作是变更过程中速度最慢的一部分)的速度。
· 要使用ALTER TABLE,您需要获得表的ALTER, INSERT和CREATE权限。
· IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。并且,对错误值进行修正,使之尽量接近正确值。
· 您可以在一个ALTER TABLE语句里写入多个ADD, ALTER, DROP和CHANGE子句,中间用逗号分开。这是MySQL相对于标准SQL的扩展。在标准SQL中,每个ALTER TABLE语句中每个子句只允许使用一次。例如,在一个语句中取消多个列:
· mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
· CHANGE col_name, DROP col_name和DROP INDEX是MySQL相对于标准SQL的扩展。
· MODIFY是Oracle对ALTER TABLE的扩展。
· COLUMN只是自选项目,可以忽略。
· 如果您使用ALTER TABLE tbl_name RENAME TO new_tbl_name并且没有其它选项,则MySQL只对与table tbl_name相对应的文件进行重命名。不需要创建一个临时表。(您也可以使用RENAME TABLE语句对表进行重命名。请参见13.1.9节,“RENAME TABLE语法”。)
· column_definition子句使用与CREATE TABLE中的ADD和CHANGE子句相同的语法。注意,此语法包括列名称,而不只是列类型。请参见13.1.5节,“CREATE TABLE语法”。
· 您可以使用CHANGE old_col_name column_definition子句对列进行重命名。重命名时,需给定旧的和新的列名称和列当前的类型。例如:要把一个INTEGER列的名称从a变更到b,您需要如下操作:
· mysql> ALTER TABLE t1 CHANGE a b INTEGER;
如果您想要更改列的类型而不是名称, CHANGE语法仍然要求旧的和新的列名称,即使旧的和新的列名称是一样的。例如:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
您也可以使用MODIFY来改变列的类型,此时不需要重命名:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
· 如果您使用CHANGE或MODITY缩短列长时,列中存在有索引,并且缩短后的列长小于索引长度,则MySQL会自动缩短索引的长度。
· 当您使用CHANGE或MODIFY更改列的类型时,MySQL会尽量把原有的列值转化为新的类型。
· 您可以使用FIRST或AFTER col_name在一个表行中的某个特定位置添加列。默认把列添加到最后。您也可以在CHANGE或MODIFY语句中使用FIRST和AFTER。
· AFTER COLUMN用于指定列的新默认值,或删除旧的默认值。如果旧的默认值被删除同时列值为NULL,则新的默认值为NULL。如果列值不能为NULL,MySQL会指定一个默认值,请参见13.1.5节,“CREATE TABLE语法”。
· DROP INDEX用于取消索引。这是MySQL相对于标准SQL的扩展。请参见13.1.7节,“DROP INDEX语法”。
· 如果列从表中被取消了,则这些列也从相应的索引中被取消。如果组成一个索引的所有列均被取消,则该索引也被取消。
· 如果一个表只包含一列,则此列不能被取消。如果您想要取消表,应使用DROP TABLE。
· DROP PRIMAY DEY用于取消主索引。注释:在MySQL较早的版本中,如果没有主索引,则DROP PRIMARY KEY会取消表中的第一个UNIQUE索引。在MySQL 5.1中不会出现这种情况。如果在MySQL 5.1中对没有主键的表使用DROP PRIMARY KEY,则会出现错误信息。
如果您向表中添加UNIQUE KEY或PRIMARY KEY,则UNIQUE KEY或PRIMARY KEY会被储存在非唯一索引之前,这样MySQL就可以尽早地检查出重复关键字。
· ORDER BY用于在创建新表时,让各行按一定的顺序排列。注意,在插入和删除后,表不会仍保持此顺序。当您知道多数情况下您会按照特定的顺序查询各行时,可以使用这个选项;在对表进行了大的改动后,通过使用此选项,您可以提高查询效率。在有些情况下,如果表按列排序,对于MySQL来说,排序可能会更简单。
· 如果您对一个MyISAM表使用ALTER TABLE,则所有非唯一索引会被创建到一个单独的批里(和REPAIR TABLE相同)。当您有许多索引时,这样做可以使ALTER TABLE的速度更快。
这项功能可以明确激活。ALTER TABLE...DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。然后使用ALTER TABLE ... ENABLE KEYS重新创建丢失的索引。进行此操作时,MySQL采用一种特殊的算法,比一个接一个地插入关键字要快很多。因此,在进行成批插入操作前先使关键字禁用可以大大地加快速度。使用ALTER TABLE ... DISABLE KEYS除了需要获得以前提到的权限以外,还需要获得INDEX权限。
· Innodb存储引擎支持FOREIGN KEY和REFERENCES子句。Innodb存储引擎执行ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...)。请参见15.2.6.4节,“FOREIGN KEY约束”。对于其它存储引擎,这些子句会被分析,但是会被忽略。对于所有的存储引擎,CHECK子句会被分析,但是会被忽略。请参见13.1.5节,“CREATE TABLE语法”。接受这些子句但又忽略子句的原因是为了提高兼容性,以便更容易地从其它SQL服务器中导入代码,并运行应用程序,创建带参考数据的表。请参见1.8.5节,“MySQL与标准SQL的差别”。
· InnoDB支持使用ALTER TABLE来取消外键:
· ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
要了解更多信息,请参见15.2.6.4节,“FOREIGN KEY约束”。
· ALTER TABLE忽略DATA DIRECTORY和INDEX DIRECTORY表选项。
· 如果您想要把表默认的字符集和所有字符列(CHAR, VARCHAR, TEXT)改为新的字符集,应使用如下语句:
· ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
警告:前面的操作转换了字符集之间的列类型。如果您有一列使用一种字符集(如latin1),但是存储的值实际上使用了其它的字符集(如utf8),这种情况不是您想要的。此时,您必须对这样的列进行以下操作。
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
这种方法能够实现此功能的原因是,当您转换到BLOB列或从BLOB列转换过来时,并没有发生转换。
如果您指定CONVERT TO CHARACTER SET为二进制,则TEXT列被转换到相应的二进制字符串类型(BINARY, VARBINARY, BLOB)。这意味着这些列将不再有字符集,接下来的CONVERT TO操作也将不适用于这些列。
要仅仅改变一个表的默认字符集,应使用此语句:
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
词语DEFAULT为自选项。如果您在向表中添加一个新列时(例如,使用ALTER TABLE...ADD column)没有指定字符集,则此时使用的字符集为默认字符集。
警告:ALTER TABLE...DEFAULT CHARACTER SET和ALTER TABLE...CHARACTER SET是等价的,只用于更改默认的表字符集。
· 如果InnoDB表在创建时,使用了.ibd文件中的自己的表空间,则这样的文件可以被删除和导入。使用此语句删除.ibd文件:
· ALTER TABLE tbl_name DISCARD TABLESPACE;
此语句用于删除当前的.ibd文件,所以应首先确认您有一个备份。如果在表空间被删除后尝试打开表格,则会出现错误。
要把备份的.ibd文件还原到表中,需把此文件复制到数据库目录中,然后书写此语句:
ALTER TABLE tbl_name IMPORT TABLESPACE;
· 使用mysql_info() C API函数,您可以了解有多少记录已被复制,以及(当使用IGNORE时)有多少记录由于重复关键字的原因已被删除。请参见25.2.3.34节,“mysql_info()”。
· ALTER TABLE也可以用于对带分区的表进行重新分区,功能包括添加、取消、合并和拆分各分区,还可以用于进行分区维护。
对带分区的表使用partition_options子句和ALTER TABLE可以对表进行重新分区,使用时依据partition_options定义的分区方法。本子句以PARTITION BY为开头,然后使用与用于CREATE TABLE的partition_options子句一样的语法和规则(要了解详细信息,请参见13.1.5节,“CREATE TABLE语法”)。注释:MySQL 5.1服务器目前接受此语法,但是不实际执行;等MySQL 5.1开发出来后,将执行此语法。
用于ALTER TABLE ADD PARTITION的partition_definition子句支持用于CREATE TABLE语句的partition_definition子句的同样名称的选项。(要了解语法和介绍,请参见13.1.5节,“CREATE TABLE语法”。)例如,假设您有一个按照以下方式创建的带分区的表:
CREATE TABLE t1 (
id INT,
year_col INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999)
);
您可以在表中增加一个新的分区p3,该分区用于储存小于2002的值。添加方法如下:
ALTER TABLE t1 ADD PARTITION p3 VALUES LESS THAN (2002);
注释:您不能使用ALTER TABLE向一个没有进行分区的表添加分区。
DROP PARTITION用于取消一个或多个RANGE或LIST分区。此命令不能用于HASH或KEY 分区;用于这两个分区时,应使用COALESCE PARTITION(见后)。如果被取消的分区其名称列于partition_names清单中,则储存在此分区中的数据也被取消。例如,如果以前已定义的表t1,您可以采用如下方法取消名称为p0和p1的分区:
ALTER TABLE DROP PARTITION p0, p1;
ADD PARTITION和DROP PARTITION目前不支持IF [NOT] EXISTS。也不可能对一个分区或一个已分区的表进行重命名。如果您希望对一个分区进行重命名,您必须取消分区,再重新建立;如果您希望对一个已分区的表进行重新命名,您必须取消所有分区,然后对表进行重命名,再添加被取消的分区。
COALESCE PARTITION可以用于使用HASH或KEY进行分区的表,以便使用number来减少分区的数目。例如,假设您使用下列方法创建了表t2:
CREATE TABLE t2 (
name VARCHAR (30),
started DATE
)
PARTITION BY HASH(YEAR(started))
PARTITIONS (6);
您可以使用以下命令,把t2使用的分区的数目由6个减少到4个:
ALTER TABLE t2 COALESCE PARTITION 2;
包含在最后一个number分区中的数据将被合并到其余的分区中。在此情况下,分区4和分区5将被合并到前4个分区中(编号为0、1、2和3的分区)。
如果要更改部分分区,但不更改所有的分区,您可以使用REORGANIZE PARTITION。这个命令有多种使用方法:
o 把多个分区合并为一个分区。通过把多个分区的名称列入partition_names清单,并为partition_definition提供一个单一的定义,可以实现这个功能。
o 把一个原有的分区拆分为多个分区。通过为partition_names命名一个分区,并提供多个partition_definitions,可以实现这个功能。
o 更改使用VALUES LESS THAN定义的分区子集的范围或更改使用VALUES IN定义的分区子集的值清单。
注释:对于没有明确命名的分区,MySQL会自动提供默认名称p0, p1, p2等。
要了解有关ALTER TALBE...REORANIZE PARTITION命令的详细信息,请参见18.3节,“分区管理”。
· 多个附加子句用于提供分区维护和修补功能。这些功能与用于非分区表的功能类似。这些功能由CHECK TABLE和REPAIR TABLE等命令(这些命令不支持用于分区表)执行。这些子句包括ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION和REPAIR PARTITION.每个选项均为一个partition_names子句,包括一个或多个分区名称。需要更改的表中必须已存在这些分区。多个分区名称用逗号分隔。要了解更多信息,或要了解举例说明,请参见18.3.3节,“分区维护”。
以下例子展示了ALTER TABLE的使用。首先展示表t1。表t1采用如下方法创建:
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
把表t1重新命名为t2:
mysql> ALTER TABLE t1 RENAME t2;
把列a从INTERGER更改为TINYINT NOT NULL(名称保持不变),并把列b从CHAR(10)更改为CHAR(20),同时把列b重新命名为列c:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
添加一个新的TIMESTAMP列,名称为d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
在列d和列a中添加索引:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
删除列c:
mysql> ALTER TABLE t2 DROP COLUMN c;
添加一个新的AUTO_INCREMENT整数列,名称为c:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> ADD PRIMARY KEY (c);
注意我们为c编制了索引(作为PRIMARY KEY),因为AUTO_INCREMENT列必须编制索引。同时我们定义c为NOT NULL,因为主键列不能为NULL。
当您添加一个AUTO_INCREMENT列时,列值被自动地按序号填入。对于MyISAM表,您可以在ALTER TABLE之前执行SET INSERT_ID=value来设置第一个序号,也可以使用AUTO_INCREMENT=value表选项来设置。请参见13.5.3节,“SET语法”。
如果值大于AUTO_INCREMENT列中的最大值,则您可以使用用于InnoDB表的ALTER TALBE...AUTO_INCREMENT=value表选项,来为新行设置序号。如果值小于列中当前的最大值,不会出现错误信息,当前的序列值也不改变。
使用MyISAM表时,如果您不更改AUTO_INCREMENT列,则序列号不受影响。如果您取消一个AUTO_INCREMENT列,然后添加另一个AUTO_INCREMENT列,则序号重新排列,从1开始。
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
CREATE DATABASE用于创建数据库,并进行命名。如果要使用CREATE DATABASE,您需要获得数据库CREATE权限。
有关合法数据库名称的规定列于9.2节,“数据库、表、索引、列和别名”。如果存在数据库,并且您没有指定IF NOT EXISTS,则会出现错误。
create_specification选项用于指定数据库的特性。数据库特性储存在数据库目录中的db.opt文件中。CHARACTER SET子句用于指定默认的数据库字符集。COLLATE子句用于指定默认的数据库整序。字符集和整序名称在第10章:字符集支持中讨论。
有些目录包含文件,这些文件与数据库中的表对应。MySQL中的数据库的执行方法与这些目录的执行方法相同。因为当数据库刚刚被创建时,在数据库中没有表,所以CREATE DATABASE只创建一个目录。这个目录位于MySQL数据目录和db.opt文件之下。
如果您手动在数据目录之下创建一个目录(例如,使用mkdir),则服务器会认为这是一个数据库目录,并在SHOW DATABASES的输出中显示出来。
也可以使用CREATE SCHEMA。
您还可以使用mysqladmin程序创建数据库。请参见8.5节,“mysqladmin:用于管理MySQL服务器的客户端”。
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
CREATE INDEX被映射到一个ALTER TABLE语句上,用于创建索引。请参见13.1.2节,“ALTER TABLE语法”。
通常,当使用CREATE TABLE创建表时,也同时在表中创建了所有的索引。请参见13.1.5节,“CREATE TABLE语法”。CREATE INDEX允许您向已有的表中添加索引。
格式为(col1, col2,...)的一个列清单创建出一个多列索引。通过串接给定列中的值,确定索引值的格式。
对于CHAR和VARCHAR列,只用一列的一部分就可创建索引。创建索引时,使用col_name(length)语法,对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引,但是必须给出前缀长度。
此处展示的语句用于创建一个索引,索引使用列名称的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));
因为多数名称的前10个字符通常不同,所以此索引不会比使用列的全名创建的索引速度慢很多。另外,使用列的一部分创建索引可以使索引文件大大减小,从而节省了大量的磁盘空间,有可能提高INSERT操作的速度。
前缀最长为255字节。对于MyISAM和InnoDB表,前缀最长为1000字节。注意前缀的限长以字节计,而CREATE INDEX语句中的前缀长度指的是字符的数目。对于使用多字节字符集的列,在指定列的前缀长度时,要考虑这一点。
在MySQL 5.1中:
· 只有当您正在使用MyISAM, InnoDB或BDB表类型时,您可以向有NULL值的列中添加索引。
· 只有当您正在使用MyISAM, BDB或InnoDB表类型时,您可以向BLOB或TEXT列中添加索引。
一个index_col_name规约可以以ASC或DESC为结尾。这些关键词将来可以扩展,用于指定递增或递减索引值存储。目前,这些关键词被分析,但是被忽略;索引值均以递增顺序存储。
部分储存引擎允许在创建索引时指定索引类型。index_type指定语句的语法是USING type_name。不同的储存引擎所支持的type_name值已显示在下表中。如果列有多个索引类型,当没有指定index_type时,第一个类型是默认值。
存储引擎 |
允许的索引类型 |
MyISAM |
BTREE |
InnoDB |
BTREE |
MEMORY/HEAP |
HASH, BTREE |
示例:
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);
TYPE type_name可以作为USING type_name的同义词,用于指定索引类型。但是,USING是首选的格式。另外,在索引规约语法中,位于索引类型前面的索引名称不能使用TYPE。这是因为,与USING不同,TYPE不是保留词,因此会被认为是一个索引名称。
如果您指定的索引类型在给定的储存引擎中不合法,但是有其它的索引类型适合引擎使用,并且不会影响查询功能,则引擎应使用此类型。
要了解更多有关MySQL如何使用索引的信息,请参见7.4.5节,“MySQL如何使用索引”。
FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引,并且只能在MyISAM表中编制。请参见12.7节,“全文搜索功能”。
SPATIAL索引只能对空间列编制索引,并且只能在MyISAM表中编制。空间列类型在第19章:MySQL中的空间扩展中进行了描述。
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
或:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(] LIKE old_tbl_name [)];
create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr)
column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
| NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| CHAR(length) [BINARY | ASCII | UNICODE]
| VARCHAR(length) [BINARY]
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]
| LONGTEXT [BINARY]
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type
index_col_name:
col_name [(length)] [ASC | DESC]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
table_options: table_option [table_option] ...
table_option:
{ENGINE|TYPE} = engine_name
| AUTO_INCREMENT = value
| AVG_ROW_LENGTH = value
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| CHECKSUM = {0 | 1}
| COMMENT = 'string'
| CONNECTION = 'connect_string'
| MAX_ROWS = value
| MIN_ROWS = value
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = 'string'
| DELAY_KEY_WRITE = {0 | 1}
| ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| UNION = (tbl_name[,tbl_name]...)
| INSERT_METHOD = { NO | FIRST | LAST }
| DATA DIRECTORY = 'absolute path to directory'
| INDEX DIRECTORY = 'absolute path to directory'
partition_options:
PARTITION BY
[LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE(expr)
| LIST(column_list)
[PARTITIONS num]
[ SUBPARTITION BY
[LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
[SUBPARTITIONS(num)]
]
[(partition_definition), [(partition_definition)], ...]
partition_definition:
PARTITION partition_name
[VALUES {
LESS THAN (expr) | MAXVALUE
| IN (value_list) }]
[[STORAGE] ENGINE [=] engine-name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] (tablespace_name)]
[NODEGROUP [=] node_group_id]
[(subpartition_definition), [(subpartition_definition)], ...]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine-name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] (tablespace_name)]
[NODEGROUP [=] node_group_id]
select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
CREATE TABLE用于创建带给定名称的表。您必须拥有表CREATE权限。
允许的表名称的规则列于9.2节,“数据库、表、索引、列和别名”中。默认的情况是,表被创建到当前的数据库中。如果表已存在,或者如果没有当前数据库,或者如果数据库不存在,则会出现错误。
表名称被指定为db_name.tbl_name,以便在特定的数据库中创建表。不论是否有当前数据库,都可以通过这种方式创建表。如果您使用加引号的识别名,则应对数据库和表名称分别加引号。例如,`mydb`.`mytbl`是合法的,但是`mydb.mytbl`不合法。
在创建表格时,您可以使用TEMPORARY关键词。只有在当前连接情况下,TEMPORARY表才是可见的。当连接关闭时,TEMPORARY表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称,同时两个临时表不会互相冲突,也不与原有的同名的非临时表冲突。(原有的表被隐藏,直到临时表被取消时为止。)您必须拥有CREATE TEMPORARY TABLES权限,才能创建临时表。
如果表已存在,则使用关键词IF NOT EXISTS可以防止发生错误。注意,原有表的结构与CREATE TABLE语句中表示的表的结构是否相同,这一点没有验证。注释:如果您在CREATE TABLE...SELECT语句中使用IF NOT EXISTS,则不论表是否已存在,由SELECT部分选择的记录都会被插入。
MySQL通过数据库目录中的.frm表格式(定义)文件表示每个表。表的存储引擎也可能会创建其它文件。对于MyISAM表,存储引擎可以创建数据和索引文件。因此,对于每个MyISAM表tbl_name,有三个磁盘文件:
文件 |
作用 |
tbl_name.frm |
表格式(定义)文件 |
tbl_name.MYD |
数据文件 |
tbl_name.MYI |
索引文件 |
用于表示表的由存储引擎创建的文件在第15章:存储引擎和表类型中描述。
要了解有关各种列类型的性质的一般说明,请参见第11章:列类型。要了解有关空间列类型的说明,请参见第19章:MySQL中的空间扩展。
· 如果没有指定是NULL或是NOT NULL,则列在创建时假定指定为NULL。
· 一个整数列可以拥有一个附加属性AUTO_INCREMENT。当您向一个已编入索引的AUTO_INCREMENT列中插入一个NULL值(建议)或0时,此列被设置为下一个序列的值。通常情况下为value+1,此处value是当前在表中的列的最大值。AUTO_INCREMENT序列从1开始。这样的列必须被定义为一种整数类型,请参见11.1.1节,“数值类型概述”中的叙述。(值1.0不是整数)。请参见25.2.3.36节,“mysql_insert_id()”。
为--sql-mode服务器选项或sql_mode系统变量指定NO_AUTO_VALUE_ON_ZERO特征位,这样可以把0存储到AUTO_INCREMENT列中,同时不生成一个新的序列值。请参见5.3.1节,“mysqld命令行选项”。
注释:有时候,每个表只有一个AUTO_INCREMENT列,此列必须编制索引,不能有DEFAULT值。一个AUTO_INCREMENT列只有在只包含正数的情况下,才能运行正常。插入一个负数会被认为是插入了一个非常大的正数。这样做是为了避免当数字由正数转为负数时出现精度问题,同时也为了确保AUTO_INCREMENT列中不会包含0。
对于MyISAM和BDB表,您可以在一个多列关键字中指定一个AUTO_INCREMENT次级列。请参见3.6.9节,“使用AUTO_INCREMENT”。
为了让MySQL与部分ODBC应用软件相兼容,您可以使用以下查询方法找到最后一个插入行的AUTO_INCREMENT值:
SELECT * FROM tbl_name WHERE auto_col IS NULL
· 字符列的定义可以包括一个CHARACTER SET属性,用来指定字符集,也可以指定列的整序。要了解详细情况,请参见第10章:字符集支持。CHARSET是CHARACTER SET的同义词。
· CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
MySQL 5.1理解,在字符列定义中的长度规约以字符为单位。(有些早期版本以字节为单位。)
· DEFAULT子句用于为列指定一个默认值。默认值必须为一个常数,不能为一个函数或一个表达式,有一种情况例外。例如,一个日期列的默认值不能被设置为一个函数,如NOW()或CURRENT_DATE。不过,有一种例外,您可以对TIMESTAMP列指定CURRENT_TIMESTAMP为默认值。请参见11.3.1.1节,“MySQL 4.1中的TIMESTAMP属性”。
BLOB和TEXT列不能被赋予默认值。
如果在列定义中没有明确的DEFAULT值,则MySQL按照如下规则确定默认值:
如果列可以使用NULL作为值,则使用DEFAULT NULL子句对列进行定义。(在MySQL的早期版本中也如此。)
如果列不能使用NULL作为值,则MySQL对列进行定义时不使用DEFAULT子句。输入数据时,如果INSERT或REPLACE语句不包括列的值,则MySQL依据当时的有效的SQL模式操作列:
o 如果严格模式没有被启用,则MySQL会根据列数据类型,把列设置为明确的默认值。
o 如果严格模式已被启用,则事务表会出现错误,语句被回滚。对于非事务表,会出现错误,不过,如果错误出现在一个多行语句中的第二行或后续行,则以前的各行将被插入。
假设表t按下面的方法进行定义:
CREATE TABLE t (i INT NOT NULL);
在这种情况下,i没有明确的默认值,所以在严格模式中,每个后续语句都会产生一个错误,并且没有行被插入。当未使用严格模式时,只有第三个语句产生错误;明确的默认值被插入到前两个语句中,但是第三个语句会出现错误,因为DEFAULT(i)不会产生一个值:
INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));
对于一个给定的表,您可以使用SHOW CREATE TABLE语句来查看那些列有明确的DEFAULT子句。
· 对于列的评注可以使用COMMENT选项来进行指定。评注通过SHOW CREATE TABLE和SHOW FULL COLUMNS语句显示。
· 属性SERIAL可以用作BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名。
· KEY通常是INDEX同义词。如果关键字属性PRIMARY KEY在列定义中已给定,则PRIMARY KEY也可以只指定为KEY。这么做的目的是与其它数据库系统兼容。
· 在UNIQUE索引中,所有的值必须互不相同。如果您在添加新行时使用的关键字与原有行的关键字相同,则会出现错误。例外情况是,如果索引中的一个列允许包含NULL值,则此列可以包含多个NULL值。此例外情况不适用于BDB表。在BDB中,带索引的列只允许一个单一NULL。
· PRIMARY KEY是一个唯一KEY,此时,所有的关键字列必须定义为NOT NULL。如果这些列没有被明确地定义为NOT NULL,MySQL应隐含地定义这些列。一个表只有一个PRIMARY KEY。如果您没有PRIMARY KEY并且一个应用程序要求在表中使用PRIMARY KEY,则MySQL返回第一个UNIQUE索引,此索引没有作为PRIMARY KEY的NULL列。
· 在已创建的表中,PRIMARY KEY的位置最靠前,然后是所有的UNIQUE索引,然后是非唯一索引。这可以帮助MySQL优化程序选择优先使用哪个索引,并且更快速的检测出重复的UNIQUE关键字。
· PRIMARY KEY可以是一个多列索引。但是,在列规约中使用PRIMARY KEY关键字属性无法创建多列索引。这么做只能把一个列标记为主列。您必须使用一个单独的PRIMARY KEY(index_col_name, ...)子句。
· 如果PRIMARY KEY或UNIQUE索引只包括一个列,并且此列为整数类型,则您也可以在SELECT语句中把此列作为_rowid引用。
· 在MySQL中,PRIMARY KEY的名称为PRIMARY。对于其它索引,如果您没有赋予名称,则索引被赋予的名称与第一个已编入索引的列的名称相同,并自选添加后缀(_2, _3,...),使名称为唯一名称。您可以使用SHOW INDEX FROM tbl_name来查看表的索引名称。请参见13.5.4.11节,“SHOW INDEX语法”。
· 部分存储引擎允许您在创建索引时指定索引类型。index_type指示语句的语法是USING type_name。
示例:
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
要了解有关USING的详细说明,请参见13.1.4节,“CREATE INDEX语法”。
要了解有关MySQL如何使用索引的更多信息,请参见7.4.5节,“MySQL如何使用索引”。
· 在MySQL 5.1中,只有MyISAM,InnoDB, BDB和MEMORY存储引擎支持在含有NULL值的列中编索引。在其它情况下,您必须定义已编索引的列为NOT NULL,否则会出现错误。
· 在一个索引规约中使用col_name(length)语法,您可以创建一个索引,此索引只使用一个CHAR或VARCHAR列的第一个length字符。只对列值的前缀编制索引可以使索引文件大大减小。请参见7.4.3节,“列索引”。
MyISAM和InnoDB存储引擎也支持对BLOB和TEXT列编索引。当对BLOB或TEXT列编索引时,您必须为索引指定一个前缀长度。例如:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
对于MyISAM和InnoDB表,前缀最长可以为1000字节,对于其它表格类型,最长可以为255字节。注意前缀长度限值以字节为单位,而在CREATE TABLE语句中的前缀长度用字符数目来表述。当为一个使用多字节字符集的列指定前缀长度时,一定要考虑到这一点。
· 一个index_col_name规约可以以ASC或DESC结尾。这些关键词可以在将来进行扩展,用于指定升序或降序的索引值存储。当前,这些关键词被分析但是被忽略;索引值均以升序储存。
·
当您在SELECT中的TEXT列或BLOB列中使用ORDER
BY或GROUP BY时,服务器只使用初始的字节数目对值进行分类。字节数目由max_sort_length系统变量进行指示。请参见11.4.3节,“BLOB和TEXT类型”。
· 您可以创建特殊的FULLTEXT索引,用于全文搜索。只有MyISAM表类型支持FULLTEXT索引。FULLTEXT索引只可以从CHAR, VARCHAR和TEXT列中创建。整个列都会被编入索引;不支持对部分列编索引。如果已指定,前缀长度会被忽略。要了解运行的详细说明,请参见12.7节,“全文搜索功能”。
· 您可以为空间列类型创建SPATIAL索引。只有MyISAM表支持空间类型,已编索引的列必须声明为NOT NULL。请参见第19章:MySQL中的空间扩展。
· InnoDB表支持对外键限制条件进行检查。请参见15.2节,“InnoDB存储引擎”。注意,在InnoDB中,FOREIGN KEY语法比本节开始时介绍的CREATE TABLE语句的语法更严格:被引用的表中的列必须有明确的命名。InnoDB支持外键的ON DELETE和ON UPDATE两种操作。有关精确语法的说明,请参见15.2.6.4节,“FOREIGN KEY约束”。
对于其它存储引擎,MySQL服务器对CREATE TABLE语句中的FOREIGN KEY和REFERENCES语法进行分析,但不采取进一步的行动。所有的存储引擎均对CHECK子句进行分析,但是忽略CHECK子句。请参见1.8.5.5节,“外键”。
· 对于MyISAM表,每个NULL列要多占用一位,进位到距离最近的字节。最大记录长度(以字节为单位)按照如下方法计算:
· row length = 1
· + (sum of column lengths)
· + (number of NULL columns + delete_flag + 7)/8
· + (number of variable-length columns)
对于采用静态记录格式的表,delete_flag为1。静态表在行记录中使用一位用作位标记。位标记指示该行是否已被删除。对于动态表,delete_flag为0,因为在动态行标题中已存储了位标记。
这些计算方法不适用于InnoDB表。对于InnoDB表,NULL列的存储量与NOT NULL列的存储量没有区别。
ENGINE和TYPE选项用于为表指定存储引擎。ENGINE是首选的选项名称。
ENGINE和TYPE选项采用以下值:
存储引擎 |
说明 |
ARCHIVE |
档案存储引擎。请参见15.8节,“ARCHIVE存储引擎”。 |
BDB |
带页面锁定的事务安全表。也称为BerkeleyDB。请参见15.5节,“BDB (BerkeleyDB)存储引擎”。 |
CSV |
值之间用逗号隔开的表。请参见15.9节,“CSV存储引擎。 |
EXAMPLE |
示例引擎。请参见15.6节,“EXAMPLE存储引擎”。 |
FEDERATED |
可以访问远程表的存储引擎。请参见15.7节,“FEDERATED存储引擎”。 |
HEAP |
|
(OBSOLETE) ISAM |
在MySQL 5.1中没有此引擎。如果您要从以前的版本升级到MySQL 5.1,您应该在进行升级前把原有的ISAM表转换为MyISAM表。请参见第15章:存储引擎和表类型。 |
InnoDB |
带行锁定和外键的事务安全表。请参见15.2节,“InnoDB存储引擎”。 |
MEMORY |
本表类型的数据只保存在存储器里。(在早期MySQL版本中被称为HEAP。) |
MERGE |
MyISAM表的集合,作为一个表使用。也称为MRG_MyISAM。请参见15.3节,“MERGE存储引擎”。 |
MyISAM |
二进制轻便式存储引擎,此引擎是MySQL所用的默认存储引擎。请参见15.1节,“MyISAM存储引擎”。 |
NDBCLUSTER |
要了解有关MySQL存储引擎的更多信息,请参见第15章:存储引擎和表类型。
如果被指定的存储引擎无法利用,则MySQL使用MyISAM代替。例如,一个表定义包括ENGINE=BDB选项,但是MySQL服务器不支持BDB表,则表被创建为MyISAM表。这样,如果您在主机上有事务表,但在从属机上创建的是非交互式表(以加快速度)时,可以进行复制设置。在MySQL 5.1中,如果没有遵守存储引擎规约,则会出现警告。
其它表选项用于优化表的性质。在多数情况下,您不必指定表选项。这些选项适用于所有存储引擎,另有说明除外:
· AUTO_INCREMENT
表的初始AUTO_INCREMENT值。在MySQL 5.1中,本选项只适用于MyISAM和MEMORY表。InnoDB也支持本选项。如果引擎不支持AUTO_INCREMENT表选项,则要设置引擎的第一个auto-increment值,需插入一个“假”行。该行的值比创建表后的值小一,然后删除该假行。
对于在CREATE TABLE语句中支持AUTO_INCREMENT表选项的引擎,您也可以使用ALTER TABLE tbl_name AUTO_INCREMENT = n来重新设置AUTO_INCREMENT值。
· AVG_ROW_LENGTH
表中平均行长度的近似值。只需要对含尺寸可变的记录的大型表进行此项设置。
当创建一个MyISAM表时,MySQL使用MAX_ROWS和AVG_ROW_LENGTH选项的乘积来确定得出的表有多大。如果有一个选项未指定,则表的最大尺寸为65,536TB数据。(如果操作系统不支持这么大的文件,则表的尺寸被限定在操作系统的限值处。)如果您想缩小指针尺寸使索引更小,速度更快,并且您不需要大文件,则您可以通过设置myisam_data_pointer_size系统变量来减少默认指针的尺寸。(见5.3.3节,“服务器系统变量”。)如果您希望所有的表可以扩大,超过默认限值,并且愿意让表稍微慢点,并稍微大点,则您可以通过设置此变量增加默认指针的尺寸。
· [DEFAULT] CHARACTER SET
用于为表指定一个默认字符集。CHARSET是CHARACTER SET的同义词。
对于CHARACTER SET.
· COLLATE
用于为表指定一个默认整序。
· CHECKSUM
如果您希望MySQL随时对所有行进行实时检验求和(也就是,表变更后,MySQL自动更新检验求和),则应把此项设