MySQL - CREATE TABLE Statement 翻译

原文链接

13.1.17 CREATE TABLE Statement

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | {INDEX|KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY}]
      [reference_definition]

data_type:
    (see Chapter 11, Data Types)

key_part:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
    table_option [[,] table_option] ...

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [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 [=] 'string' ]
        [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]

query_expression:
    SELECT ...   (Some valid select or union statement)

默认情况下，使用 InnoDB 存储类型在默认的数据库中创建表，若表已存在或没有默认数据库将会发生错误。

MySQL 对标的数量没有限制。底层文件系统可能对表示表的文件数量有限制。单个存储引擎可能会施加特定于引擎的约束。InnoDB 最多允许 40 亿个表。

有关表的物理表示信息，更多信息参阅《Files Created by CREATE TABLE》

创建表时，原始的 CREATE TABLE 语句由 MySQL 存储。更多信息参阅《CREATE TABLE Statement Retention》

表名

tbl_name
- 可以将表名指定为 db_name.db_name 在特定的数据库中创建表。如果数据库存在，那么无论是否存在默认数据库，它都可以工作。如果使用带引号的标识符，请分别引用数据库和表明例如，写 `mydb`.`mytbl` 而非 `mydb.mytbl`
- 允许表名的规则在《Schema Object Names”.》中给出
IF NOT EXISTS 防止在表存在时发生错误。但是，这并没有验证现有表是否具有与 CREATE TABLE 语句所指示的结构相同

临时表

你可以在创建表时添加关键字 TEMPORARY。临时表仅在当前会话中可见，并且在会话关闭时自动删除。更多信息参阅《CREATE TEMPORARY TABLE Statement》

表克隆和复制

LIKE
- 使用 CREATE TABLE ... LIKE 可以基于另一张表的定义创建新表。包括在原表中定义的任何列属性和索引
- 更多信息参阅《CREATE TABLE … LIKE Statement》

1	CREATE TABLE new_tbl LIKE orig_tbl;

[AS] query_expression
- 基于另一张表的数据创建一张表，请在 CREATE TABLE 语句的末尾添加一个 SELECT 语句
- 更多信息参阅《CREATE TABLE … SELECT Statement》

1	CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

IGNORE | REPLACE
- IGNORE 和 REPLACE 选线只是如何处理在 SELECT 语句复制表时复制唯一键值的行
- 更多信息参阅《CREATE TABLE … SELECT Statement》

列数据类型和属性

每张表有 4096 个列的硬性限制，但是对于给定的表，有效的最大值可能更小，参阅《Limits on Table Column Count and Row Size》

data_type
- data_type 表示列定义中的数据类型。有关指定列数据类型可用语法的完整描述，以及关于每种类型属性的信息，参阅《Chapter 11, Data Types》
- 有些属性并不适用于所有的数据类型，AUTO_INCREMENT 值适用于整数和付点类型。DEFAULT 不适用于 BLOB 或 TEXT 类型
- 字符类型数据（CHAR、VARCHAR、TEXT、ENUM、SET 及其它同义词）可以包含 CHARACTER SET 来声明列的字符集。CHARSET 是 CHARACTER SET 的同义词。字符集的排序规则可以用 COLLATE 和其他属性指定。更多信息参阅《Chapter 10, Character Sets, Collations, Unicode》
- MySQL 5.6 字符型列以字符作为长度单位，BINARY、VARBINARY 则以字节作为长度单位
- 对于 CHAR、VARCHAR、BINARY、VARBINARY 列，可创建仅使用列值的前导部分的索引，使用 col_name(length) 语法可以指定索引的前缀长度。BLOB 和 TEXT 列也可以被索引，但是必须给出前缀长度。非二进制字符串类型前缀长度单位为字符数，二进制字符串类型前缀长度为字节数。也就是说，索引项包含 CHAR、VARCHAR、TEXT 的每个列值开头的 length 个字符，以及 BINARY、VARBINARY、BLOB 列的每个列值开头的 length 个字节。只索引列值的前缀可以使索引文件小很多。有关索引的更多信息，参阅《CREATE INDEX Statement》
- 只有 InnoDB 和 MyISAM 存储引擎支持对 BLOB 和 TEXT 进行索引

1	CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

1	CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

NOT NULL | NULL
- 如果未指定 NOT NULL 或 NULL ，该列将被视为指定了 NULL
- 在 MySQL 5.6 中，只有 InnoDB、MyISAM 和 MEMORY 存储迎亲支持空值列上的索引。在其他情况下，必须将索引列声明为 NOT NULL 或错误结果
DEFALUT
- 指定列的默认值。有关列默认值处理的更多信息，参阅《Data Type Default Values》
AUTO_INCREMENT
- 整数或浮点列可以具有附件属性 AUTO_INCREMENT。在 AUTO_INCREMENT 列中插入 NULL（推荐）或 0 值时，该值会被设置为下一个序列值，这通常是 value + 1，其中 value 是表中当前列的最大值，AUTO_INCREMENT 序列从 1 开始
- 要在插入航之后检索 AUTO_INCREMENT 值，可以使用 LAST_INSERT_ID() SQL 函数或 mysql_insert_id() C API 函数，参阅《Information Functions》、《mysql_insert_id()》
- 如果启用了 NO_AUTO_VALUE_ON_ZERO SQL 模式，则可以将 0 存储在 AUTO_INCREMENT 列中，而不生成新的序列值
- 每个表只能有一个 AUTO_INCREMENT 列，它必须被索引，并且不能有默认值。只有当 AUTO_INCREMENT 列值包含正数时，它才能正常工作。插入一个负数被认为是插入一个非常大的正数。
- 对于 MyISAM 表，可以在多个列中指定 AUTO_INCREMENT 辅助列，参阅《Using AUTO_INCREMENT》
- 要使 MySQL 与某些 ODBC 应用程序兼容，可以使用一下查询找到最后插入行的 AUTO_INCREMENT 值；这个方法依赖于 sql_auto_is_null 变量不为 0

1	SELECT * FROM tbl_name WHERE auto_col IS NULL

COMMENT: 可以使用 COMMENT 选项指定列的注释，最多 1024 个字符。通过 SHOW CREATE TABLE 或 SHOW FULL COLUMNS 语句可以显示注释内容
COLUMN_FORMAT
- 在 NDB 集群中，还可以使用 COLUMN_FORMAT 为 DNB 表的各个列指定数据存储格式。允许的列格式有 FIXED、DYNAMIC、DEFAULT。FIXED 用于指定固定宽度的存储，DYNAMIC 允许列使用可变宽度的存储，DEFAULT 则由列的数据类型决定其是否可变
- NDB 表 COLUMN_FORMAT 的默认值为 DEFAULT
- 在 NDB 集群中，COLUMN_FORMAT=FIXED 定义的列最大可能的偏移量为 8188 字节。更多信息参阅《Limits Associated with Database Objects in NDB Cluster》
- COLUMN_FORMAT 目前对使用 NDB 以外的存储引擎的表的列没有影响。在 MySQL 5.6 及以后版本中，COLUMN_FORMAT 将被忽略
STORAGE
- 对于 NDB 表，可以使用 STORAGE 子句指定列是存储在磁盘或是内存中。分别用 DISK 和 MEMORY 声明。所使用的 CREATE TABLE 语句必须包含一个 TABLESPACE 子句
- 对于 NDB 表，STORAGE DEFAULT 等同于 STORAGE MEMORY
- 存储子句对使用 NDB 以外的存储引擎的表没有影响。STORAGE 关键词仅在 NDB 集群提供的 mysqld 构建中受支持；它在 MySQL 的任何其他版本中都无法被识别，因为任何使用 STORAGE 关键词的尝试都会导致语法错误

mysql> CREATE TABLE t1 (
    ->     c1 INT STORAGE DISK,
    ->     c2 INT STORAGE MEMORY
    -> ) ENGINE NDB;
ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)

mysql> CREATE TABLE t1 (
    ->     c1 INT STORAGE DISK,
    ->     c2 INT STORAGE MEMORY
    -> ) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.06 sec)

索引和外键

一些关键词适用于创建索引和外键，相关背景知识参阅《CREATE INDEX Statement》、《FOREIGN KEY Constraints》

CONSTRAINT symbol
- CONSTRAINT symbol 用来命名约束。如果没有给出子句，或者 CONSTRAINT 关键词后面没有包含 symbol，MySQL 会自动生成一个约束名。symbol（如果有）必须在每个 schema（数据库）中每个约束类型中唯一。重复的 symbol 会导致错误。参阅《Identifier Length Limits》
- SQL 标准制定的所有约束类型（主键、唯一索引、外键、检查）都属于同一个命名空间。在 MySQL 中，每个约束类型都有自己的命名空间。因此，每种约束类型的名称对于每个模式都必须是唯一的
PRIMARY KEY
- 所有键列必须定义为 NOT NULL 的唯一索引。如果没有显式地将他们声明为 NOT NULL，MySQL 会隐式地（并且以静默方式）声明它们。
- 如果表中没有主键，而应用程序要求表中必须有主键，那么 MySQL 将返回第一个无 NULL 的列作为主键的唯一索引。
- 在 InnoDB 表中，保持主键短，以最小化辅助索引的存储开销。每个二级索引条目包含对应行主键列的索引副本。参阅《Clustered and Secondary Indexes》
- 在创建的表中，首先放置主键，然后是所有的唯一索引，然后是非唯一索引。这有助于 MySQL 优化器优先选择要使用的索引，并且更快地检测重复的唯一键。
- 主键可以是多列索引。但是，不能使用列规范中的 PRIMARY KEY 键属性创建多列索引，这样做只会讲单个列标记为主列，必须使用单独的 PRIMARY KEY (key_part, ...) 子句。
- 如果一张表有 PRIMARY KEY 或 UNIQUE NOT NULL 索引，该索引由具有整数类型的单个列组成，则可以在 SELECT 语句中使用 _rowid 指向已索引的列，如《Unique Indexes》所述
- 在 MySQL 中，主键的名称为 PRIMARY。对于其他索引，如果不指定名称，则将为索引分配与第一个索引列相同的名称，并使用可选的后缀（_2、_3、…）使其唯一。可以使用 SHOW INDEX FROM tbl_name 查看列的索引名。参阅《SHOW INDEX Statement》
KEY | INDEX
- KEY 是 INDEX 的同义词。PRIMARY KEY 的键属性也可以在列定义中指定为 KEY。这是为了与其他数据库系统兼容而实现的
UNIQUE
- UNIQUE 索引创建了一个约束，使得索引中的所有值必须是不同的。如果尝试添加具有与现有行匹配的新行，则会发生错误。对于所有引擎，一个唯一索引允许包含 NULL 的列有多个 NULL 值。如果在唯一索引中为列指定一个前缀值，则列值在前缀长度内必须是唯一的。
FULLTEXT
- FULLTEXT 索引是用于全文搜索的一种特殊类型的索引。只有 InnoDB 和 MyISAM 引擎支持 FULLTEXT 索引，它们只能从 CHAR、VARCHAR、TEXT 列创建。索引总是在整个列上进行，不支持列前缀索引，如果指定，则忽略任何前缀长度，操作细节参阅《Full-Text Search Functions》。如果全文索引和搜索操作需要特殊处理，可以用 WITH PARSER 子句指定一个 index_option 值，以将解析器插件和索引关联起来，该子句仅对全文索引有效。有关创建插件的详细信息，参阅《The MySQL Plugin API》
SPATIAL
- 可以为空间类型创建 SPATIAL 索引。空间类型值支持 MyISAM 表，索引必须声明为 NOT NULL，参阅《Spatial Data Types》
FOREIGN KEY
- MySQL 支持外键，可以跨表交叉引用相关数据，外键约束有助于保持数据的一致性，相关定义和选项信息，参阅 reference_definition 和 reference_option
- 使用 InnoDB 存储引擎的分区表不支持外键。更多信息参阅《Restrictions and Limitations on Partitioning》
CHECK
- 所有存储引擎都会解析但忽略 CHECK 子句
key_part
- key_part 规范可以以 ASC 或 DESC 结束。这些关键字允许用于以后指定升序或降序索引值存储的扩展。目前，他们被解析但被忽略，索引值总是按升序存储的
- 对于 InnoDB 表，length 属性定义的前缀最长可达 767 字节，如果启用了 innodb_large_prefix 选项，最长可达 3072 字节。对于 MyISAM 表，前缀长度限制为 1000 字节
- 前缀限制以字节为单位。但是 CREATE TABLE、ALTER TABLE 以及 CREATE INDEX 语句中的非二进制字符串类型（CHAR、VARCHAR、TEXT）来说，前缀长度为字符数。在使用多字节字符集和非二进制字符串指定前缀长度时，要考虑这一点。
index_type
- 一些存储引擎允许在创建索引时指定索引类型。
- USING 首选位置在索引列之后。它可以在列列表之前给出，但不支持在该位置使用该选项，在未来的 MySQL 版本中将移除该选项。

1
2
3

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

TODO

index_option
reference_definition
reference_option

表选项

ENGINE
AUTO_INCREMENT
AVG_ROW_LENGTH
[DEFAULT] CHARACTER SET
CHECKSUM
[DEFAULT] COLLATE
COMMENT
CONNECTION
DATA DIRECTORY, INDEX DIRECTORY
DELAY_KEY_WRITE
INSERT_METHOD
KEY_BLOCK_SIZE
MAX_ROWS
MIN_ROWS
PACK_KEYS
PASSWORD
ROW_FORMAT
STATS_AUTO_RECALC
STATS_PERSISTENT
STATS_SAMPLE_PAGES
TABLESPACE
UNION

表分区

PARTITION BY
HASH(expr)
KEY(column_list)
RANGE(expr)
RANGE COLUMNS(column_list)
LIST(expr)
LIST COLUMNS(column_list)
PARTITIONS num
SUBPARTITION BY
partition_definition
subpartition_definition