MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (1)

1. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
2.     (create_definition,...)
3.     [table_options]
4.     [partition_options]
5. 
6. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
7.     [(create_definition,...)]
8.     [table_options]
9.     [partition_options]
10.     [IGNORE | REPLACE]
11.     [AS] query_expression
12. 
13. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
14.     { LIKE old_tbl_name | (LIKE old_tbl_name) }
15. 
16. create_definition: {
17.     col_name column_definition
18.   | {INDEX | KEY} [index_name] [index_type] (key_part,...)
19.       [index_option] ...
20.   | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
21.       [index_option] ...
22.   | [CONSTRAINT [symbol]] PRIMARY KEY
23.       [index_type] (key_part,...)
24.       [index_option] ...
25.   | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
26.       [index_name] [index_type] (key_part,...)
27.       [index_option] ...
28.   | [CONSTRAINT [symbol]] FOREIGN KEY
29.       [index_name] (col_name,...)
30.       reference_definition
31.  | check_constraint_definition
32. }
33. 
34. column_definition: {
35.     data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
36.       [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
37.       [COMMENT ‘string‘]
38.       [COLLATE collation_name]
39.       [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
40.       [ENGINE_ATTRIBUTE [=] ‘string‘]
41.       [SECONDARY_ENGINE_ATTRIBUTE [=] ‘string‘] 
42.       [STORAGE {DISK | MEMORY}]
43.       [reference_definition]
44.       [check_constraint_definition]
45.   | data_type
46.       [COLLATE collation_name]
47.       [GENERATED ALWAYS] AS (expr)
48.       [VIRTUAL | STORED] [NOT NULL | NULL]
49.       [UNIQUE [KEY]] [[PRIMARY] KEY]
50.       [COMMENT ‘string‘]
51.       [reference_definition]
52.       [check_constraint_definition]
53. }
54. 
55. data_type:
56.     (see Chapter 11, Data Types)
57. 
58. key_part: {col_name [(length)] | (expr)} [ASC | DESC]
59. 
60. index_type:
61.     USING {BTREE | HASH}
62. 
63. index_option: {
64.     KEY_BLOCK_SIZE [=] value
65.   | index_type
66.   | WITH PARSER parser_name
67.   | COMMENT ‘string‘
68.   | {VISIBLE | INVISIBLE}
69.   |ENGINE_ATTRIBUTE [=] ‘string‘
70.   |SECONDARY_ENGINE_ATTRIBUTE [=] ‘string‘
71. }
72. 
73. check_constraint_definition:
74.     [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
75. 
76. reference_definition:
77.     REFERENCES tbl_name (key_part,...)
78.       [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
79.       [ON DELETE reference_option]
80.       [ON UPDATE reference_option]
81.
82. reference_option:
83.     RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
84. 
85. table_options:
86.     table_option [[,] table_option] ...
87. 
88. table_option: {
89.     AUTO_INCREMENT [=] value
90.   | AVG_ROW_LENGTH [=] value
91.   | [DEFAULT] CHARACTER SET [=] charset_name
92.   | CHECKSUM [=] {0 | 1}
93.   | [DEFAULT] COLLATE [=] collation_name
94.   | COMMENT [=] ‘string‘
95.   | COMPRESSION [=] {‘ZLIB‘ | ‘LZ4‘ | ‘NONE‘}
96.   | CONNECTION [=] ‘connect_string‘
97.   | {DATA | INDEX} DIRECTORY [=] ‘absolute path to directory‘
98.   | DELAY_KEY_WRITE [=] {0 | 1}
99.   | ENCRYPTION [=] {‘Y‘ | ‘N‘}
100.   | ENGINE [=] engine_name
101.   | ENGINE_ATTRIBUTE [=] ‘string‘ 
102.   | INSERT_METHOD [=] { NO | FIRST | LAST }
103.   | KEY_BLOCK_SIZE [=] value
104.   | MAX_ROWS [=] value
105.   | MIN_ROWS [=] value
106.   | PACK_KEYS [=] {0 | 1 | DEFAULT}
107.   | PASSWORD [=] ‘string‘
108.   | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
109.   | SECONDARY_ENGINE_ATTRIBUTE [=] ‘string‘
110.   | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
111.   | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
112.   | STATS_SAMPLE_PAGES [=] value
113.   | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
114.   | UNION [=] (tbl_name[,tbl_name]...)
115. }
116. 
117. partition_options:
118.     PARTITION BY
119.         { [LINEAR] HASH(expr)
120.         | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
121.         | RANGE{(expr) | COLUMNS(column_list)}
122.         | LIST{(expr) | COLUMNS(column_list)} }
123.     [PARTITIONS num]
124.     [SUBPARTITION BY
125.         { [LINEAR] HASH(expr)
126.         | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
127.       [SUBPARTITIONS num]
128.     ]
129.     [(partition_definition [, partition_definition] ...)]
130. 
131. partition_definition:
132.     PARTITION partition_name
133.         [VALUES
134.             {LESS THAN {(expr | value_list) | MAXVALUE}
135.             |
136.             IN (value_list)}]
137.         [[STORAGE] ENGINE [=] engine_name]
138.         [COMMENT [=] ‘string‘ ]
139.         [DATA DIRECTORY [=] ‘data_dir‘]
140.         [INDEX DIRECTORY [=] ‘index_dir‘]
141.         [MAX_ROWS [=] max_number_of_rows]
142.         [MIN_ROWS [=] min_number_of_rows]
143.         [TABLESPACE [=] tablespace_name]
144.         [(subpartition_definition [, subpartition_definition] ...)]
145. 
146. subpartition_definition:
147.     SUBPARTITION logical_name
148.         [[STORAGE] ENGINE [=] engine_name]
149.         [COMMENT [=] ‘string‘ ]
150.         [DATA DIRECTORY [=] ‘data_dir‘]
151.         [INDEX DIRECTORY [=] ‘index_dir‘]
152.         [MAX_ROWS [=] max_number_of_rows]
153.         [MIN_ROWS [=] min_number_of_rows]
154.         [TABLESPACE [=] tablespace_name]
155. 
156. query_expression:
157.     SELECT ...   (Some valid select or union statement)

CREATE TABLE 用给定名称创建表。必须要具有表的 CREATE 权限。

默认情况下，使用 InnoDB 存储引擎在默认数据库中创建表。如果表存在、没有默认数据库或数据库不存在，则会发生错误。

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

在本节的以下主题中对 CREATE TABLE 语句的几个方面进行了描述：

表名

● tbl_name

在特定数据库中创建表，表名可以指定为 db_name.tbl_name。不管是否有默认数据库，都可以这样做，假设数据库存在。如果使用带引号的标识符，请分别引用数据库名和表名。例如，mydb.mytbl，而不是 mydb.mytbl。

● IF NOT EXISTS

防止表存在时发生错误。但是，无法验证现有表是否与 CREATE TABLE 语句所指定的结构相同。

临时表

创建表时可以使用 TEMPORARY 关键字。TEMPORARY 表仅在当前会话中可见，并在会话关闭时自动删除。

表的克隆和复制

● LIKE

使用 CREATE TABLE ... LIKE 语句根据另一个表的定义创建一个空表，包括原始表中定义的任何列属性和索引：

1. CREATE TABLE new_tbl LIKE orig_tbl;

● [AS] query_expression

要从另一个表创建一个表，请在 CREATE TABLE 语句的末尾添加 SELECT 语句：

1. CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

● IGNORE | REPLACE

IGNORE 和 REPLACE 选项指示在使用 SELECT 语句复制表时如何处理重复的唯一键值的行。

列数据类型和属性

每个表有4096列的硬限制，但是对于给定的表，有效最大值可能更小，这还取决于其他因素。

● data_type

data_type 表示列定义中的数据类型。有关指定列数据类型可用语法的完整描述，以及有关每种类型属性的信息，请参阅对“数据类型”的详细介绍。

■ 有些属性并不适用于所有数据类型。AUTO_INCREMENT 仅适用于整数和浮点类型。在 MySQL 8.0.13 之前，DEFAULT 不适用于 BLOB、TEXT、GEOMETRY 和 JSON 类型。

■ 字符数据类型（CHAR、VARCHAR、TEXT、ENUM、SET 类型以及他们的同义词）可以包含 CHARACTER SET 来指定列的字符集。CHARSET 是 CHARACTER SET 的同义词。可以使用 COLLATE 属性以及其他属性指定字符集的排序规则。例子：

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

MySQL 8.0以字符形式解释字符列定义中的长度规范。BINARY 和 VARBINARY 列的长度以字节为单位。

■ 对于 CHAR、VARCHAR、BINARY 和 VARBINARY 列，可以只使用列值前导部分创建索引，使用 col_name(length) 语法指定索引前缀长度。BLOB 和 TEXT 列也可以被索引，但必须指定前缀长度。非二进制字符串类型的前缀长度以字符为单位，二进制字符串类型以字节为单位。也就是说，对于CHAR、VARCHAR 和 TEXT 列，索引项由每个列值的前 length 个字符组成，对于 BINARY、VARBINARY 和 BLOB 列，索引项由每个列值的前 length 个字节组成。只对列值的前缀进行索引可以使索引文件更小。

只有 InnoDB 和 MyISAM 存储引擎支持 BLOB 和 TEXT 列的索引。例如：

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

如果指定的索引前缀超过列数据类型最大大小，则 CREATE TABLE 将按如下方式处理索引：

○ 对于非唯一索引，要么发生错误（如果启用了严格SQL模式），要么索引长度减小到列数据类型大小的最大值之内，并生成警告（如果未启用严格SQL模式）。

○ 对于唯一索引，无论SQL模式如何，都会发生错误，因为减少索引长度可能会导致插入不满足指定唯一性要求的非唯一项。

■ 无法为 JSON 列创建索引。可以通过在从 JSON 列中提取标量值的生成列上创建索引来突破此限制。

● NOT NULL | NULL

如果未指定 NULL 和 NOT NULL，则将该列视为已指定 NULL。

在MySQL 8.0中，只有 InnoDB、MyISAM 和 MEMORY 存储引擎支持可以有 NULL 值的列创建索引。在其他情况下，必须将索引列声明为 NOT NULL，否则会报错。

● DEFAULT

指定列的默认值。

如果启用了 NO_ZERO_DATE 或 NO_ZERO_IN_DATE SQL模式，并且日期默认值不符合该模式，则如果未启用严格SQL模式，则 CREATE TABLE 将生成警告；如果启用了严格模式，则会生成错误。例如，如果启用了 NO_ZERO_IN_DATE，c1 DATE DEFAULT ‘2010-00-00‘ 这个语句将产生一个警告。

● AUTO_INCREMENT

整数列或浮点列可以有属性 AUTO_INCREMENT。将 NULL（推荐）或0插入索引的 AUTO_INCREMENT 列时，该列将设置为下一个序列值。通常是 value+1，其中value是表中当前列的最大值。AUTO_INCREMENT 序列从1开始。

若要在插入行后检索 AUTO_INCREMENT 值，请使用 LAST_INSERT_ID() SQL函数或 mysql_insert_id() C API 函数。

如果启用了 NO_AUTO_VALUE_ON_ZERO SQL模式，则可以在 AUTO_INCREMENT 列中将0存储为0，而无需生成新的序列值。

每个表只能有一个 AUTO_INCREMENT 列，必须对其进行索引，并且不能有 DEFAULT 值。只有当 AUTO_INCREMENT 列只包含正值时，它才能正常工作。插入一个负数被认为是插入一个非常大的正数。这样做是为了避免数字从正数转换到负数时出现精度问题，也为了确保不会意外地得到一个包含0的 AUTO_INCREMENT 列。

对于 MyISAM 表，可以在包含多个列的键中指定 AUTO_INCREMENT 辅助列。

要使 MySQL 与某些 ODBC 应用程序兼容，可以使用以下查询找到最后插入行的 AUTO_INCREMENT 值：

1. SELECT * FROM tbl_name WHERE auto_col IS NULL

此方法要求 sql_auto_is_null 变量未设置为0。

● COMMENT

可以使用 COMMENT 选项指定列的注释，最长1024个字符。可以用 SHOW CREATE TABLE 和 SHOW FULL COLUMNS 语句显示注释。

● COLUMN_FORMAT

在NDB集群中，还可以使用 COLUMN_FORMAT 为 NDB 表的某个列指定数据存储格式。允许的列格式有 FIXED、DYNAMIC 和 DEFAULT。FIXED 于指定固定宽度存储，DYNAMIC 允许列为可变宽度，而使用 DEFAULT 格式，可以基于列的数据类型来使用固定宽度还是可变宽度存储（可能被 ROW_FORMAT 说明符覆盖）。

对于NDB表，COLUMN_FORMAT 的默认值是 FIXED。

在NDB Cluster中，使用 COLUMN_FORMAT=FIXED 定义的列的最大可能偏移量为8188字节。

COLUMN_FORMAT 当前对使用NDB以外的存储引擎的表的列没有影响。MySQL8.0会自动忽略 COLUMN_FORMAT。

● ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 选项（从MySQL 8.0.21开始提供）用于指定主存储引擎和辅助存储引擎的列属性。这些选项保留供将来使用。

允许的值是包含有效JSON文档的字符串文本或空字符串(‘‘)。不接受无效的JSON。

1. CREATE TABLE t1 (c1 INT ENGINE_ATTRIBUTE=‘{"key":"value"}‘);

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值可以重复而不会报错。在这种情况下，使用最后一个值。

服务器不会检查 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值，也不会在更改表的存储引擎时清除它们。

● STORAGE

对于NDB表，可以使用 STORAGE 子句指定列是存储在磁盘上还是存储在内存中。STORAGE DISK 指定列存储在磁盘上，STORAGE MEMORY 指定内存存储。使用的 CREATE TABLE 语句必须仍然包含 TABLESPACE 子句：

1. mysql> CREATE TABLE t1 (
2.     ->     c1 INT STORAGE DISK,
3.     ->     c2 INT STORAGE MEMORY
4.     -> ) ENGINE NDB;
5. ERROR 1005 (HY000): Can‘t create table ‘c.t1‘ (errno: 140)
6. 
7. mysql> CREATE TABLE t1 (
8.     ->     c1 INT STORAGE DISK,
9.     ->     c2 INT STORAGE MEMORY
10.     -> ) TABLESPACE ts_1 ENGINE NDB;
11. Query OK, 0 rows affected (1.06 sec)

对于NDB表，STORAGE DEFAULT 等价于 STORAGE MEMORY。

STORAGE 子句对使用 NDB 以外的存储引擎的表没有影响。只有提供 NDB Cluster 的 mysqld 版本中才支持 STORAGE 关键字；在任何其他版本的MySQL中都无法识别该关键字，在其中尝试使用 STORAGE 关键字都会导致语法错误。

● GENERATED ALWAYS

用于指定生成列表达式。

可以为存储的生成列创建索引。InnoDB支持虚拟生成列的二级索引。

官方地址：
https://dev.mysql.com/doc/refman/8.0/en/create-table.html

MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (1)

标签：引擎   ons   code   服务   redundant   encrypt   query   always   symbol