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;

见15.2.6.6节，“使用按表的表空间”。

·         使用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开始。

见A.7.1节，“与ALTER TABLE有关的问题”。

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时，第一个类型是默认值。

示例：

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，有三个磁盘文件：

用于表示表的由存储引擎创建的文件在第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));

见5.3.2节，“SQL服务器模式”。

对于一个给定的表，您可以使用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选项采用以下值：

要了解有关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自动更新检验求和），则应把此项设置为1。这样做，表的更新速度会略微慢些，但是更容易寻找到受损的表。CHECKSUM TABLE语句用于报告检验求和（仅限于MyISAM）。

·         COMMENT

表的注释，最长60个字符。

·         CONNECTION

FEDERATED表的连接字符串。（ 注释：较早版本的MySQL使用COMMENT选项用于连接字符串。

·         MAX_ROWS

您打算储存在表中的行数目的最大值。这不是一个硬性限值，而更像一个指示语句，指示出表必须能存储至少这么多行。

·         MIN_ROWS

您打算存储在表中的行数目的最小值。

·         PACK_KEYS

如果您希望索引更小，则把此选项设置为1。这样做通常使更新速度变慢，同时阅读速度加快。把选项设置为0可以取消所有的关键字压缩。把此选项设置为DEFAULT时，存储引擎只压缩长的CHAR或VARCHAR列（仅限于MyISAM）。

如果您不使用PACK_KEYS，则默认操作是只压缩字符串，但不压缩数字。如果您使用PACK_KEYS=1，则对数字也进行压缩。

在对二进制数字关键字进行压缩时，MySQL采用前缀压缩：

o        每个关键字需要一个额外的字节来指示前一个关键字中有多少字节与下一个关键字相同。

o        指向行的指针以高位字节优先的顺序存储在关键字的后面，用于改进压缩效果。

这意味着，如果两个连续行中有许多相同的关键字，则后续的“相同”的关键字通常只占用两个字节（包括指向行的指针）。与此相比，常规情况下，后续的关键字占用storage_size_for_key + pointer_size（指针尺寸通常为4）。但是，只有在许多数字相同的情况下，前缀压缩才有好处。如果所有的关键字完全不同，并且关键字不能含有NULL值，则每个关键字要多使用一个字节。（在这种情况中，储存压缩后的关键字的长度的字节与用于标记关键字是否为NULL的字节是同一字节。）

·         PASSWORD

使用密码对.frm文件加密。在标准MySQL版本中，本选项不起任何作用。

·         DELAY_KEY_WRITE

如果您想要延迟对关键字的更新，等到表关闭后再更新，则把此项设置为1（仅限于MyISAM）。

·         ROW_FORMAT

定义各行应如何储存。当前，此选项只适用于MyISAM表。对于静态行或长度可变行，此选项值可以为FIXED或DYNAMIC。myisampack用于把类型设置为COMPRESSED。请参见15.1.3节，“MyISAM表的存储格式”。

在默认情况下，InnoDB记录以压缩格式存储（ROW_FORMAT=COMPACT）。通过指定ROW_FORMAT=REDUNDANT，仍然可以申请用于较早版本的MySQL中的非压缩格式。

·         RAID_TYPE

在MySQL 5.0中,RAID支持被删除了。要了解有关RAID的说明，请参见http://dev.mysql.com/doc/refman/4.1/en/create-table.html。

·         UNION

当您想要把一组相同的表当作一个表使用时，采用UNION。UNION仅适用于MERGE表。请参见15.3节，“MERGE存储引擎”。

对于您映射到一个MERGE表上的表，您必须拥有SELECT, UPDATE和DELETE权限。（注释：以前，所有被使用的表必须位于同一个数据库中，并作为MERGE表。这些限制不再适用。）

·         INSERT_METHOD

如果您希望在MERGE表中插入数据，您必须用INSERT_METHOD指定应插入行的表。INSERT_METHOD选项仅用于MERGE表。使用FIRST或LAST把行插入到第一个或最后一个表中；或者使用NO，阻止插入行。请参见15.3节，“MERGE存储引擎”。

·         DATA DIRECTORY, INDEX DIRECTORY

通过使用DATA DIRECTORY='directory'或INDEX DIRECTORY='directory'，您可以指定MyISAM存储引擎放置表格数据文件和索引文件的位置。注意，目录应是通向目录的完整路径（不是相对路径）。

仅当您没有使用--skip-symbolic-links选项时，DATA DIRECTORY, INDEX DIRECTORY才能使用。操作系统必须有一个正在工作的、线程安全的realpath()调用。要了解全面信息，请参见7.6.1.2节，“在Unix平台上使用表的符号链接”。

·         对于用CREATE TABLE创建的表，可以使用partition_options控制分区。如果使用了partition_options，则其中必须包含至少一个PARTITION BY子句。本子句包含用于确定分区的函数；该函数会返回一个整值，范围从1到num。此处num为分区的数目。此函数中可以使用的选项显示在下面的清单中。 要点：在本节开始时介绍的用于partition_options的语法中显示的选项，并不是都能用于所有分区类型。要了解各种类型具体的信息 ，请参见以下各类型的清单。要了解有关在MySQL中的分区的操作和使用情况的全面说明，以及要了解表创建的示例和与MySQL分区有关的其它命令，请参见第18章：分区。

o        HASH（expr）：用于混编一个或多个列，创建一个关键字，用于放置行，并确定行的位置。expr是一个表达式，使用一个或多个表中的列。该表达式可以是任何能够生成单一整值的合法的MySQL表达式（包括MySQL函数）。例如，这些都是有效的CREATE TABLE语句，语句中使用了PARTITION BY HASH：

o                     CREATE TABLE t1 (col1 INT, col2 CHAR(5)) 

o                         PARTITION BY HASH(col1);

o                      

o                     CREATE TABLE t1 (col1 INT, col2 CHAR(5))

o                         PARTITION BY HASH( ORD(col2) );

o                      

o                     CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)

o                         PARTITION BY HASH ( YEAR(col3) );

VALUES LESS THAN或VALUES IN子句不能和PARTITION BY HASH一起使用。

PARTITION BY HASH使用expr被分区数目所除后的余数（也就是模数）。要了解示例和其它信息，请参见18.2.3节，“HASH分区”。

LENEAR关键词需要一种不同的算法。在这种情况下，通过一次或多次逻辑AND运算得出的结果，计算出存储记录的分区的数目。要了解线形混编的讨论和示例，请参见18.2.3.1节，“LINEAR HASH分区”。

o        KEY(column_list)：与HASH近似，除了有一点不一样，即MySQL提供了混编函数，以保证均匀的数据分布。column_list自变量只是各列的一个清单。本示例显示了由关键字进行分区的一个简单的表，分为4个分区：

o                     CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)

o                         PARTITION BY KEY(col3)

o                         PARTITIONS 4;

采用LINEAR关键词，您可以对由关键字分区的表进行线形分区。这与由HASH进行分区的表格有同样的效果；也就是说，使用&操作符查找分区数目，而不是使用模数（详细说明见18.2.3.1节，“LINEAR HASH分区”和18.2.4节，“KEY分区”）。本示例采用了关键字线形分区，用来在5个分区之间分配数据：

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)

    PARTITION BY LINEAR KEY(col3)

    PARTITIONS 5;

VALUES LESS THAN或VALUES IN子句不能和PARTITION BY KEY一起使用。

o        RANGE：在此情况下，expr使用一套VALUES LESS THAN操作符显示了某一范围内的值。当使用范围分区时，您必须使用VALUES LESS THAN定义至少一个分区。VALUES IN不能和范围分区一起使用。

VALUES LESS THAN可以与一个文字值同时使用，或者与一个可以求算单一值的表达式同时使用。

举例说明，假设您有一个表，您希望采用以下方法对包含年份值的一列进行分区：

采用这种分区方法的表可以通过如下CREATE TABLE语句实现：

CREATE TABLE t1 (

    year_col INT, 

    some_data INT 

) 

PARTITION BY RANGE (year_col) (

    PARTITION p0 VALUES LESS THAN (1991),

    PARTITION p1 VALUES LESS THAN (1995),

    PARTITION p2 VALUES LESS THAN (1999),

    PARTITION p3 VALUES LESS THAN (2002),

    PARTITION p4 VALUES LESS THAN (2006),

    PARTITION p5 VALUES LESS THAN MAXVALUE

);

PARTITION ... VALUES LESS THAN ...语句按顺序执行。VALUES LESS THAN MAXVALUE的作用是指定大于最大值的“其余”的值。

注意，VALUES LESS THAN子句按顺序执行，执行方式类似于switch ... case语段的一部分（许多编程语言，如C, Java和PHP也如此）。也就是说，子句必须按照这样一种方法排列，每一个后续的VALUES LESS THAN中指定的上限值大于前一个VALUES LESS THAN中指定的上限值，并在清单的最后加一个参照性的MAXVALUE。

VALUES IN与一系列的值同时使用。举例说明，您可以创建如下的分区方法：

CREATE TABLE client_firms (

    id INT,

    name VARCHAR(35)

)

PARTITION BY RANGE (id) (

    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),

    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),

    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),

    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)

);

当前，与VALUES IN...同时使用的值必须只包含整数值。

（因为此表只使用VALUES IN表达式进行分区，您也可以用PARTITION BY LIST代替，而不是使用PARTITION BY RANGE。请参见下一条。）

在使用VALUES LESS THAN或VALUES IN情况下，每个分区使用PARTITION name定义，此处name是分区的标识名，后面接VALUES...子句。

o        LIST(expr)：当根据含有一系列限定性值（例如州代码或国家代码）的列进行分区时使用。在这种情况下，所有与特定的州或国家有关的记录都被分配到一个单一分区中，或者可以预留出一个分区，用于一系列特定的州或国家。LIST(expr)与RANGE类似，除了一点以外，即只有VALUES IN可以被用于为每个分区指定值。

当使用清单分区时，您必须使用VALUES IN定义至少一个分区。VALUES LESS THAN不能与PARTITION BY LIST一起使用。

o        分区数目可以使用PARTITION num子句，自选进行指定，此处，num是分区的数目。如果本子句和其它PARTITION子句同时使用，则num必须与使用PARTITION子句说明的分区的总数相等。

注释：不论您在创建一个由RANGE或LIST进行分区的表时是否使用了PARTITIONS子句，您必须在表定义中包括至少一个PARTITION VALUES（见后）。

o        一个分区可以自选分隔成多个子分区。使用自选的SUBPARTITION BY子句可以指示。子分区可以由HASH或KEY进行分隔。两种方法建立的子分区均为LINEAR。分隔子分区时的操作方式与以前描述的分区类型的操作方式一样。（无法由LIST或RANGE进行子分区分隔。）

使用SUBPARTITIONS关键词，后面接一个整值，可以对子分区的数目进行指示。

·         使用一个partition_definition子句可以对每个分区分别进行定义。下面是组成这个子句的各个部分：

o        PARTITION partition_name：用于为分区指定一个逻辑名称。

o        VALUE子句：对于范围分区，每个分区必须包括一个VALUES LESS THAN子句；对于清单分区，您必须为每个分区指定一个VALUES IN子句。本子句用于确定哪些行将被存储到此分区中。要了解语法示例，请参见第18章：分区中对分区类型的讨论。

o        自选的COMMENT子句可以用于描述分区。注释必须加单引号。举例说明：

o                     COMMENT = 'Data for the years previous to 1999'

o        DATA DIRECTORY和INDEX DIRECTORY可以被用于指示本分区的数据和索引各自的存储位置的目录。data_dir和index_dir都必须是绝对系统路径。例如：

o                     CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)

o                     PARTITION BY LIST(YEAR(adate))

o                     (

o                         PARTITION p1999 VALUES IN (1995, 1999, 2003) DATA DIRECTORY = '/var/appdata/95/data' INDEX DIRECTORY = '/var/appdata/95/idx',

o                         PARTITION p2000 VALUES IN (1996, 2000, 2004) DATA DIRECTORY = '/var/appdata/96/data' INDEX DIRECTORY = '/var/appdata/96/idx',

o                         PARTITION p2001 VALUES IN (1997, 2001, 2005) DATA DIRECTORY = '/var/appdata/97/data' INDEX DIRECTORY = '/var/appdata/97/idx',

o                         PARTITION p2000 VALUES IN (1998, 2002, 2006) DATA DIRECTORY = '/var/appdata/98/data' INDEX DIRECTORY = '/var/appdata/98/idx'

);

DATA DIRECTORY和INDEX DIRECTORY的操作方法与CREATE TABLE语句中的table_option子句的操作方法一样。此table_option子句用于位于MyISAM表管理程序下的各表。

可以为每个分区指定一个数据目录和一个索引目录。如果不指定，则数据和索引被存储在默认的MySQL数据目录中。

o        MAX_ROWS和MIN_ROWS分别用于将被存储在分区中的行数目最大值和行数目最小值。max_number_of_rows和min_number_of_rows的值必须为正整数。和具有同样名称的桌面选项一样，max_number_of_rows和min_number_of_rows只作为对服务器的“建议”值，并不是硬性限值。

o        自选的TABLESPACE子句可以用于为分区指定一个桌面空间。仅用于MySQL Cluster。

o        自选的[STORAGE] ENGINE子句可以把本分区中表的类型改为指定的类型。表的类型可以是本MySQL服务器支持的所有类型。STORAGE关键字和等号(=)均为自选项。如果没有使用此选项设置分区存储引擎，则适用于整个表的引擎可以用于此分区。

注释：分区管理程序对于PARTITION和SUBPARTITION均接受[STORAGE] ENGINE选项。目前，此子句的使用方式仅限于对所有的分区或子分区设置同一个存储引擎，如果试图在同一个表内对不同的分区或子分区设置不同的存储引擎，则会出现错误ERROR 1469 (HY000)：在本版本的MySQL中，不允许在各分区中混用管理程序。我们打算在将来的MySQL 5.1版本中加入这种对分区的限定。

o        NODEGROUP选项可以用于使本分区可以作为节点组的一部分，节点组使用node_group_id识别。本选项仅适用于MySQL Cluster。

o        分区定义可以自选地包含一个或多个subpartition_definition子句。每个这种子句至少包括SUBPARTITION name，此处，name是子分区的识别名称。除了用SUBPARTITION代替PARTITION关键词外，用于子分区定义的语法与用于分区定义的语法一样。

子分区必须由HASH或KEY完成，并且只能对RANGE或LIST分区进行子分区。请参见18.2.5节，“子分区”。

·         分区可以修改、合并、添加到表中，或从表中删去。要了解有关完成这些任务的MySQL命令的基本说明，请参见13.1.2节，“ALTER TABLE语法”。要了解详细的说明和示例，请参见18.3节，“分区管理”。

您可以在CREATE TABLE语句的末尾添加一个SELECT语句，在一个表的基础上创建表。

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL会对SELECT中的所有项创建新列。举例说明：

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,

    ->        PRIMARY KEY (a), KEY(b))

    ->        TYPE=MyISAM SELECT b,c FROM test2;

本语句用于创建含三个列（a, b, c）的MyISAM表。注意，用SELECT语句创建的列附在表的右侧，而不是覆盖在表上。参考以下示例：

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: 0

mysql> SELECT * FROM bar;

+------+---+

| m    | n |

+------+---+

| NULL | 1 |

+------+---+

1 row in set (0.00 sec)

对应于表foo中的每一行，在表bar中插入一行，含有表foo中的值以及新列中的默认值。

在由CREATE TABLE...SELECT生成的表中，只在CREATE TABLE部分中命名的列首先出现。在两个部分中都命名的列和只在SELECT部分中命名的列随后出现。也可以通过指定CREATE TABLE部分中的列覆盖SELECT列中的数据类型。

如果在把数据复制到表中时出现错误，则表会自动被取消，不会被创建。

CREATE TABLE...SELECT不会自动创建任何索引。索引需要专门创建，以便使语句的灵活性更强。如果您希望为已创建的表建立索引，您应在SELECT语句前指定索引。

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

列的类型会发生部分转化。例如，AUTO_INCREAMENT属性不会被保留，VARCHAR列会变成CHAR列。

当使用CREATE...SELECT创建表时，在查询时一定要对功能调用和表达式起别名。如果不起别名，则CREATE语句会出现错误或者生成不符合需要的列名称。

CREATE TABLE artists_and_works

SELECT artist.name, COUNT(work.artist_id) AS number_of_works

FROM artist LEFT JOIN work ON artist.id = work.artist_id

GROUP BY artist.id;

您也可以明确地为一个已生成的列指定类型：

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

根据其它表的定义（包括在原表中定义的所有的列属性和索引），使用LIKE创建一个空表:

CREATE TABLE new_tbl LIKE orig_tbl;

CREATE TABLE...LIKE不会复制对原表或外键定义指定的DATA DIRECTORY或INDEX DIRECTORY表选项。

您可以在SELECT前增加IGNORE或REPLACE，指示如何对复制唯一关键字值的记录进行操纵。使用IGNORE后，如果新记录复制了原有的唯一关键字值的记录，则新记录被丢弃。使用REPLACE后，新记录替换具有相同的唯一关键字值的记录。如果没有指定IGNORE或REPLACE，则出现多重唯一关键字值时会导致发生错误。

为了确保更新日志/二进位日志可以被用于再次创建原表，MySQL不允许在CREATE TABLE...SELECT过程中进行联合插入。

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE用于取消数据库中的所用表格和取消数据库。使用此语句时要非常小心！如果要使用DROP DATABASE，您需要获得数据库DROP权限。

IF EXISTS用于防止当数据库不存在时发生错误。

也可以使用DROP SCHEMA。

如果您对一个带有符号链接的数据库使用DROP DATABASE，则链接和原数据库都被取消。

DROP DATABASE会返回已被取消的表的数目。此数目相当于被取消的.frm文件的数目。

在正常操作中MySQL自身会创建出一些文件和目录。DROP DATABASE语句会从给定的数据库目录中取消这些文件和目录：

·         所有带这些扩展名的文件：

·         名称中包含两位16进制数00-ff的所有子目录。这些子目录用于RAID表。（当对RAID表的支持被取消时，在MySQL 5.0中，这些目录不会被取消。您应该在升级到MySQL 5.0或更新的版本前转化原有的RAID表，并人工取消这些目录。请参见MySQL 5.0参考手册中有关从较早版本升级到MySQL 5.0的章节。MySQL 5.0参考手册可以从MySQL网站中获取。）

·         db.opt文件

如果在MySQL取消了上述这些文件之后，在数据库目录中仍保留有其它文件和目录，则数据库目录不能被取消。在这种情况下，您必须人工取消所有保留下的文件或目录，并再次发送DROP DATABASE语句。

您还可以使用mysqladmin来取消文件。请参见8.5节，“mysqladmin：用于管理MySQL服务器的客户端”。

DROP INDEX index_name ON tbl_name

DROP INDEX用于从表tbl_name中取消名称为index_name的索引。本语句被映射到一个ALTER TABLE语句中，用于取消索引。请参见13.1.2节，“ALTER TABLE语法”。

DROP [TEMPORARY] TABLE [IF EXISTS]

    tbl_name [, tbl_name] ...

    [RESTRICT | CASCADE]

DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心！

注意，对于一个带分区的表，DROP TABLE会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消的表有关联的分区定义（.par）文件。

对与不存在的表，使用IF EXISTS用于防止错误发生。当使用IF EXISTS时，对于每个不存在的表，会生成一个NOTE。请参见13.5.4.22节，“SHOW WARNINGS语法”。

RESTRICT和CASCADE可以使分区更容易。目前，RESTRICT和CASCADE不起作用。

注释：除非您使用TEMPORARY关键词，DROP TABLE会自动提交当前的有效的事务。

TEMPORARY关键词具有以下作用：

·         语句只取消TEMPORARY表。

·         语句不会终止正在进行中的事务。

·         不会查验存取权。（TEMPORARY表仅对于创建该表的客户端是可见的，所以查验是不必要的。）

使用TEMPORARY是确保您不会意外取消一个非TEMPORARY表的良好方法。

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

    [WHERE where_definition]

    [ORDER BY ...]

    [LIMIT row_count]

多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    tbl_name[.*] [, tbl_name[.*] ...]

    FROM table_references

    [WHERE where_definition]

或：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    FROM tbl_name[.*] [, tbl_name[.*] ...]

    USING table_references

    [WHERE where_definition]

tbl_name中有些行满足由where_definition给定的条件。DELETE用于删除这些行，并返回被删除的记录的数目。

如果您编写的DELETE语句中没有WHERE子句，则所有的行都被删除。当您不想知道被删除的行的数目时，有一个更快的方法，即使用TRUNCATE TABLE。请参见13.2.9节，“TRUNCATE语法”。

如果您删除的行中包括用于AUTO_INCREMENT列的最大值，则该值被重新用于BDB表，但是不会被用于MyISAM表或InnoDB表。如果您在AUTOCOMMIT模式下使用DELETE FROM tbl_name（不含WHERE子句）删除表中的所有行，则对于所有的表类型（除InnoDB和MyISAM外），序列重新编排。对于InnoDB表，此项操作有一些例外，在15.2.6.3节，“AUTO_INCREMENT列如何在InnoDB中运行”中进行了讨论。

对于MyISAM和BDB表，您可以把AUTO_INCREMENT次级列指定到一个多列关键字中。在这种情况下，从序列的顶端被删除的值被再次使用，甚至对于MyISAM表也如此。请参见3.6.9节，“使用AUTO_INCREMENT”。

DELETE语句支持以下修饰符：

·         如果您指定LOW_PRIORITY，则DELETE的执行被延迟，直到没有其它客户端读取本表时再执行。

·         对于MyISAM表，如果您使用QUICK关键词，则在删除过程中，存储引擎不会合并索引端结点，这样可以加快部分种类的删除操作的速度。

·         在删除行的过程中，IGNORE关键词会使MySQL忽略所有的错误。（在分析阶段遇到的错误会以常规方式处理。）由于使用本选项而被忽略的错误会作为警告返回。

删除操作的速度会受到一些因素的影响，这些因素在7.2.18节，“DELETE语句的速度”中进行了讨论。

在MyISAM表中，被删除的记录被保留在一个带链接的清单中，后续的INSERT操作会重新使用旧的记录位置。要重新使用未使用的空间并减小文件的尺寸，则使用OPTIMIZE TABLE语句或myisamchk应用程序重新编排表。OPTIMIZE TABLE更简便，但是myisamchk速度更快。请参见13.5.2.5节，“OPTIMIZE TABLE语法”和第7章：优化。

QUICK修饰符会影响到在删除操作中索引端结点是否合并。当用于被删除的行的索引值被来自后插入的行的相近的索引值代替时，DELETE QUICK最为适用。在此情况下，被删除的值留下来的空穴被重新使用。

未充满的索引块跨越某一个范围的索引值，会再次发生新的插入。当被删除的值导致出现未充满的索引块时，DELETE QUICK没有作用。在此情况下，使用QUICK会导致未利用的索引中出现废弃空间。下面是此种情况的举例说明：

1.    创建一个表，表中包含已编索引的AUTO_INCREMENT列。

2.    在表中插入很多记录。每次插入会产生一个索引值，此索引值被添加到索引的高端处。

3.    使用DELETE QUICK从列的低端处删除一组记录。

在此情况下，与被删除的索引值相关的索引块变成未充满的状态，但是，由于使用了QUICK，这些索引块不会与其它索引块合并。当插入新值时，这些索引块仍为未充满的状态，原因是新记录不含有在被删除的范围内的索引值。另外，即使您此后使用DELETE时不包含QUICK，这些索引块也仍是未充满的，除非被删除的索引值中有一部分碰巧位于这些未充满的块的之中，或与这些块相邻。在这些情况下，如果要重新利用未使用的索引空间，需使用OPTIMIZE TABLE。

如果您打算从一个表中删除许多行，使用DELETE QUICK再加上OPTIMIZE TABLE可以加快速度。这样做可以重新建立索引，而不是进行大量的索引块合并操作。

用于DELETE的MySQL唯一的LIMIT row_count选项用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。本选项用于确保一个DELETE语句不会占用过多的时间。您可以只重复DELETE语句，直到相关行的数目少于LIMIT值为止。

如果DELETE语句包括一个ORDER BY子句，则各行按照子句中指定的顺序进行删除。此子句只在与LIMIT联用是才起作用。例如，以下子句用于查找与WHERE子句对应的行，使用timestamp_column进行分类，并删除第一（最旧的）行：

DELETE FROM somelog

WHERE user = 'jcole'

ORDER BY timestamp_column

LIMIT 1;

您可以在一个DELETE语句中指定多个表，根据多个表中的特定条件，从一个表或多个表中删除行。不过，您不能在一个多表DELETE语句中使用ORDER BY或LIMIT。

table_references部分列出了包含在联合中的表。此语法在13.2.7.1节，“JOIN语法”中进行了说明。

对于第一个语法，只删除列于FROM子句之前的表中的对应的行。对于第二个语法，只删除列于FROM子句之中（在USING子句之前）的表中的对应的行。作用是，您可以同时删除许多个表中的行，并使用其它的表进行搜索：

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

或：

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

当搜索待删除的行时，这些语句使用所有三个表，但是只从表t1和表t2中删除对应的行。

以上例子显示了使用逗号操作符的内部联合，但是多表DELETE语句可以使用SELECT语句中允许的所有类型的联合，比如LEFT JOIN。

本语法允许在名称后面加.*，以便与Access相容。

如果您使用的多表DELETE语句包括InnoDB表，并且这些表受外键的限制，则MySQL优化程序会对表进行处理，改变原来的从属关系。在这种情况下，该语句出现错误并返回到前面的步骤。要避免此错误，您应该从单一表中删除，并依靠InnoDB提供的ON DELETE功能，对其它表进行相应的修改。

注释：当引用表名称时，您必须使用别名（如果已给定）：

DELETE t1 FROM test AS t1, test2 WHERE ...

进行多表删除时支持跨数据库删除，但是在此情况下，您在引用表时不能使用别名。举例说明：

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

目前，您不能从一个表中删除，同时又在子查询中从同一个表中选择。

DO expr [, expr] ...

HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE

HANDLER语句提供通往表存储引擎接口的直接通道。HANDLER可以用于MyISAM和InnoDB表。

HANDLER...OPEN语句用于打开一个表，通过后续的HANDLER...READ语句建立读取表的通道。本表目标不会被其它线程共享，也不会关闭，直到线程调用HANDLER...CLOSE或线程中止时为止。如果您使用一个别名打开表，则使用其它HANDLER语句进一步参阅表是必须使用此别名，而不能使用表名。

如果被指定的索引满足给定的值并且符合了WHERE条件，则第一个HANDLER...READ语法取出一行。如果您有一个多列索引，则指定索引列值为一个用逗号隔开的清单。既可以为索引中的所有列指定值，也可以为索引列的最左边的前缀指定值。假设一个索引包括三个列，名称为col_a, col_b,和col_c，并按此顺序排列。HANDLER语句可以为索引中的所有三个列指定值，或者为一个最左边前缀中的各列指定值。举例说明：

HANDLER ... index_name = (col_a_val,col_b_val,col_c_val) ...

HANDLER ... index_name = (col_a_val,col_b_val) ...

HANDLER ... index_name = (col_a_val) ...

第二个HANDLER...READ语法按索引的顺序从表中取出一行。索引的顺序符合WHERE条件。

第三个HANDLER...READ语法按自然行的顺序从表中取出一行。自然行的顺序符合WHERE条件。当想要对整个表进行扫描时，此语句比HANDLER tbl_name READ index_name更快。自然行的顺序指的是行存储在MyISAM表数据文件的顺序。本语句也适用于InnoDB表，但是因为没有独立的数据文件，所以没有这类概念。

不使用LIMIT子句时，所有形式的HANDLER...READ语句均只取出一行。 如果要返回多个行，应加入一个LIMIT子句。本语句于SELECT语句的语法一样。请参见13.2.7节，“SELECT语法”。

HANDLER...CLOSE用于关闭使用HANDLER...OPEN打开的表。

注释：要使用HANDLER接口来查阅一个表的PRIMARY KEY，应使用带引号的识别符`PRIMARY`：

HANDLER tbl_name READ `PRIMARY` > (...);

HANDLER是比较低级别的语句。例如，它不能提供一致性。也就是说，HANDLER...OPEN不能为表做快照，也不能锁定表。这意味着，当一个HANDLER...OPEN语句被编写后，表数据可以被更改（用此线程或用其它线程），并且这些更改只会部分地出现在HANDLER...NEXT或HANDLER...PREV扫描中。

使用HANDLER接口代替常规的SELECT语句有多个原因：

·         HANDLER比SELECT更快：

o        一个指定的存储引擎管理程序目标为了HANDLER...OPEN进行整序。该目标被重新用于该表的后续的HANDLER语句；不需要对每个语句进行重新初始化。

o        涉及的分析较少。

o        没有优化程序或查询校验开销。

o        在两个管理程序请求之间，不需要锁定表。

o        管理程序接口不需要提供外观一致的数据（例如，允许无条理的读取），所以存储引擎可以使用优化，而SELECT通常不允许使用优化。

·         有些应用程序使用与ISAM近似的接口与MySQL连接。使用HANDLER可以更容易地与这些应用程序连接。

·         HANDLER允许您采用一种特殊的方式进出数据库。而使用SELECT时难以采用（或不可能采用）这种方式。有些应用程序可以提供一个交互式的用户接口与数据库连接。当与这些应用程序同时使用时，用HANDLER接口观看数据更加自然。

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    VALUES ({expr | DEFAULT},...),(...),...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    SET col_name={expr | DEFAULT}, ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT用于向一个已有的表中插入新行。INSERT...VALUES和INSERT...SET形式的语句根据明确指定的值插入行。INSERT...SELECT形式的语句插入从其它表中选出的行。在13.2.4.1节，“INSERT ... SELECT语法”中对INSERT...SELECT进行了进一步的讨论。

行应被插入到tbl_name表中。可以按以下方法指定列。本语句向这些列提供值。

·         列名称清单或SET子句明确的指示了列。

·         如果您不为INSERT...VALUES或INSERT...SELECT指定列的清单，则表中每列的值必须在VALUES清单中提供，或由SELECT提供。如果您不知道表中各列的顺序，则使用DESCRIBE tbl_name查询。

列值可以采用多种方法给定：

·         如果不是在严格模式下运行，则所有没有明确给定值的列都被设置为默认值（明确的或隐含的）。例如，如果您指定了一个列清单，但此清单没有对表中所有的列进行命名，则未命名的各列被设置为默认值。默认值的赋值在13.1.5节，“CREATE TABLE语法”中进行了说明。也可参见1.8.6.2节，“对无效数据的约束”。

有时候，您需要对所有没有默认值的列明确地指定值。如果您希望，在没有明确指定值时，INSERT语句可以生成错误信息，则您应该使用STRICT模式。请参见5.3.2节，“SQL服务器模式”。

·         使用关键词DEFAULT，明确地把列设置为默认值。这样，编写向所有列赋值的INSERT语句时可以更容易，因为使用DEFAULT可以避免编写出不完整的、未包含全部列值的VALUES清单。如果不使用DEFUALT，您必须编写一个列名称清单，与VALUES清单中的每个值对应。

您还可以使用DEFAULT(col_name)作为一种更通用的形式，在表达式中使用，用于生成一个列的默认值。

·         如果列清单和VALUES清单均为空清单，则INSERT会创建一个行，每个列都被设置为默认值：

·                mysql> INSERT INTO tbl_name () VALUES();

在STRICT模式中，如果有一列没有默认值，则会出现错误。或者，MySQL会对所有没有明确定义默认值的列使用隐含的默认值。

·         您可以指定一个表达式expr来提供一个列值。如果表达式的类型与列值不匹配，这样做会造成类型转化。并且，给定值的转化会导致不同的插入值，插入何值由列类型而定。例如，向一个INT, FLOAT, DECIMAL(10,6)或YEAR列插入字符串'1999.0e-2'，插入值分别是1999，19.9921，19.992100和1999。存储在INT和YEAR列中的值为1999的原因是，在从字符串到整数的转化中，只把字符串的前面部分看作有效的整数或年份。对于浮点列和固定点列，在从字符串到浮点的转化中，把整个字符串均看作有效的浮点值。

表达式expr可以引用在值清单中已设置的所有列。例如，您可以这么操作，因为用于col2的值引用了col1，而col1已经被赋值：

mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

但是以下语句不合法，因为用于col1的值引用了col2，而col2在col1之后被赋值：

mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

有一种例外情况，那就是含有AUTO_INCREMENT值的列。因为AUTO_INCREMENT值在其它值赋值之后被生成，所以任何在赋值时对AUTO_INCREMENT列的引用都会返回0。

INSERT语句支持下列修改符：

·         如果您使用DELAYED关键字，则服务器会把待插入的行放到一个缓冲器中，而发送INSERT DELAYED语句的客户端会继续运行。如果表正在被使用，则服务器会保留这些行。当表空闲时，服务器开始插入行，并定期检查是否有新的读取请求。如果有新的读取请求，则被延迟的行被延缓执行，直到表再次空闲时为止。请参见13.2.4.2节，“INSERT DELAYED语法”。

·         如果您使用LOW_PRIORITY关键词，则INSERT的执行被延迟，直到没有其它客户端从表中读取为止。当原有客户端正在读取时，有些客户端刚开始读取。这些客户端也被包括在内。此时，INSERT LOW_PRIORITY语句等候。因此，在读取量很大的情况下，发出INSERT LOW_PRIORITY语句的客户端有可能需要等待很长一段时间（甚至是永远等待下去）。（这与INSERT DELAYED形成对比，INSERT DELAYED立刻让客户端继续执行。请参见13.2.4.2节，“INSERT DELAYED语法”。）注意LOW_PRIORITY通常不应用于MyISAM表，因为这么做会取消同时进行的插入。请参见15.1节，“MyISAM存储引擎”。

·         如果您指定了HIGH_PRIORITY，同时服务器采用--low-priority-updates选项启动，则HIGH_PRIORITY将覆盖--low-priority-updates选项。这么做还会导致同时进行的插入被取消。

·         使用mysql_affected_rows() C API函数，可以获得用于INSERT的受影响行的值。请参见25.2.3.1节，“mysql_affected_rows()”。

·         如果您在一个INSERT语句中使用IGNORE关键词，在执行语句时出现的错误被当作警告处理。例如，没有使用IGNORE时，如果一个行复制了原有的UNIQUE索引或PRIMARY KEY值，会导致出现重复关键字错误，语句执行失败。使用IGNORE时，该行仍然未被插入，但是不会出现错误。IGNORE未被指定时，如果数据转化引发错误，则会使语句执行失败。使用IGNORE后，无效数据被调整到最接近的值，并被插入；此时，生成警告，但是语句执行不会失败。您可以使用mysql_info() C API函数测定有多少行被插入到表中。

如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)

    -> ON DUPLICATE KEY UPDATE c=c+1;

mysql> UPDATE table SET c=c+1 WHERE a=1;

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。

注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。换句话说，如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义，其它时候会返回NULL。

示例：

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

本语句与以下两个语句作用相同：

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)

    -> ON DUPLICATE KEY UPDATE c=3;

mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)

    -> ON DUPLICATE KEY UPDATE c=9;

当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。

您可以使用SQL LAST_INSERT_ID()函数查找用于AUTO_INCREMENT列的值。从C API的内部，使用mysql_insert_id()函数。不过，您应该注意，两个函数的作用并不总是相同的。在12.9.3节，“信息函数”和25.2.3.36节，“mysql_insert_id()”中进一步讨论了与AUTO_INCREMENT列有关的INSERT语句的作用。

如果您使用INSERT...VALUES语句时采用了多个值清单或INSERT...SELECT，则该语句按以下格式返回一个信息字符串：

Records: 100 Duplicates: 0 Warnings: 0

记录指示了经过语句处理的行的数目。（因为重复数目可以不是零，所以该数目不一定是实际被插入的行的数目。）重复数目指的是不能被插入的行的数目，因为这些行会复制部分原有的唯一索引值。警告指的是插入有错误或有问题的列值的次数。在以下情况下会出现警告：

·         向一个已定义为NOT NULL的列中插入NULL。对于一个多行INSERT语句或INSERT INTO...SELECT语句，根据列数据的类型，列被设置为隐含的默认值。对于数字类型，默认值为0；对于字符串类型，默认值为空字符串('')；对于日期和时间类型，默认值为“zero”值。对INSERT INTO...SELECT语句的处理方法与对多行插入的处理方法一样，因为服务器不能检测来自SELECT的结果，不能判断是否返回单一行。（对于单一行INSERT，当NULL被插入一个NOT NULL列时，不会出现警告，而是出现错误，并且语句运行失败。）

·         数字列的值被设置在列的值范围之外。此值被修改为未最接近的值范围端点。

·         向一个数字列赋予一个例如'10.34 a'的值。尾部的非数字文本被删节，其余的数字部分被插入，如果字符串值没有前导的数字部分，则该列被设置为0。

·         向一个字符串列（CHAR, VARCHAR, TEXT或BLOB）中插入的字符串超过了列的最大长度。此值被删节到列的最大长度。

·         向日期或时间列中插入的值对于该列的类型是不合法的。根据列的类型，该列被设置到相应的零值。

如果您正在使用C API，则可以通过调用mysql_info()函数获取信息字符串。请参见25.2.3.34节，“mysql_info()”。

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT INTO tbl_temp2 (fld_id)

    SELECT tbl_temp1.fld_order_id

    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

INSERT DELAYED ...

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [FIELDS

        [TERMINATED BY 'string']

        [[OPTIONALLY] ENCLOSED BY 'char']

        [ESCAPED BY 'char' ]

    ]

    [LINES

        [STARTING BY 'string']

        [TERMINATED BY 'string']

    ]

    [IGNORE number LINES]

    [(col_name_or_user_var,...)]

    [SET col_name = expr,...)]

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。

要了解有关INSERT和LOAD DATA INFILE的效率的对比和有关LOAD DATA INFILE加速的更多信息，请参见7.2.16节，“INSERT语句的速度”。

由character_set_database系统变量指示的字符集被用于解释文件中的信息。SET NAMES和character_set_client的设置不会影响对输入的解释。

注意，目前不能载入UCS2数据文件。

您也可以通过使用mysqlimport应用程序载入数据文件；通过向服务器发送一个LOAD DATA INFILE语句实现此功能。--local选项用于使mysqlimport从客户主机中读取数据文件。如果客户端和服务器支持压缩协议，则您可以指定—compress选项提高在慢速网络中的性能。请参见8.10节，“mysqlimport：数据导入程序。

如果您使用LOW_PRIORITY，则LOAD DATA语句的执行被延迟，直到没有其它的客户端从表中读取为止。

如果一个MyISAM表满足同时插入的条件（即该表在中间有空闲块），并且您对这个MyISAM表指定了CONCURRENT，则当LOAD DATA正在执行时，其它线程会从表中重新获取数据。即使没有其它线程在同时使用本表格，使用本选项也会略微影响LOAD DATA的性能。

如果指定了LOCAL，则被认为与连接的客户端有关：

·         如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。

·         如果LOCAL没有被指定，则文件必须位于服务器主机上，并且被服务器直接读取。

当在服务器主机上为文件定位时，服务器使用以下规则：

·         如果给定了一个绝对的路径名称，则服务器使用此路径名称。

·         如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。

·         如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。

注意，这些规则意味着名为./myfile.txt的文件会从服务器数据目录中被读取，而名为myfile.txt的同样的文件会从默认数据库的数据库目录中读取。例如，下面的LOAD DATA语句会从db1数据库目录中读取文件data.txt，因为db1是当前数据库。即使语句明确把文件载入到db2数据库中的表里，也会从db1目录中读取。

mysql> USE db1;

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

注意，使用正斜杠指定Windows路径名称，而不是使用反斜杠。如果您使用反斜杠，您必须使用两个。

出于安全原因，当读取位于服务器中的文本文件时，文件必须位于数据库目录中，或者是全体可读的。另外，要对服务器文件使用LOAD DATA INFILE，您必须拥有FILE权限。

见5.7.3节，“MySQL提供的权限”。

与让服务器直接读取文件相比，使用LOCAL速度略慢，这是因为文件的内容必须通过客户端发送到服务器上。不过，您不需要FILE权限来载入本地文件。

只有当您的服务器和您的客户端都许可时，LOCAL才可运行。例如，如果使用—local-infile=0启动mysqld，则LOCAL不运行。请参见5.6.4节，“LOAD DATA LOCAL安全问题”。

如果您需要LOAD DATA来从一个管道中读取，您可以使用以下方法（此处我们把/目录清单载入一个表格）：

mkfifo /mysql/db/x/x

chmod 666 /mysql/db/x/x

find / -ls > /mysql/db/x/x

mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

有些输入记录把原有的记录复制到唯一关键字值上。REPLACE和IGNORE关键字用于控制这些输入记录的操作。

如果您指定了REPLACE，则输入行会替换原有行（换句话说，与原有行一样，对一个主索引或唯一索引具有相同值的行）。请参见13.2.6节，“REPLACE语法”。

如果您指定IGNORE，则把原有行复制到唯一关键字值的输入行被跳过。如果您这两个选项都不指定，则运行情况根据LOCAL关键词是否被指定而定。不使用LOCAL时，当出现重复关键字值时，会发生错误，并且剩下的文本文件被忽略。使用LOCAL时，默认的运行情况和IGNORE被指定时的情况相同；这是因为在运行中间，服务器没有办法中止文件的传输。

如果您希望在载入运行过程中忽略外键的限制，您可以在执行LOAD DATA前发送一个SET FOREIGN_KEY_CHECKS=0语句。

如果您对一个空的MyISAM表使用LOAD DATA INFILE，则所有的非唯一索引会被创建在一个独立批中（对于REPAIR TABLE）。当您有许多索引时，这通常会使LOAD DATA INFILE大大加快。通常，LOAD DATA INFILE的速度会非常快，但是在某些极端情况下，您可以在把文件载入到表中之前使用ALTER TABLE...DISABLE KEYS关闭LOAD DATA INFILE，或者在载入文件之后使用ALTER TABLE...ENABLE KEYS再次创建索引，使创建索引的速度更快。请参见7.2.16节，“INSERT语句的速度”。

LOAD DATA INFILE是SELECT...INTO OUTFILE的补语。（见13.2.7节，“SELECT语法”。）要从一个表中把数据写入一个文件中，应使用SELECT...INTO OUTFILE。要读取文件，放回到表中，应使用LOAD DATA INFILE。FIELDS和LINES子句的语法对于两个语句是一样的。两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。

如果您指定了一个FIELDS子句，则每个亚子句（TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY）也是自选的。不过，您必须指定其中至少一个。

如果您不指定FIELDS子句，则默认值为假设您写下如下语句时的值：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

如果您不指定LINES子句，则默认值为假设您写下如下语句时的值：

LINES TERMINATED BY '\n' STARTING BY ''

换句话说，当读取输入值时，默认值会使LOAD DATA INFILE按如下方式运行：

·         在新行处寻找行的边界。

·         不会跳过任何行前缀。

·         在制表符处把行分解为字段。

·         不希望字段被包含在任何引号字符之中。

·         出现制表符、新行、或在‘\’前有‘\’时，理解为作为字段值一部分的文字字符。

相反的，当编写输出值时，默认值会使SELECT...INTO OUTFILE按如下方式运行：

·         在字段之间写入制表符。

·         不把字段包含在任何引号字符中。

·         当字段值中出现制表符、新行或‘\’时，使用‘\’进行转义。

·         在行的末端写入新行。

注意，要写入FIELDS ESCAPED BY ‘\\’，您必须为待读取的值指定两个反斜杠，作为一个单反斜杠使用。

注释：如果您已经在Windows系统中生成了文本文件，您可能必须使用LINES TERMINATED BY ‘\r\n’来正确地读取文件，因为Windows程序通常使用两个字符作为一个行终止符。部分程序，比如WordPad，当编写文件时，可能会使用\r作为行终止符。要读取这样的文件，应使用LINES TERMINATED BY ‘\r’。

如果所有您希望读入的行都含有一个您希望忽略的共用前缀，则您可以使用'prefix_string'来跳过前缀（和前缀前的字符）。如果某行不包括前缀，则整个行被跳过。注释：prefix_string会出现在一行的中间。

示例：

mysql> LOAD DATA INFILE '/tmp/test.txt'

    -> INTO TABLE test LINES STARTING BY "xxx";

使用此语句，您可以读入包含有如下内容的文件：

xxx"row",1

something xxx"row",2

并只得到数据("row",1)和("row",2)。

IGNORE number LINES选项可以被用于在文件的开始处忽略行。例如，您可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行：

mysql> LOAD DATA INFILE '/tmp/test.txt'

    -> INTO TABLE test IGNORE 1 LINES;

当您联合使用SELECT...INTO OUTFILE和LOAD DATA INFILE来从一个数据库中把数据写入一个文件中，然后再读取文件，返回到数据库中时，用于两个语句的field-和line-handling选项必须匹配。否则，LOAD DATA INFILE不会正确地理解文件的内容。假设您使用SELECT...INTO OUTFILE来编写一个的文件，字段由逗号分隔：

mysql> SELECT * INTO OUTFILE 'data.txt'

    ->          FIELDS TERMINATED BY ','

    ->          FROM table2;

要读取由逗号分隔的文件并返回，则正确的语句应该是：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2

    ->           FIELDS TERMINATED BY ',';

如果您尝试使用以下所示的语句读入文件，则不会运行，因为该语句命令LOAD DATA INFILE寻找位于字段之间的制表符：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2

    ->           FIELDS TERMINATED BY '\t';

结果很可能是，每个输入行被理解为一个单一字段。

LOAD DATA INFILE也可以被用于读取从外源中获取的文件。例如，一个dBASE格式的文件具有以逗号分隔并且包含在双引号中的字段。如果文件中的各行以新行为结尾，则此处所示的语句描述了您可以用于载入文件的field-和line-handling选项：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name

    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'

    ->           LINES TERMINATED BY '\n';

所有field-或line-handling选项都可以指定一个空字符串('')。如果字符串不是空的，则FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须为单一字符。FIELDS TERMINATED BY, LINES STARTING BY和LINES TERMINATED BY值可以超过一个字符。例如，要编写由回车/换行成对字符作为结尾的行，或读取包含这类行的文件，则应指定一个LINES TERMINATED BY ‘\r\n’子句。

如果jokes被由%%组成的行分隔，要读取包含jokes的文件，您可以这么操作：

mysql> CREATE TABLE jokes

    ->     (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    ->     joke TEXT NOT NULL);

mysql> LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes

    ->     FIELDS TERMINATED BY ''

    ->     LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY用于控制字段的引号。对于（SELECT...INTO OUTFILE），如果您忽略了词语OPTIONALLY，则所有的字段都被包含在ENCLOSED BY字符串中。此处展示了此类输出的一个示例（使用逗号作为字段分隔符）：

"1","a string","100.20"

"2","a string containing a , comma","102.20"

"3","a string containing a \" quote","102.20"

"4","a string containing a \", quote and comma","102.20"

如果您指定了OPTINALLY，则ENCLOSED BY字符只被用于包含具有字符串数据类型（比如CHAR, BINARY, TEXT或ENUM）的列中的值：

1,"a string",100.20

2,"a string containing a , comma",102.20

3,"a string containing a \" quote",102.20

4,"a string containing a \", quote and comma",102.20

注意，如果在字段值内出现ENCLOSED BY字符，则通过使用ESCAPED BY字符作为前缀，对ENCLOSED BY字符进行转义。另外，要注意，如果您指定了一个空的ESCAPED BY值，则可能会生成不能被LOAD DATA INFILE正确读取的输出值。例如，如果转义符为空字符，则刚显示的先前输出值应显示如下。请观察，第四行中的第二个字段在引号后面包含一个逗号，该引号（错误地）显示出来，作为字段的结尾：

1,"a string",100.20

2,"a string containing a , comma",102.20

3,"a string containing a " quote",102.20

4,"a string containing a ", quote and comma",102.20

对于输入值，ENCLOSED BY字符被从字段字的末尾剥离。（不论OPTIONALLY是否被指定都会剥离；OPTIONALLY对输入值的解释没有影响。）如果ENCLOSED BY字符前面带有ESCAPED BY字符，则被理解为当前字段值的一部分。

如果字段以ENCLOSED BY字符为开始，当出现这类字符时，只有后面接着字段或行TERMINATED BY序列时，这类字符被认为是一个字段值的结尾。为了避免意思不明确，当在一个字段值中出现ENCLOSED BY字符时，此字符可以重复书写，并被理解为单一的字符。例如，如果指定了ENCLOSED BY '"'，则按照以下方法操作引号：

"The ""BIG"" boss"  -> The "BIG" boss

The "BIG" boss      -> The "BIG" boss

The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY用于控制如何写入或读取特殊字符。如果FIELDS ESCAPED BY字符不是空字符，则可以在输出中用于对以下字符加前缀：

·         FIELDS ESCAPED BY字符

·         FIELDS [OPTIONALLY] ENCLOSED BY字符

·         FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符

·         ASCII 0（在转义符之后编写的字符实际上是ASCII‘0’，而不是一个值为0的字节）

如果FIELDS ESCAPED BY字符为空字符，则没有字符被转义，并且NULL被作为NULL输出，而不是\N。去指定一个空的转义符不是一个好办法，特别是如果数据的字段值包含任何刚给定的清单中的字符时，更不能这么做。

对于输入值，如果FIELDS ESCAPED BY字符不是空字符，则出现这种字符时会被剥离，然后以下字符被作为字段值的一部分。例外情况是，被转义的‘0’或‘N’（例如，\0或\N，此时转义符为‘\’）。这些序列被理解为ASCII NUL（一个零值字节）和NULL。用于NULL处理的规则在本节的后部进行说明。

要了解有关‘\’-escape语法的更多信息，请参见9.1节，“文字值”。

在特定情况下，field-和line-handling选项相互影响：

·         如果LINES TERMINATED BY是空字符串，并且FIELDS TERMINATED BY不是空字符串，则各行以FIELDS TERMINATED BY作为结尾。

·         如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值均为空值('')，则使用固定行（无分隔）格式。使用固定行格式时，在字段之间不使用分隔符（但是您仍然可以有行终止符）。列值使用列的显示宽度进行写入和读取。例如，如果某列被定义为INT(7)，则使用7字符字段写入列值。输出时，通过读取7个字符获取列值。

LINES TERMINATED BY仍然用于分隔行。如果某行不包含所有字段，则其余的各列被设置到默认值。如果您没有行终止符，您应该把终止符设置为''。在此情况下，文本文件必须包含每行的所有字段。

固定行格式也会影响NULL值的操作，这将在以后进行介绍。注意，如果您正在使用一个多字节字符集，则固定规格格式不会运行。

根据正在使用中的FIELDS和LINES选项的不同，NULL值的操作有所变化：

·         对于默认的FIELDS和LINES值，NULL被作为\N的字段值编写，用于输出；\N字段值被作为NULL读取，用于输入（假设ESCAPED BY字符为‘\’）。

·         如果FIELDS ENCLOSED BY不是空值，则包含以文字词语NULL为值的字段被作为NULL值读取。这与被FIELDS ENCLOSED BY字符包围的词语NULL不同。该词语被作为字符串'NULL'读取。

·         如果FIELDS ESCAPED BY是空值，则NULL被作为词语NULL写入。

·         采用固定行格式时（当FIELDS TERMINATED BY和FIELDS ENCLOSED BY均为空值时采用），NULL被作为一个空字符串写入。注意，这会导致在被写入文件时，表中的NULL值和空字符串均无法辨别，这是因为两者都被作为空字符串写入。如果您需要在读取文件并返回时能够分辨两者，则您不应使用固定行格式。

LOAD DATA INFILE不支持有些情况：

·         固定规格行（FIELDS TERMINATED BY和FIELDS ENCLOSED BY均为空值）和BLOB或TEXT列。

·         如果您指定了一个分隔符，并且该分隔符与其它的前缀一样，则LOAD DATA INFILE不能正确地理解输入值。例如，下面的FIELDS子句会导致问题：

·                FIELDS TERMINATED BY '"' ENCLOSED BY '"'

·         如果FIELDS ESCAPED BY为空值，则包含FIELDS ENCLOSED BY或LINES TERMINATED BY的字段值后面再接FIELDS TERMINATED BY值会导致LOAD DATA INFILE过早地停止读取一个字段或行。出现这种情况的原因是LOAD DATA INFILE不能正确地决定字段或行值在哪里结束。

以下的例子载入了persondata表中的所有列：

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

默认情况下，如果在LOAD DATA INFILE语句的末尾处没有设列清单时，则输入行预计会包含一个字段，用于表中的每个列。如果您只想载入一个表的部分列，则应指定一个列清单：

mysql> LOAD DATA INFILE 'persondata.txt'

    ->           INTO TABLE persondata (col1,col2,...);

如果输入文件中各字段的顺序与表中各列的顺序不同，您也必须指定一个列清单。否则，MySQL不能把输入字段和表中的列匹配起来。

列清单可以包含列名称或用户变量。支持SET子句。这使您可以把输入值赋予用户变量，然后在把结果赋予列之前，对这些值进行变换。

SET子句中的用户变量可以采用多种方式使用。以下例子使用数据文件中的第一列，直接用于t1.column1的值。在用户变量被用于t2.column2值之前，把第二列赋予用户变量。该变量从属于一个分割运行。

LOAD DATA INFILE 'file.txt'

  INTO TABLE t1

  (column1, @var1)

  SET column2 = @var1/100;

SET子句可以被用于提供不是来源于输入文件的值。以下语句把column3设置为当前的日期和时间：

LOAD DATA INFILE 'file.txt'

  INTO TABLE t1

  (column1, column2)

  SET column3 = CURRENT_TIMESTAMP;

您也可以通过把输入值赋予一个用户变量，同时不把变量赋予表中的列，来丢弃此输入值：

LOAD DATA INFILE 'file.txt'

  INTO TABLE t1

  (column1, @dummy, column2, @dummy, column3);

列/变量清单和SET子句的使用受到以下限定：

·         在SET子句中的赋值应只含有位于赋值操作符的左侧的列名称。

·         您可以在SET赋值的右侧使用子查询。如果子查询可以返回一个值，并且此值将被赋予到一个列中，则此子查询只能是标量子查询。另外，您不能使用子查询从一个正在被载入的表中选择。

·         对于于列/变量清单或SET子句，被IGNORE子句忽略的行不被处理。

·         当载入采用固定行格式的数据时，不能使用用户变量，因为用户变量没有显示宽度。

当处理一个输入行时，LOAD DATA会依据列/变量清单和SET子句，把行拆分成字段，并使用值。然后，得到的行被插入表中。如果有用于表的BEFORE INSERT或AFTER INSERT触发器，则在插入行之前和插入行之后分别启动触发器。

如果一个输入行含有过多的字段，则多余的字段被忽略，并且警告的数量增加。

如果一个输入行含有的字段过少，则输入字段缺失的表中的列被设置为默认值。默认值赋值在13.1.5节，“CREATE TABLE语法”中进行了说明。

如果字段值缺失，则对一个空字段值会被按不同方式理解：

·         对于字符串类型，列被设置为空字符串。

·         对于数字类型，列被设置为0。

·         对于日期和时间类型，列被设置为该类型相应的“zero”。请参见11.3节，“日期和时间类型”。

如果您明确地把一个空字符串赋予一个INSERT或UPDATE语句中的字符串类型、数字类型或日期或时间类型，则产生的这些值相同。

只有在两种情况下TIMESTAMP列被设置为当前日期和时间。一种情况时当列有一个NULL值（也就是\N）时；另一种情况是（仅对于第一个TIMESTAMP列），当一个字段清单被指定时，TIMESTAMP列会从字段清单中被略去。

LOAD DATA INFILE把所有的输入值当作字符串，所以您不能按照使用INSERT语句的方式使用ENUM或SET列的数字值。所有的ENUM和SET值必须被指定为字符串。

当LOAD DATA INFILE语句结束时，会按以下格式返回一个信息字符串：

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

如果您正在使用C API，您可以通过调用mysql_info()函数获取有关语句的信息。请参见25.2.3.34节，“mysql_info()”。

当值通过INSERT语句被插入时或出现相同情况时，会发生警告（见13.2.4节，“INSERT语法”）。例外情况是，当输入行中字段过多或过少时，LOAD DATA INFILE也生成警告。这些警告并不存储；警告的数量只用于指示运行是否良好。

您可以使用SHOW WARNINGS来得到第一批max_error_count警告的清单，作为有关运行错误的信息。请参见13.5.4.22节，“SHOW WARNINGS语法”。

REPLACE [LOW_PRIORITY | DELAYED]

    [INTO] tbl_name [(col_name,...)]

    VALUES ({expr | DEFAULT},...),(...),...

或：

REPLACE [LOW_PRIORITY | DELAYED]

    [INTO] tbl_name

    SET col_name={expr | DEFAULT}, ...

或：

REPLACE [LOW_PRIORITY | DELAYED]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

REPLACE的运行与INSERT很相像。只有一点除外，如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。请参见13.2.4节，“INSERT语法”。

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。

所有列的值均取自在REPLACE语句中被指定的值。所有缺失的列被设置为各自的默认值，这和INSERT一样。您不能从当前行中引用值，也不能在新行中使用值。如果您使用一个例如“SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。

为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。

REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。

受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。

如果您正在使用C API，则可以使用mysql_affected_rows()函数获得受影响的行数。

目前，您不能在一个子查询中，向一个表中更换，同时从同一个表中选择。

以下是所用算法的更详细的说明（该算法也用于LOAD DATA...REPLACE）：

1.    尝试把新行插入到表中

2.    当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时：

a.    从表中删除含有重复关键字值的冲突行

b.    再次尝试把新行插入到表中

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [STRAIGHT_JOIN]

      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr, ...

    [INTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name']

    [FROM table_references

    [WHERE where_definition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT用于恢复从一个或多个表中选择的行，并可以加入UNION语句和子查询。请参见13.2.7.2节，“UNION语法
”和13.2.8节，“Subquery语法”。

·         每个select_expr都指示一个您想要恢复的列。

·         table_references指示行从哪个表或哪些表中被恢复。在13.2.7.1节，“JOIN语法”中对该语法进行了说明。

·         where_definition包括关键词WHERE，后面接一个表达式。该表达式指示被选择的行必须满足的条件。

有的行在计算时未引用任何表。SELECT也可以用于恢复这类行。

举例说明：

mysql> SELECT 1 + 1;

        -> 2

所有被使用的子句必须按语法说明中显示的顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。

·         使用AS alias_name可以为select_expr给定一个别名。此别名用作表达式的列名，可以用于GROUP BY、ORDER BY或HAVING子句。例如：

·                mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name

·                    -> FROM mytable ORDER BY full_name;

在为select_expr给定别名时，AS关键词是自选的。前面的例子可以这样编写：

mysql> SELECT CONCAT(last_name,', ',first_name) full_name

    -> FROM mytable ORDER BY full_name;

因为AS是自选的，如果您忘记在两个select_expr表达式之间加逗号，则会出现一个小问题：MySQL会把第二个表达式理解为一个别名。例如，在以下语句中，columnb被作为别名对待：

mysql> SELECT columna columnb FROM mytable;

因此，使用AS明确地指定列的别名，把它作为习惯，是一个良好的操作规范。

·         在一个WHERE子句中使用列别名是不允许的，因为当执行WHERE子句时，列值可能还没有被确定。请参见A.5.4节，“与列别名有关的问题”。

·         FROM table_references子句指示行从哪些表中被恢复。如果您命名的表多于一个，则您在进行一个联合操作。要了解有关联合语法的说明，请参见13.2.7.1节，“JOIN语法”。对于每一个被指定的表，您可以自选地指定一个别名。

·                tbl_name [[AS] alias]

·                    [{USE|IGNORE|FORCE} INDEX (key_list)]

使用USE INDEX、IGNORE INDEX、FORCE INDEX可以向优化符提示如何选择索引。这部分内容在13.2.7.1节，“JOIN语法”中进行了讨论。

您可以使用SET max_seeks_for_key=value作为一种替代方法，来促使MySQL优先采用关键字扫描，替代表扫描。

·         您可以把当前数据库中的一个表作为tbl_name（在当前数据库中）引用，或作为db_name.tbl_name引用，来明确地指定一个数据库。您可以把一列作为col_name, tbl_name.col_name引用或作为db_name.tbl_name.col_name引用。您不需要对一个列引用指定一个tbl_name或db_name.tbl_name前缀，除非此引用意义不明确。意义不明确时，要求指定明确的列引用格式。有关示例见9.2节，“数据库、表、索引、列和别名”。

·         在没有表被引用的情况下，允许您指定DUAL作为一个假的表名。

·                mysql> SELECT 1 + 1 FROM DUAL;

·                        -> 2

有些服务器要求一个FROM子句。DUAL仅用于与这些服务器兼容。如果没有表被引用，则MySQL不要求该子句，前面的语句可以按以下方法编写：

mysql> SELECT 1 + 1;

        -> 2

·         使用tbl_name AS alias_name或tbl_name alias_name可以为一个表引用起别名：

·                mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2

·                    ->     WHERE t1.name = t2.name;

·                mysql> SELECT t1.name, t2.salary FROM employee t1, info t2

·                    ->     WHERE t1.name = t2.name;

·         在WHERE子句中，您可以使用MySQL支持的所有函数，不过总计（总结）函数除外。请参见第12章：函数和操作符。

·         被选择的用于输出的列可以使用列名称、列别名或列位置被引用到ORDER BY和GROUP BY子句中。列位置为整数，从1开始：

·                mysql> SELECT college, region, seed FROM tournament

·                    ->     ORDER BY region, seed;

·                mysql> SELECT college, region AS r, seed AS s FROM tournament

·                    ->     ORDER BY r, s;

·                mysql> SELECT college, region, seed FROM tournament

·                    ->     ORDER BY 2, 3;

要以相反的顺序进行分类，应把DESC（降序）关键字添加到ORDER BY子句中的列名称中。默认值为升序；该值可以使用ASC关键词明确地指定。

不建议使用列位置，因为该语法已经从SQL标准中删除。

·         如果您使用GROUP BY，则输出行根据GROUP BY列进行分类，如同您对相同的列进行了ORDER BY。MySQL对GROUP BY进行了扩展，因此您可以在各列（在子句中进行命名）的后面指定ASC和DESC：

·                SELECT a, COUNT(b) FROM test_table GROUP BY a DESC

·         MySQL对GROUP BY的使用进行了扩展，允许选择在GROUP BY子句中没有被提到的字段。如果您没有得到预期的结果，请阅读GROUP BY的说明，请参见12.10节，“与GROUP BY子句同时使用的函数和修改程序”。

·         GROUP BY允许一个WITH ROLLUP修饰符。请参见12.10.2节，“GROUP BY修改程序”。

·         HAVING子句基本上是最后使用，只位于被发送给客户端的条目之前，没有进行优化。（LIMIT用于HAVING之后。）

SQL标准要求HAVING必须引用GROUP BY子句中的列或用于总计函数中的列。不过，MySQL支持对此工作性质的扩展，并允许HAVING因为SELECT清单中的列和外部子查询中的列。

如果HAVING子句引用了一个意义不明确的列，则会出现警告。在下面的语句中，col2意义不明确，因为它既作为别名使用，又作为列名使用：

mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

标准SQL工作性质具有优先权，因此如果一个HAVING列名既被用于GROUP BY，又被用作输出列清单中的起了别名的列，则优先权被给予GROUP BY列中的列。

·         HAVING不能用于应被用于WHERE子句的条目。例如，不能编写如下语句：

·                mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;

而应这么编写：

mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;

·         HAVING子句可以引用总计函数，而WHERE子句不能引用：

·                mysql> SELECT user, MAX(salary) FROM users

·                    ->     GROUP BY user HAVING MAX(salary)>10;

（在有些较早版本的MySQL中，本语句不运行。）

·         LIMIT子句可以被用于限制被SELECT语句返回的行数。LIMIT取一个或两个数字自变量，自变量必须是非负的整数常数（当使用已预备的语句时除外）。

使用两个自变量时，第一个自变量指定返回的第一行的偏移量，第二个自变量指定返回的行数的最大值。初始行的偏移量为0（不是1）：

mysql> SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

为了与PostgreSQL兼容，MySQL也支持LIMIT row_count OFFSET offset语法。

如果要恢复从某个偏移量到结果集合的末端之间的所有的行，您可以对第二个参数是使用比较大的数。本语句可以恢复从第96行到最后的所有行：

mysql> SELECT * FROM tbl LIMIT 95,18446744073709551615;

使用1个自变量时，该值指定从结果集合的开头返回的行数：

mysql> SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

换句话说，LIMIT n与LIMIT 0,n等价。

对于已预备的语句，您可以使用位置保持符。以下语句将从tb1表中返回一行：

mysql> SET @a=1;

mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";

mysql> EXECUTE STMT USING @a;

以下语句将从tb1表中返回第二到第六行：

mysql> SET @skip=1; SET @numrows=5;

mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";

mysql> EXECUTE STMT USING @skip, @numrows;

·         SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。file_name不能是一个原有的文件。原有文件会阻止例如“/etc/passwd”的文件和数据库表被销毁。

SELECT...INTO OUTFILE语句的主要作用是让您可以非常快速地把一个表转储到服务器机器上。如果您想要在服务器主机之外的部分客户主机上创建结果文件，您不能使用SELECT...INTO OUTFILE。在这种情况下，您应该在客户主机上使用比如“mysql –e "SELECT ..." > file_name”的命令，来生成文件。

SELECT...INTO OUTFILE是LOAD DATA INFILE的补语；用于语句的exort_options部分的语法包括部分FIELDS和LINES子句，这些子句与LOAD DATA INFILE语句同时使用。请参见13.2.5节，“LOAD DATA INFILE语法”。

FIELDS ESCAPED BY用于控制如何写入特殊字符。如果FIELDS ESCAPED BY字符不是空字符，则被用于在输出中对以下字符设前缀：

o        FIELDS ESCAPED BY字符

o        FIELDS [OPTIONALLY] ENCLOSED BY字符

o        FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符

o        ASCII 0（在编写时接在转义符后面的是ASCII ‘0’，而不是一个零值字节）

如果FIELDS ESCAPED BY字符是空字符，则没有字符被转义，并且NULL被作为NULL输出，而不是作为\N输出。指定一个空的转义符不是一个好的主意。特别是当您的数据中的字段值包含刚被给予的清单中的字符时，更是如此。

其原因是您必须对所有FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY或LINES TERMINATED BY字符进行转义，才能可靠地读取文件并返回。ASCII NUL被转义，以便更容易地使用调页程序观看。

生成的文件不必符合SQL语法，所以没有其它的字符需要被转义。

在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

FROM test_table;

·         如果您使用INTO DUMPFILE代替INTO OUTFILE，则MySQL只把一行写入到文件中，不对任何列或行进行终止，也不执行任何转义处理。如果您想要把一个BLOB值存储到文件中，则这个语句是有用的。

·         注释：任何由INTO OUTFILE或INTO DUMPFILE创建的文件都可以被服务器主机上的所有用户编写。原因是，MySQL服务器不能创建这样的文件，即文件的所有者不是该文件运行时所属的用户（任何时候，您都不能出于此原因或出于其它原因把mysqld作为根段运行）。该文件必须是全局可写的，这样您就可以操作其中的内容。

·         有的过程应在结果集合内处理数据。PROCEDURE子句用于对这些过程进行命名。要了解示例，请参见27.3.1节，“步骤分析”。

·         存储引擎使用页面或行锁。如果您对存储引擎使用FOR UPDATE，则受到查询检验的行会被进行写锁定，直到当前事务结束为止。使用LOCK IN SHARE MODE可以设置一个共享锁。共享锁可以防止其它事务更新或删除被检验的行。请参见15.2.10.5节，“锁定读取SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE”。

在SELECT关键词的后面，您可以使用许多选项。这些选项可以影响语句的运行。

ALL, DISTINCT和DISTINCTROW选项指定是否重复行应被返回。如果这些选项没有被给定，则默认值为ALL（所有的匹配行被返回）。DISTINCT和DISTINCTROW是同义词，用于指定结果集合中的重复行应被删除。

HIGH_PRIORITY, STRAIGHT_JOIN和以SQL_为开头的选项是MySQL相对于标准SQL的扩展。

·         HIGH_PRIORITY给予SELECT更高的优先权，高于用于更新表的语句。您应该只对查询使用HIGH_PRIORITY。查询速度非常快，而且立刻被执行。SELECT HIGH_PRIORITY查询在表被锁定用于读取时被发出。即使有一个新的语句正在等待表变为空闲，查询也会运行。

HIGH_PRIORITY不能和SELECT语句同时使用。SELECT语句是UNION的一部分。

·         STRAIGHT_JOIN用于促使优化符把表联合在一起，顺序按照这些表在FROM子句中排列的顺序。如果优化符联合表时顺序不佳，您可以使用STRAIGHT_JOIN来加快查询的速度。请参见7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。STRAIGHT_JOIN也可以被用于table_references清单中。请参见13.2.7.1节，“JOIN语法”。

·         SQL_BIG_RESULT可以与GROUP BY或DISTINCT同时使用，来告知优化符结果集合有很多行。在这种情况下，MySQL直接使用以磁盘为基础的临时表（如果需要的话）。在这种情况下，MySQL还会优先进行分类，不优先使用临时表。临时表对于GROUP BY组分带有关键字。

·         SQL_BUFFER_RESULT促使结果被放入一个临时表中。这可以帮助MySQL提前解开表锁定，在需要花费较长时间的情况下，也可以帮助把结果集合发送到客户端中。

·         SQL_SMALL_RESULT可以与GROUP BY或DISTINCT同时使用，来告知优化符结果集合是较小的。在此情况下，MySAL使用快速临时表来储存生成的表，而不是使用分类。在MySQL 5.1中，通常不需要这样。

·         SQL_CALC_FOUND_ROWS告知MySQL计算有多少行应位于结果集合中，不考虑任何LIMIT子句。行的数目可以使用SELECT FOUND_ROWS()恢复。请参见12.9.3节，“信息函数”。

·         如果您正在使用一个query_cache_type值，值为2或DEMAND，则SQL_CACHE告知MySQL把查询结果存储在查询缓存中。对于使用UNION的查询或子查询，本选项会影响查询中的所有SELECT。请参见5.13节，“MySQL查询高速缓冲”。

·         SQL_NO_CACHE告知MySQL不要把查询结果存储在查询缓存中。请参见5.13节，“MySQL查询高速缓冲”。对于一个使用UNION或子查询的查询，本选项会影响查询中的SELECT。

table_references:
    table_reference [, table_reference] …

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

·                mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2

·                    ->        WHERE t1.name = t2.name;

·                mysql> SELECT t1.name, t2.salary FROM employee t1, info t2

·                    ->        WHERE t1.name = t2.name;

·                mysql> SELECT table1.* FROM table1

·                    ->        LEFT JOIN table2 ON table1.id=table2.id

·                    ->        WHERE table2.id IS NULL;

·                a LEFT JOIN b USING (c1,c2,c3)

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id

    ->          LEFT JOIN table3 ON table2.id=table3.id;

mysql> SELECT * FROM table1 USE INDEX (key1,key2)

    ->          WHERE key1=1 AND key2=2 AND key3=3;

mysql> SELECT * FROM table1 IGNORE INDEX (key3)

    ->          WHERE key1=1 AND key2=2 AND key3=3;

CREATE TABLE t1 (i INT, j INT);

CREATE TABLE t2 (k INT, j INT);

INSERT INTO t1 VALUES(1,1);

INSERT INTO t2 VALUES(1,1);

SELECT * FROM t1 NATURAL JOIN t2;

SELECT * FROM t1 JOIN t2 USING (j);

+------+------+------+------+

| i    | j    | k    | j    |

+------+------+------+------+

|    1 |    1 |    1 |    1 |

+------+------+------+------+

+------+------+------+------+

| i    | j    | k    | j    |

+------+------+------+------+

|    1 |    1 |    1 |    1 |

+------+------+------+------+

+------+------+------+

| j    | i    | k    |

+------+------+------+

|    1 |    1 |    1 |

+------+------+------+

+------+------+------+

| j    | i    | k    |

+------+------+------+

|    1 |    1 |    1 |

+------+------+------+

·                SELECT … FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

SELECT … FROM t1, t2, t3

  WHERE t1.b = t2.b AND t2.c = t3.c;

SELECT … FROM t1, t2, t3

  WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;

CREATE TABLE t1 (i1 INT, j1 INT);

CREATE TABLE t2 (i2 INT, j2 INT);

CREATE TABLE t3 (i3 INT, j3 INT);

INSERT INTO t1 VALUES(1,1);

INSERT INTO t2 VALUES(1,1);

INSERT INTO t3 VALUES(1,1);

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

CREATE TABLE t1 (i1 INT);

CREATE TABLE t2 (i2 INT);

CREATE TABLE t3 (i3 INT);

SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

·                a LEFT JOIN b USING (c1,c2,c3)

·                a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

o                     COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)

a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

(SELECT a FROM tbl_name WHERE a=10 AND B=1)

UNION

(SELECT a FROM tbl_name WHERE a=11 AND B=2)

ORDER BY a LIMIT 10;

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)

UNION

(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);

+---------------+

| REPEAT('a',1) |

+---------------+

| a             |

| bbbbbbbbbb    |

+---------------+

子查询是另一个语句中的一个SELECT语句。

MySQL支持SQL标准要求的所有子查询格式和操作，也支持MySQL特有的几种特性。

以下是一个子查询的例子：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

在本例中，SELECT * FROM t1...是外部查询（或外部语句）,（SELECT column1 FROM t2）是子查询。我们可以说子查询嵌套在外部查询中。实际上，子查询也可以嵌套在其它子查询中，嵌套程度可以很深。子查询必须要位于圆括号中。

子查询的主要优势为：

·         子查询允许结构化的查询，这样就可以把一个语句的每个部分隔离开。

·         有些操作需要复杂的联合和关联。子查询提供了其它的方法来执行这些操作。

·         在许多人看来，子查询是可读的。实际上，子查询给予人们调用早期SQL“结构化查询语言”的原本的想法，这是子查询的创新。

以下是一个示例语句。该语句显示了有关子查询语法的要点。子查询语法由SQL标准指定并被MySQL支持。

DELETE FROM t1

WHERE s11 > ANY

(SELECT COUNT(*)  FROM t2

WHERE NOT EXISTS

(SELECT * FROM t3

WHERE ROW(5*t2.s1,77)=

(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM

(SELECT * FROM t5) AS t5)));

一个子查询会返回一个标量（单一值）、一个行、一个列或一个表（一行或多行及一列或多列）。这些子查询被称为标量、列、行和表子查询。可返回一个特定种类结果的子查询经常只被用于特定的语境中，在后面各节中有说明。

有些语句可以使用子查询。对这些语句的类型基本没有限定。子查询可以包括普通SELECT可以包括的任何关键词或子句：DISTINCT, GROUP BY, ORDER BY, LIMIT, 联合, 索引提示, UNION结构化, 评注和函数等。

有一个限定是，一个子查询的外部语句必须是以下语句之一：SELECT, INSERT, UPDATE, DELETE, SET或DO。还有一个限定是，目前，您不能在一个子查询中修改一个表，又在同一个表中选择。这适用于DELETE, INSERT, REPLACE和UPDATE语句。在附录I：特性限制 中给出了对子查询使用的更综合的讨论。

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);

INSERT INTO t1 VALUES(100, 'abcde');

SELECT (SELECT s2 FROM t1);

CREATE TABLE t1 (s1 INT);

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (s1 INT);

INSERT INTO t2 VALUES (2);

SELECT (SELECT s1 FROM t2) FROM t1;

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

non_subquery_operand comparison_operator (subquery)

=  >  <  >=  <=  <>

  ... 'a' = (SELECT column1 FROM t1)

SELECT column1 FROM t1

WHERE column1 = (SELECT MAX(column2) FROM t2);

SELECT * FROM t1 AS t

WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

operand comparison_operator ANY (subquery)

operand IN (subquery)

operand comparison_operator SOME (subquery)

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

operand comparison_operator ALL (subquery)

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);

SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

  SELECT * FROM t1 WHERE (column1,column2) = (1,1);

  SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

SELECT column1,column2,column3

FROM t1

WHERE (column1,column2,column3) IN

(SELECT column1,column2,column3 FROM t2);

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

·                SELECT DISTINCT store_type FROM stores

·                  WHERE EXISTS (SELECT * FROM cities_stores

·                                WHERE cities_stores.store_type = stores.store_type);

·                SELECT DISTINCT store_type FROM stores

·                  WHERE NOT EXISTS (SELECT * FROM cities_stores

·                                    WHERE cities_stores.store_type = stores.store_type);

·                SELECT DISTINCT store_type FROM stores s1

·                  WHERE NOT EXISTS (

·                    SELECT * FROM cities WHERE NOT EXISTS (

·                      SELECT * FROM cities_stores

·                       WHERE cities_stores.city = cities.city

·                       AND cities_stores.store_type = stores.store_type));

SELECT * FROM t1 WHERE column1 = ANY

(SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

SELECT column1 FROM t1 AS x

WHERE x.column1 = (SELECT column1 FROM t2 AS x

WHERE x.column1 = (SELECT column1 FROM t3

WHERE x.column2 = t3.column1));

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

SELECT ... FROM (subquery) [AS] name ...

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

INSERT INTO t1 VALUES (1,'1',1.0);

INSERT INTO t1 VALUES (2,'2',2.0);

SELECT sb1,sb2,sb3

FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb

WHERE sb1 > 1;

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

SELECT AVG(sum_column1)

FROM (SELECT SUM(column1) AS sum_column1

FROM t1 GROUP BY column1) AS t1;

·                ERROR 1241 (ER_OPERAND_COL)

·                SQLSTATE = 21000

·                Message = "Operand should contain 1 column(s)"

SELECT (SELECT column1, column2 FROM t2) FROM t1;

·                ERROR 1242 (ER_SUBSELECT_NO_1_ROW)

·                SQLSTATE = 21000

·                Message = "Subquery returns more than 1 row"

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

·                Error 1093 (ER_UPDATE_TABLE_USED)

·                SQLSTATE = HY000

·                Message = "You can't specify target table 'x'

·                for update in FROM clause"

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

·                SELECT * FROM t1 WHERE t1.column1 IN

·                (SELECT column1 FROM t2 ORDER BY column1);

·                SELECT * FROM t1 WHERE t1.column1 IN

·                (SELECT DISTINCT column1 FROM t2);

·                SELECT * FROM t1 WHERE EXISTS

·                (SELECT * FROM t2 LIMIT 1);

·                SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (

·                SELECT column1 FROM t2);

SELECT DISTINCT t1.column1 FROM t1, t2

WHERE t1.column1 = t2.column1;

·                SELECT * FROM t1

·                WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

SELECT * FROM t1

WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

·                SELECT * FROM t1

·                WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

SELECT * FROM t1

WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1

AND t2.column2=t1.column2);

·                SELECT * FROM t1 WHERE t1.col_name

·                = (SELECT a FROM t2 WHERE b = some_const);

SELECT * FROM t1 WHERE t1.col_name

IN (SELECT a FROM t2 WHERE b = some_const);