MySQL 기초 튜토리얼 14 - SQL 구문 데이터 정의문 DDL

1. ALTER DATABASE 구문

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 절은 기본 데이터베이스 순서를 변경하는 데 사용됩니다.

데이터베이스 이름은 무시해도 됩니다. 이때 해당 문은 기본 데이터베이스에 해당합니다. ALTER SCHEMA를 사용할 수도 있습니다.

2. ALTER TABLE 구문

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 등이 포함됩니다.

스토리지 엔진이 일부 작업을 지원하지 않습니다. 이러한 작업을 수행하면 경고가 나타납니다. 이러한 경고는 SHOW WARNINGS를 사용하여 표시할 수 있습니다.

ALTER TABLE을 사용하여 열 사양을 변경했지만 DESCRIBE tbl_name에서 열 사양이 변경되지 않았다는 메시지가 표시되는 경우 MySQL이 변경 사항을 무시했기 때문일 수 있습니다. 예를 들어, VARCHAR 열을 CHAR 열로 변경하려고 하면 테이블에 다른 가변 길이 열이 포함되어 있으면 MySQL은 계속 VARCHAR을 사용합니다.

ALTER TABLE이 실행되면 원본 테이블을 임시로 복사하고 복사본을 변경한 다음 원본 테이블을 삭제하고 새 테이블의 이름을 바꿉니다. ALTER TABLE을 실행하면 다른 사용자가 원본 테이블을 읽을 수 있지만 새 테이블이 생성될 때까지 테이블 업데이트 및 수정이 지연됩니다. 새 테이블이 생성된 후 이러한 업데이트 및 수정 사항은 자동으로 새 테이블로 전송됩니다.

ALTER TABLE을 실행할 때 RENAME 이외의 옵션을 사용하면 MySQL이 임시 테이블을 생성한다는 점에 유의하세요. MySQL은 데이터를 복사할 필요가 없는 경우에도(예: 열 이름을 변경하는 경우) 이 작업을 수행합니다. MyISAM 테이블의 경우 myisam_sort_buffer_size 시스템 변수를 더 높은 값으로 설정하면 인덱스 재생성 속도를 높일 수 있습니다(이 작업은 변경 프로세스에서 가장 느린 부분입니다).

· ALTER TABLE을 사용하려면 테이블에 대한 ALTER, INSERT 및 CREATE 권한을 얻어야 합니다.

· IGNORE는 표준 SQL과 관련된 MySQL의 확장입니다. 새 테이블에 중복된 키워드가 있거나 STRICT 모드 시작 시 경고가 발생하는 경우 IGNORE를 사용하여 ALTER TABLE의 동작을 제어합니다. IGNORE를 지정하지 않은 경우 중복 키워드 오류가 발생하면 복사 작업을 포기하고 이전 단계로 돌아갑니다. IGNORE를 지정하면 중복 키워드가 있는 행에 대해 첫 번째 행만 사용되며 충돌하는 다른 행은 삭제됩니다. 또한 오류 값을 수정하여 가능한 한 올바른 값에 가깝게 만듭니다.

· 하나의 ALTER TABLE 문에 여러 개의 ADD, ALTER, DROP 및 CHANGE 절을 쉼표로 구분하여 작성할 수 있습니다. 이는 표준 SQL과 관련된 MySQL의 확장입니다. 표준 SQL에서는 각 ALTER TABLE 문에서 각 절이 한 번만 허용됩니다. 예를 들어, 하나의 명령문에서 여러 열을 취소하려면:

mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

· CHANGE col_name, DROP col_name 및 DROP INDEX는 표준 SQL에 상대적인 MySQL의 확장입니다.

· MODIFY는 ALTER TABLE에 대한 Oracle의 확장입니다.

· COLUMN은 선택항목이므로 무시해도 됩니다.

· ALTER TABLE tbl_name RENAME TO new_tbl_name을 사용하고 다른 옵션이 없는 경우 MySQL은 tabletbl_name 이름을 바꿉니다. 임시 테이블을 만들 필요가 없습니다. (RENAME TABLE 문을 사용하여 테이블 이름을 바꿀 수도 있습니다.)

·

column_definition 절은 CREATE TABLE의 ADD 및 CHANGE 절과 동일한 구문을 사용합니다. 이 구문에는 열 유형뿐만 아니라 열 이름도 포함됩니다.

· 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会指定一个默认值。

· DROP INDEX用于取消索引。这是MySQL相对于标准SQL的扩展。

· 如果列从表中被取消了，则这些列也从相应的索引中被取消。如果组成一个索引的所有列均被取消，则该索引也被取消。

· 如果一个表只包含一列，则此列不能被取消。如果您想要取消表，应使用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 ... (...)。对于其它存储引擎，这些子句会被分析，但是会被忽略。对于所有的存储引擎，CHECK子句会被分析，但是会被忽略。接受这些子句但又忽略子句的原因是为了提高兼容性，以便更容易地从其它SQL服务器中导入代码，并运行应用程序，创建带参考数据的表。

· InnoDB支持使用ALTER TABLE来取消外键：

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

· 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时）有多少记录由于重复关键字的原因已被删除。

· ALTER TABLE也可以用于对带分区的表进行重新分区，功能包括添加、取消、合并和拆分各分区，还可以用于进行分区维护。

对带分区的表使用partition_options子句和ALTER TABLE可以对表进行重新分区，使用时依据partition_options定义的分区方法。本子句以PARTITION BY为开头，然后使用与用于CREATE TABLE的partition_options子句一样的语法和规则。注释：MySQL 5.1服务器目前接受此语法，但是不实际执行；等MySQL 5.1开发出来后，将执行此语法。

用于ALTER TABLE ADD PARTITION的partition_definition子句支持用于CREATE TABLE语句的partition_definition子句的同样名称的选项。例如，假设您有一个按照以下方式创建的带分区的表：

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等。

·         多个附加子句用于提供分区维护和修补功能。这些功能与用于非分区表的功能类似。这些功能由CHECK TABLE和REPAIR TABLE等命令（这些命令不支持用于分区表）执行。这些子句包括ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION和REPAIR PARTITION.每个选项均为一个partition_names子句，包括一个或多个分区名称。需要更改的表中必须已存在这些分区。多个分区名称用逗号分隔。

以下例子展示了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表选项来设置。

如果值大于AUTO_INCREMENT列中的最大值，则您可以使用用于InnoDB表的ALTER TALBE...AUTO_INCREMENT=value表选项，来为新行设置序号。如果值小于列中当前的最大值，不会出现错误信息，当前的序列值也不改变。

使用MyISAM表时，如果您不更改AUTO_INCREMENT列，则序列号不受影响。如果您取消一个AUTO_INCREMENT列，然后添加另一个AUTO_INCREMENT列，则序号重新排列，从1开始。

3. CREATE DATABASE语法

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权限。

如果存在数据库，并且您没有指定IF NOT EXISTS，则会出现错误。

create_specification选项用于指定数据库的特性。数据库特性储存在数据库目录中的db.opt文件中。CHARACTER SET子句用于指定默认的数据库字符集。COLLATE子句用于指定默认的数据库整序。

有些目录包含文件，这些文件与数据库中的表对应。MySQL中的数据库的执行方法与这些目录的执行方法相同。因为当数据库刚刚被创建时，在数据库中没有表，所以CREATE DATABASE只创建一个目录。这个目录位于MySQL数据目录和db.opt文件之下。

如果您手动在数据目录之下创建一个目录（例如，使用mkdir），则服务器会认为这是一个数据库目录，并在SHOW DATABASES的输出中显示出来。

也可以使用CREATE SCHEMA。

您还可以使用mysqladmin程序创建数据库。

4. CREATE INDEX语法

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语句上，用于创建索引。

通常，当使用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不是保留词，因此会被认为是一个索引名称。

如果您指定的索引类型在给定的储存引擎中不合法，但是有其它的索引类型适合引擎使用，并且不会影响查询功能，则引擎应使用此类型。

FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引，并且只能在MyISAM表中编制。

SPATIAL索引只能对空间列编制索引，并且只能在MyISAM表中编制。

5. CREATE TABLE语法

• 5.1. 沉寂的列规格变更

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_typeindex_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 ACTIONtable_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权限。

默认的情况是，表被创建到当前的数据库中。如果表已存在，或者如果没有当前数据库，或者如果数据库不存在，则会出现错误。

表名称被指定为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	索引文件

· 如果没有指定是NULL或是NOT NULL，则列在创建时假定指定为NULL。

· 一个整数列可以拥有一个附加属性AUTO_INCREMENT。当您向一个已编入索引的AUTO_INCREMENT列中插入一个NULL值（建议）或0时，此列被设置为下一个序列的值。通常情况下为value+1，此处value是当前在表中的列的最大值。AUTO_INCREMENT序列从1开始。

为--sql-mode服务器选项或sql_mode系统变量指定NO_AUTO_VALUE_ON_ZERO特征位，这样可以把0存储到AUTO_INCREMENT列中，同时不生成一个新的序列值。

注释：有时候，每个表只有一个AUTO_INCREMENT列，此列必须编制索引，不能有DEFAULT值。一个AUTO_INCREMENT列只有在只包含正数的情况下，才能运行正常。插入一个负数会被认为是插入了一个非常大的正数。这样做是为了避免当数字由正数转为负数时出现精度问题，同时也为了确保AUTO_INCREMENT列中不会包含0。

对于MyISAM和BDB表，您可以在一个多列关键字中指定一个AUTO_INCREMENT次级列。

为了让MySQL与部分ODBC应用软件相兼容，您可以使用以下查询方法找到最后一个插入行的AUTO_INCREMENT值：

SELECT * FROM tbl_name WHERE auto_col IS NULL

· 字符列的定义可以包括一个CHARACTER SET属性，用来指定字符集，也可以指定列的整序。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为默认值。

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来查看表的索引名称。

· 部分存储引擎允许您在创建索引时指定索引类型。index_type指示语句的语法是USING type_name。

示例：

CREATE TABLE lookup
  (id INT, INDEX USING BTREE (id))
  ENGINE = MEMORY;

· 在MySQL 5.1中，只有MyISAM，InnoDB, BDB和MEMORY存储引擎支持在含有NULL值的列中编索引。在其它情况下，您必须定义已编索引的列为NOT NULL，否则会出现错误。

· 在一个索引规约中使用col_name(length)语法，您可以创建一个索引，此索引只使用一个CHAR或VARCHAR列的第一个length字符。只对列值的前缀编制索引可以使索引文件大大减小。

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系统变量进行指示。

· 您可以创建特殊的FULLTEXT索引，用于全文搜索。只有MyISAM表类型支持FULLTEXT索引。FULLTEXT索引只可以从CHAR, VARCHAR和TEXT列中创建。整个列都会被编入索引；不支持对部分列编索引。如果已指定，前缀长度会被忽略。

· 您可以为空间列类型创建SPATIAL索引。只有MyISAM表支持空间类型，已编索引的列必须声明为NOT NULL。

· InnoDB表支持对外键限制条件进行检查。注意，在InnoDB中，FOREIGN KEY语法比本节开始时介绍的CREATE TABLE语句的语法更严格：被引用的表中的列必须有明确的命名。InnoDB支持外键的ON DELETE和ON UPDATE两种操作。

对于其它存储引擎，MySQL服务器对CREATE TABLE语句中的FOREIGN KEY和REFERENCES语法进行分析，但不采取进一步的行动。所有的存储引擎均对CHECK子句进行分析，但是忽略CHECK子句。

· 对于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	档案存储引擎。请参见“ARCHIVE存储引擎”。
BDB	带页面锁定的事务安全表。也称为BerkeleyDB。请参见“BDB (BerkeleyDB)存储引擎”。
CSV	值之间用逗号隔开的表。请参见“CSV存储引擎。
EXAMPLE	示例引擎。请参见“EXAMPLE存储引擎”。
FEDERATED	可以访问远程表的存储引擎。请参见“FEDERATED存储引擎”。
HEAP	见“MEMORY (HEAP)存储引擎”。
(OBSOLETE)ISAM	在MySQL 5.1中没有此引擎。如果您要从以前的版本升级到MySQL 5.1，您应该在进行升级前把原有的ISAM表转换为MyISAM表。请参见存储引擎和表类型。
InnoDB	带行锁定和外键的事务安全表。请参见“InnoDB存储引擎”。
MEMORY	本表类型的数据只保存在存储器里。（在早期MySQL版本中被称为HEAP。）
MERGE	MyISAM表的集合，作为一个表使用。也称为MRG_MyISAM。请参见“MERGE存储引擎”。
MyISAM	二进制轻便式存储引擎，此引擎是MySQL所用的默认存储引擎。请参见“MyISAM存储引擎”。
NDBCLUSTER	成簇表，容错表，以存储器为基础的表。也称为NDB。请参见MySQL簇。

지정된 스토리지 엔진을 사용할 수 없는 경우 MySQL은 대신 MyISAM을 사용합니다. 예를 들어 테이블 정의에 ENGINE=BDB 옵션이 포함되어 있지만 MySQL 서버가 BDB 테이블을 지원하지 않는 경우 해당 테이블은 MyISAM 테이블로 생성됩니다. 이렇게 하면 마스터에 트랜잭션 테이블이 있는 경우 복제를 설정할 수 있지만 속도를 위해 슬레이브에 비대화형 테이블을 생성할 수 있습니다. MySQL 5.1에서는 스토리지 엔진 규칙을 따르지 않으면 경고가 표시됩니다.

다른 테이블 옵션은 테이블 속성을 최적화하는 데 사용됩니다. 대부분의 경우 테이블 옵션을 지정할 필요가 없습니다. 이러한 옵션은 달리 명시되지 않는 한 모든 스토리지 엔진에 적용됩니다.

· AUTO_INCREMENT

테이블의 초기 AUTO_INCREMENT 값입니다. MySQL 5.1에서 이 옵션은 MyISAM 및 MEMORY 테이블에만 적용됩니다. InnoDB도 이 옵션을 지원합니다. 엔진이 AUTO_INCREMENT 테이블 옵션을 지원하지 않는 경우 엔진의 첫 번째 자동 증가 값을 설정하려면 "false" 행을 삽입해야 합니다. 해당 행의 값은 테이블 생성 후의 값보다 1이 작아진 후 가짜 행이 삭제됩니다.

CREATE TABLE 문에서 AUTO_INCREMENT 테이블 옵션을 지원하는 엔진의 경우 ALTER TABLE tbl_nameAUTO_INCREMENT = n을 사용하여 AUTO_INCREMENT 값을 재설정할 수도 있습니다.

·AVG_ROW_LENGTH

테이블의 대략적인 평균 행 길이입니다. 이 설정은 가변 크기 레코드가 있는 대형 테이블에만 필요합니다.

MyISAM 테이블을 생성할 때 MySQL은 MAX_ROWS 및 AVG_ROW_LENGTH 옵션의 곱을 사용하여 결과 테이블의 크기를 결정합니다. 하나의 옵션을 지정하지 않을 경우 최대 테이블 크기는 65,536TB의 데이터입니다. (운영 체제에서 이러한 큰 파일을 지원하지 않는 경우 테이블 크기는 운영 체제의 제한으로 제한됩니다.) 인덱스가 더 작고 빠르도록 포인터 크기를 줄이고 큰 파일이 필요하지 않은 경우, 그런 다음 myisam_data_pointer_size 시스템 변수를 설정하여 기본 포인터 크기를 줄일 수 있습니다. 모든 테이블을 기본 제한 이상으로 늘리고 테이블을 약간 더 느리고 약간 더 크게 만들고 싶다면 이 변수를 설정하여 기본 포인터 크기를 늘릴 수 있습니다.

· [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를 사용하지 않는 경우 기본 작업은 문자열만 압축하고 숫자는 압축하지 않는 것입니다.

이진 숫자 키워드를 압축할 때 MySQL은 접두사 압축을 사용합니다.

o는 키워드당 하나가 필요합니다. 이전 키의 바이트 수가 다음 키와 동일함을 나타내는 추가 바이트가 저장됩니다. 압축 효과를 위해 빅엔디안 순서로 키 뒤에 옵니다.

이는 연속된 두 줄에 동일한 키워드가 많은 경우 후속 "동일한" 키워드는 일반적으로 2바이트만 차지한다는 의미입니다. 이를 후속 키가 Storage_size_for_key + 포인터_크기(포인터 크기는 일반적으로 4)를 차지하는 일반적인 경우와 비교하십시오. 그러나 접두사 압축은 많은 숫자가 동일한 경우에만 이점이 있으므로 각 키워드는 NULL 값을 포함할 수 없습니다. 키워드는 1바이트를 더 사용합니다(이 경우 압축된 키워드의 길이를 저장하는 바이트와 키워드가 NULL인지 여부를 표시하는 데 사용되는 바이트는 동일한 바이트입니다.)

· PASSWORD

암호를 사용하여 .frm 파일을 암호화합니다. 표준 MySQL 버전에서는 이 옵션이 적용되지 않습니다.

· DELAY_KEY_WRITE

테이블이 닫힐 때까지 키워드 업데이트를 지연하려는 경우 , 1로 설정(MyISAM만 해당)

· ROW_FORMAT

각 행을 저장하는 방법을 정의합니다. 현재 이 옵션은 정적 행 또는 가변 길이 행의 경우에만 적용됩니다. 이 옵션 값은 FIXED 또는 DYNAMIC으로 유형을 COMPRESSED로 설정할 수 있습니다.

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

· RAID_TYPE

在MySQL 5.0中,RAID支持被删除了。要了解有关RAID的说明，请参见http://www.php.cn/。

· UNION

当您想要把一组相同的表当作一个表使用时，采用UNION。UNION仅适用于MERGE表。

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

· INSERT_METHOD

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

· DATA DIRECTORY, INDEX DIRECTORY

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

仅当您没有使用--skip-symbolic-links选项时，DATA DIRECTORY, INDEX DIRECTORY才能使用。操作系统必须有一个正在工作的、线程安全的realpath()调用。要了解全面信息，

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

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

CREATE TABLE t1 (col1 INT, col2 CHAR(5))
        PARTITION BY HASH(col1);

CREATE TABLE t1 (col1 INT, col2 CHAR(5))
        PARTITION BY HASH( ORD(col2) );

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
        PARTITION BY HASH ( YEAR(col3) );

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

PARTITION BY HASH使用expr被分区数目所除后的余数（也就是模数）。

LENEAR关键词需要一种不同的算法。在这种情况下，通过一次或多次逻辑AND运算得出的结果，计算出存储记录的分区的数目。

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

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
            PARTITION BY KEY(col3)
            PARTITIONS 4;

采用LINEAR关键词，您可以对由关键字分区的表进行线形分区。这与由HASH进行分区的表格有同样的效果；也就是说，使用&操作符查找分区数目，而不是使用模数。本示例采用了关键字线形分区，用来在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可以与一个文字值同时使用，或者与一个可以求算单一值的表达式同时使用。

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

分区编号：	年份范围：
0	1990以前
1	1991 - 1994
2	1995 - 1998
3	1999 - 2002
4	2003 - 2005
5	2006年以后

采用这种分区方法的表可以通过如下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子句。本子句用于确定哪些行将被存储到此分区中。

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

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

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

CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
        PARTITION BY LIST(YEAR(adate))
        (
             PARTITION p1999 VALUES IN (1995, 1999, 2003) DATA DIRECTORY = '/var/appdata/95/data' INDEX DIRECTORY = '/var/appdata/95/idx',
             PARTITION p2000 VALUES IN (1996, 2000, 2004) DATA DIRECTORY = '/var/appdata/96/data' INDEX DIRECTORY = '/var/appdata/96/idx',
             PARTITION p2001 VALUES IN (1997, 2001, 2005) DATA DIRECTORY = '/var/appdata/97/data' INDEX DIRECTORY = '/var/appdata/97/idx',
             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分区进行子分区。

· 分区可以修改、合并、添加到表中，或从表中删去。

您可以在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过程中进行联合插入。

5.1. 沉寂的列规格变更

在有些情况下，较早版本的MySQL会静默地更改在CREATE TABLE或ALTER TABLE语句中给定的列规约。在MySQL 5.1中不会进行这类变更。如果使用指定的数据类型无法创建列，则会出现错误。

6. DROP DATABASE语法

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

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

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

也可以使用DROP SCHEMA。

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

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

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

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

.BAK	.DAT	.HSH
.MRG	.MYD	.ISD
.MYI	.db	.frm

· 名称中包含两位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来取消文件。

7. DROP INDEX语法

DROP INDEX index_name ON tbl_name

DROP INDEX用于从表tbl_name中取消名称为index_name的索引。本语句被映射到一个ALTER TABLE语句中，用于取消索引。

8. DROP 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。

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

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

TEMPORARY关键词具有以下作用：

· 语句只取消TEMPORARY表。

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

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

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

9. RENAME TABLE语法

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

本语句用于对一个或多个表进行重命名。

重命名操作自动进行，这意味着当重命名正在运行时，其它线程不能读取任何表。例如，如果您有一个原有的表old_table，您可以创建另一个具有相同结构的空表new_table，然后用此空表替换原有的表：

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

如果此语句用于对多个表进行重命名，则重命名操作从左至右进行。如果您想要交换两个表的名称，您可以这样做（假设不存在名称为tmp_table的表）：

RENAME TABLE old_table TO tmp_table,
            new_table TO old_table,
            tmp_table TO new_table;

只要两个数据库位于同一文件系统中，您还可以对表进行重命名，把表从一个数据库中移动到另一个数据库中：

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

当您执行RENAME时，您不能有被锁定的表，也不能有处于活性状态的事务。您还必须拥有原表的ALTER和DROP权限，以及新表的CREATE和INSERT权限。

如果MySQL对多个表进行重命名时遇到了错误，MySQL会对所有已被重命名的表进行反向重命名，返回到原来的状态。

只要您不尝试通过重命名把视图加入另一个数据库中，则RENAME TABLE也可以用于视图。

 以上就是MySQL基础教程14 —— SQL语法之数据定义语句DDL的内容，更多相关内容请关注PHP中文网（www.php.cn）！