CREATE TABLE

语法说明

CREATE TABLE 语句用于在当前所选数据库创建一张新表。

语法结构

> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]table_name [comment = "comment of table"];
(
    name1 type1 [comment 'comment of column'] [AUTO_INCREMENT] [[PRIMARY] KEY] [[FOREIGN] KEY],
    name2 type2 [comment 'comment of column'],
    ...
)
    [cluster by (column_name1, column_name2, ...);]
    [partition_options]

语法释义

TEMPORARY

在创建表时，可以使用 TEMPORARY 关键字创建一个临时表。TEMPORARY 表只在当前会话中可见，在会话关闭时自动删除。这表示两个不同的会话可以使用相同的临时表名，而不会彼此冲突或与同名的现有非临时表冲突。(在删除临时表之前，会隐藏现有表。)

删除数据库会自动删除数据库中创建的所有 TEMPORARY 表。

创建会话可以对表执行任何操作，例如 DROP table、INSERT、UPDATE 或 SELECT。

COMMENT

可以使用 comment 选项指定列或整张表的注释：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [comment = "comment of table"]; 中的 comment 为整张表的注释，最长 2049 个字符。
(name1 type1 [comment 'comment of column'],...) 中的 comment 为指定列的注释：最长 1024 个字符。

使用 SHOW CREATE TABLE 和 SHOW FULL COLUMNS 语句显示注释内容。注释内容也显示在 INFORMATION_SCHEMA.COLUMN_COMMENT 列中。

AUTO_INCREMENT

AUTO_INCREMENT：表的初始值，初始值从 1 开始，且数据列的值必须唯一。

设置 AUTO_INCREMENT 的列，需为整数或者浮点数据类型。
自增列需要设置为 NOT NULL，否则会直接存储 NULL。当你将 NULL（推荐）或 0 值插入索引的 AUTO_INCREMENT 列时，该列将设置为下一个序列值。通常这是 value+1，其中 value 是表中当前列的最大值。
每个表只能有一个 AUTO_INCREMENT 列，它必须可以被索引，且不能设置默认值。AUTO_INCREMENT 列需要含有正数值，如果插入一个负数被判断为插入一个非常大的正数，这样做是为了避免数字出现精度问题，并确保不会意外出现包含 0 的 AUTO_INCREMENT 列。

PRIMARY KEY

PRIMARY KEY 即主键约束，用于唯一标示表中的每条数据。主键必须包含 UNIQUE 值，不能包含 NULL 值。当前版本一个表只能有一个主键，这个主键可以由一个列组成，也可以由多个列组成。

在建表时创建主键

以下 SQL 语句在创建 Persons 表时，在其中的 ID 列创建主键：

> CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Note

上述示例中只有一个主键，并且其中仅包含了一列（ID）

而下面 SQL 语句在创建 Students 表时，在其中的 ID 和 LastName 列创建主键：

> CREATE TABLE Students (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID,LastName)
);

FOREIGN KEY

FOREIGN KEY 约束，即外键约束，是表的一个特殊字段，经常与主键约束一起使用。外键约束是用于防止破坏表之间链接的行为。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

定义外键时，需要遵守下列规则：

主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为主表定义主键。
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。

下面通过一个例子进行说明通过 FOREIGN KEY 和 PRIMARY KEY 关联父表与子表：

首先创建一个父表，字段 a 为主键：

create table t1(a int primary key,b varchar(5));
insert into t1 values(101,'abc'),(102,'def');
mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|  101 | abc  |
|  102 | def  |
+------+------+
2 rows in set (0.00 sec)

然后创建一个子表，字段 c 为外键，关联父表字段 a：

create table t2(a int ,b varchar(5),c int, foreign key(c) references t1(a));
insert into t2 values(1,'zs1',101),(2,'zs2',102);
insert into t2 values(3,'xyz',null);
mysql> select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | zs1  |  101 |
|    2 | zs2  |  102 |
|    3 | xyz  | NULL |
+------+------+------+
3 rows in set (0.00 sec)

有关数据完整性约束的更多信息，参见数据完整性约束概述。

Cluster by

Cluster by 是一种用于优化表的物理排列方式的命令。在建表时使用 Cluster by 命令，对于无主键的表，可以按照指定的列对表进行物理排序，并将数据行重新排列成与该列的值的顺序相同的顺序。使用 Cluster by 提高查询性能。

单列语法为：create table() cluster by col;
多列语法为：create table() cluster by (col1, col2);

Note: Cluster by 不能和主键同时存在，否则会语法报错；Cluster by 只能在建表时指定，不支持动态创建。

更多关于使用 Cluster by 进行性能调优，参见使用 Cluster by 语句调优.

Table PARTITION 和 PARTITIONS

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

partition_definition:
 PARTITION partition_name
     [VALUES
         {LESS THAN {(expr | value_list) | MAXVALUE}
         |
         IN (value_list)}]
     [COMMENT [=] 'string' ]

分区可以被修改、合并、添加到表中，也可以从表中删除。

· 和单个磁盘或文件系统分区相比，可以存储更多的数据。

· 优化查询。

where 子句中包含分区条件时，可以只扫描必要的分区。
涉及聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需汇总得到结果。

· 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。

· 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

PARTITION BY

分区语法以 PARTITION BY 开头。该子句包含用于确定分区的函数，这个函数返回一个从 1 到 num 的整数值，其中 num 是分区的数目。

HASH(expr)

在实际工作中，经常会遇到像会员表这种没有明显可以分区的特征字段的大表。为了把这类的数据进行分区打散，MatrixOne 提供了 HASH 分区。基于给定的分区个数，将数据分配到不同的分区，HASH 分区只能针对整数进行 HASH，对于非整形的字段则通过表达式将其转换成整数。

· HASH 分区，基于给定的分区个数，把数据分配到不同的分区。

· Expr 是使用一个或多个表列的表达式。

示例如下：

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

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

KEY(column_list)

· KEY 分区，按照某个字段取余。分区对象必须为列，不能是基于列的表达式，且允许多列。KEY 分区列可以不指定，默认为主键列或者唯一键列，无主键和唯一键的情况下，则必须显性指定列。

类似于 HASH。column_list 参数只是一个包含 1 个或多个表列的列表（最大值：16）。下面的示例为一个按 KEY 分区的简单表，有 4 个分区：

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

对于按 KEY 分区的表，可以使用 LINEAR KEY 来进行线性分区。这与使用 HASH 分区的表具有相同的效果。下面的示例为使用 LINEAR KEY 线性分区在 5 个分区之间分配数据：

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

PARTITIONS num

可以选择使用 PARTITIONS num 子句指定分区数，其中 num 是分区数。如果使用此子句的同时，也使用了其他 PARTITION 子句，那么 num 必须等于使用 PARTITION 子句声明的分区的总数。

示例

示例 1：创建普通表

CREATE TABLE test(a int, b varchar(10));
INSERT INTO test values(123, 'abc');

mysql> SELECT * FROM test;
+------+---------+
|   a  |    b    |
+------+---------+
|  123 |   abc   |
+------+---------+

示例 2：创建表示增加注释

create table t2 (a int, b int) comment = "事实表";

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
`a` INT DEFAULT NULL,
`b` INT DEFAULT NULL
) COMMENT='事实表',    |
+-------+---------------------------------------------------------------------------------------+

示例 3：建表时为列增加注释

create table t3 (a int comment '列的注释', b int) comment = "table";

mysql> SHOW CREATE TABLE t3;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
`a` INT DEFAULT NULL COMMENT '列的注释',
`b` INT DEFAULT NULL
) COMMENT='table',     |
+-------+----------------------------------------------------------------------------------------------------------+

示例 4：创建普通分区表

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

mysql> SHOW CREATE TABLE tp1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp1   | CREATE TABLE `tp1` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 2 (col3) partitions 4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 不指定分区数
CREATE TABLE tp2 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3);

mysql> SHOW CREATE TABLE tp2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tp2   | CREATE TABLE `tp2` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 2 (col3) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 指定分区算法
CREATE TABLE tp3
(
    col1 INT,
    col2 CHAR(5),
    col3 DATE
) PARTITION BY KEY ALGORITHM = 1 (col3);


mysql> show create table tp3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tp3   | CREATE TABLE `tp3` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 1 (col3) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 指定分区算法及分区数
CREATE TABLE tp4 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5;

mysql> SHOW CREATE TABLE tp4;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp4   | CREATE TABLE `tp4` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by linear key algorithm = 1 (col3) partitions 5 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 多列分区
CREATE TABLE tp5
(
    col1 INT,
    col2 CHAR(5),
    col3 DATE
) PARTITION BY KEY(col1, col2) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp5;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp5   | CREATE TABLE `tp5` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 2 (col1, col2) partitions 4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 创建主键列分区
CREATE TABLE tp6
(
    col1 INT  NOT NULL PRIMARY KEY,
    col2 DATE NOT NULL,
    col3 INT  NOT NULL,
    col4 INT  NOT NULL
) PARTITION BY KEY(col1) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp6;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp6   | CREATE TABLE `tp6` (
`col1` INT NOT NULL,
`col2` DATE NOT NULL,
`col3` INT NOT NULL,
`col4` INT NOT NULL,
PRIMARY KEY (`col1`)
) partition by key algorithm = 2 (col1) partitions 4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 创建HASH分区
CREATE TABLE tp7
(
    col1 INT,
    col2 CHAR(5)
) PARTITION BY HASH(col1);

mysql> SHOW CREATE TABLE tp7;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                         |
+-------+------------------------------------------------------------------------------------------------------+
| tp7   | CREATE TABLE `tp7` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL
) partition by hash (col1) |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 创建 HASH 分区时指定分区数
CREATE TABLE tp8
(
    col1 INT,
    col2 CHAR(5)
) PARTITION BY HASH(col1) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp8;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| tp8   | CREATE TABLE `tp8` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL
) partition by hash (col1) partitions 4 |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 创建分区时，指定分区粒度
CREATE TABLE tp9
(
    col1 INT,
    col2 CHAR(5),
    col3 DATETIME
) PARTITION BY HASH (YEAR(col3));

mysql> SHOW CREATE TABLE tp9;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| tp9   | CREATE TABLE `tp9` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATETIME DEFAULT NULL
) partition by hash (year(col3)) |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 创建分区时，指定分区粒度和分区数量
CREATE TABLE tp10
(
    col1 INT,
    col2 CHAR(5),
    col3 DATE
) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6;

mysql> SHOW CREATE TABLE tp10;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp10  | CREATE TABLE `tp10` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by linear hash (year(col3)) partitions 6 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 创建分区时，使用主键列作为 HASH 分区
CREATE TABLE tp12 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY HASH(col1) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp12;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp12  | CREATE TABLE `tp12` (
`col1` INT NOT NULL,
`col2` DATE NOT NULL,
`col3` INT NOT NULL,
`col4` INT NOT NULL,
PRIMARY KEY (`col1`)
) partition by hash (col1) partitions 4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

示例 5：主键自增

drop table if exists t1;
create table t1(a bigint primary key auto_increment, b varchar(10));
insert into t1(b) values ('bbb');
insert into t1 values (3, 'ccc');
insert into t1(b) values ('bbb1111');

mysql> select * from t1 order by a;
+------+---------+
| a    | b       |
+------+---------+
|    1 | bbb     |
|    3 | ccc     |
|    4 | bbb1111 |
+------+---------+
3 rows in set (0.01 sec)

insert into t1 values (2, 'aaaa1111');

mysql> select * from t1 order by a;
+------+----------+
| a    | b        |
+------+----------+
|    1 | bbb      |
|    2 | aaaa1111 |
|    3 | ccc      |
|    4 | bbb1111  |
+------+----------+
4 rows in set (0.00 sec)

insert into t1(b) values ('aaaa1111');

mysql> select * from t1 order by a;
+------+----------+
| a    | b        |
+------+----------+
|    1 | bbb      |
|    2 | aaaa1111 |
|    3 | ccc      |
|    4 | bbb1111  |
|    5 | aaaa1111 |
+------+----------+
5 rows in set (0.01 sec)

insert into t1 values (100, 'xxxx');
insert into t1(b) values ('xxxx');

mysql> select * from t1 order by a;
+------+----------+
| a    | b        |
+------+----------+
|    1 | bbb      |
|    2 | aaaa1111 |
|    3 | ccc      |
|    4 | bbb1111  |
|    5 | aaaa1111 |
|  100 | xxxx     |
|  101 | xxxx     |
+------+----------+
7 rows in set (0.00 sec)

限制

目前不支持使用 ALTER TABLE table_name DROP PRIMARY KEY 语句删除表中的主键。