CREATE TABLE

语法说明

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

语法结构

1. > CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
2. (create_definition,...)
3. [table_options]
4. [partition_options]

6. create_definition: {
7. col_name column_definition
8. | [CONSTRAINT [symbol]] PRIMARY KEY
9. [index_type] (key_part,...)
10. [index_option] ...
11. | [CONSTRAINT [symbol]] FOREIGN KEY
12. [index_name] (col_name,...)
13. reference_definition
14. }

16. column_definition: {
17. data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
18. [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
19. [COMMENT 'string']
20. [reference_definition]
21. | data_type
22. [[PRIMARY] KEY]
23. [COMMENT 'string']
24. [reference_definition]
25. }

27. reference_definition:
28. REFERENCES tbl_name (key_part,...)
29. [ON DELETE reference_option]
30. [ON UPDATE reference_option]

32. reference_option:
33. RESTRICT | CASCADE | SET NULL | NO ACTION

35. table_options:
36. table_option [[,] table_option] ...

38. table_option: {
39. | AUTO_INCREMENT [=] value
40. | COMMENT [=] 'string'
41. | START TRANSACTION
42. }

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

51. partition_definition:
52. PARTITION partition_name
53. [VALUES
54. {LESS THAN {(expr | value_list) | MAXVALUE}
55. |
56. IN (value_list)}]
57. [COMMENT [=] 'string' ]

语法释义

创建表时可以使用的各种参数和选项，包括表的创建、列的定义、约束、选项和分区等。

  • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name：这是创建表的基本语法。TEMPORARY 关键字表示创建临时表，IF NOT EXISTS 表示如果表不存在则创建，tbl_name 是要创建的表的名称。
  • (create_definition,...)：这是列定义的部分，用来定义表的各个列以及相关属性。
  • [table_options]：这是表级别的选项，可以设置表的存储引擎、字符集等参数。
  • [partition_options]：这是用于分区表的选项，用来定义分区方式和分区键。

create_definition 部分用于定义每一列的属性，它可以包含以下内容：

  • col_name column_definition：定义具体列名以及列的属性，包括数据类型、是否允许为空、默认值等。
  • [CONSTRAINT [symbol]] PRIMARY KEY：定义主键约束，可以设置约束名称和主键的列。
  • [CONSTRAINT [symbol]] FOREIGN KEY：定义外键约束，可以设置约束名称、外键的列以及参考的表。

column_definition 部分用于具体列的定义，可以包含以下内容：

  • data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]：定义列的数据类型，以及是否允许为空和默认值。
  • [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]：设置自增、唯一和主键约束。
  • [COMMENT 'string']：设置列的注释。
  • [reference_definition]：可选的引用定义，用于定义外键约束。

reference_definition 部分用于定义外键引用，包括以下内容：

  • REFERENCES tbl_name (key_part,...)：指定外键引用的表和列。
  • [ON DELETE reference_option]：设置在删除时的外键操作。
  • [ON UPDATE reference_option]：设置在更新时的外键操作。

reference_option 表示外键操作的选项，可以是 RESTRICT、CASCADE、SET NULL 或 NO ACTION。

table_options 部分用于设置表级别的选项，包括自增的初始值、表的注释等。

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 开始，每条新纪录递增 1，且数据列的值必须唯一。

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

你可以使用 AUTO_INCREMENT 属性来定义自增列的起始值。如果要设置自增列的起始值为 10，可以在创建表时使用 AUTO_INCREMENT 关键字，并在后面指定起始值。

例如，创建一个表并定义自增列的起始值为 10，可以使用以下 SQL 语句：

1. -- 设置
2. create table t1(id int auto_increment primary key) auto_increment = 10;

在这个例子中，id 列是自增列，其起始值为 10，当向表中插入新记录时，id 列的值将从 10 开始，每次自动递增 1。如果没有指定 AUTO_INCREMENT 的起始值，默认起始值为 1，每次自动递增 1。

Note

  1. MatrixOne 目前仅支持默认的递增步长为 1，无论自增列的初始值为何，每次自动递增都为 1。暂时不支持设置递增步长大小。
  2. MatrixOne 仅语法上支持使用系统变量 set @@auto_increment_offset=n 来设置自增列初始值，实际并不生效。

PRIMARY KEY

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

  • 在建表时创建主键

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

1. > CREATE TABLE Persons (
2. ID int NOT NULL,
3. LastName varchar(255) NOT NULL,
4. FirstName varchar(255),
5. Age int,
6. PRIMARY KEY (ID)
7. );

Note

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

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

1. > CREATE TABLE Students (
2. ID int NOT NULL,
3. LastName varchar(255) NOT NULL,
4. FirstName varchar(255),
5. Age int,
6. PRIMARY KEY (ID,LastName)
7. );

FOREIGN KEY

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

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

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

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

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

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

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

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

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

另外，[ON DELETE reference_option] 和 [ON UPDATE reference_option] 在定义外键关系时用于指定在删除父表中的记录时执行的操作。这两个参数主要用于维护数据的完整性和一致性：

  • ON DELETE reference_option：这个参数指定了在引用表中的数据被删除时，应该如何处理与之关联的外键数据。常见的选项包括：

  • RESTRICT：如果在引用表中有相关的外键数据存在，不允许删除引用表中的数据。这可以用来防止误删除关联数据，以维护数据的一致性。

  • CASCADE：当引用表中的数据被删除时，同时删除与之关联的外键数据。这可以用于级联删除关联数据，以确保数据的完整性。
  • SET NULL：当引用表中的数据被删除时，将外键列的值设置为 NULL。这可以用于在删除引用数据时保留外键数据，但断开与引用数据的关联。
  • NO ACTION：表示不采取任何操作，只是检查是否有关联数据存在。这类似于 RESTRICT，但可能在某些数据库中有微小的差异。
  • ON UPDATE reference_option：这个参数指定了在引用表中的数据被更新时，应该如何处理与之关联的外键数据。常见的选项类似于 ON DELETE reference_option，用法也类似，只是针对数据更新操作。

参见下面的示例：

假设有两张表 Orders 和 Customers，Orders 表中有一个外键列 customer_id 引用 Customers 表中的 id 列。如果在 Customers 表中的某个客户被删除，同时也希望删除关联的订单数据，可以使用 ON DELETE CASCADE。

1. CREATE TABLE Customers (
2. id INT PRIMARY KEY,
3. name VARCHAR(50)
4. );

6. CREATE TABLE Orders (
7. id INT PRIMARY KEY,
8. order_number VARCHAR(10),
9. customer_id INT,
10. FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE
11. );

在上述示例中，当 Customers 表中的某个客户被删除时，关联的订单数据也会被级联删除，以维护数据的完整性。同样的，ON UPDATE 参数也可以用类似的方式来处理更新操作。

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

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

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

8. partition_definition:
9. PARTITION partition_name
10. [VALUES
11. {LESS THAN {(expr | value_list) | MAXVALUE}
12. |
13. IN (value_list)}]
14. [COMMENT [=] 'string' ]

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

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

· 优化查询。

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

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

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

  • PARTITION BY

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

  • HASH(expr)

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

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

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

示例如下：

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

4. CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
5. PARTITION BY HASH ( YEAR(col3) );
  • KEY(column_list)

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

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

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

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

1. CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
2. PARTITION BY LINEAR KEY(col3)
3. PARTITIONS 5;
  • PARTITIONS num

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

示例

  • 示例 1：创建普通表
1. CREATE TABLE test(a int, b varchar(10));
2. INSERT INTO test values(123, 'abc');

4. mysql> SELECT * FROM test;
5. +------+---------+
6. |   a  |    b    |
7. +------+---------+
8. |  123 |   abc   |
9. +------+---------+
  • 示例 2：创建表示增加注释
1. create table t2 (a int, b int) comment = "事实表";

3. mysql> show create table t2;
4. +-------+---------------------------------------------------------------------------------------+
5. | Table | Create Table                                                                          |
6. +-------+---------------------------------------------------------------------------------------+
7. | t2    | CREATE TABLE `t2` (
8. `a` INT DEFAULT NULL,
9. `b` INT DEFAULT NULL
10. ) COMMENT='事实表',    |
11. +-------+---------------------------------------------------------------------------------------+
  • 示例 3：建表时为列增加注释
1. create table t3 (a int comment '列的注释', b int) comment = "table";

3. mysql> SHOW CREATE TABLE t3;
4. +-------+----------------------------------------------------------------------------------------------------------+
5. | Table | Create Table                                                                                             |
6. +-------+----------------------------------------------------------------------------------------------------------+
7. | t3    | CREATE TABLE `t3` (
8. `a` INT DEFAULT NULL COMMENT '列的注释',
9. `b` INT DEFAULT NULL
10. ) COMMENT='table',     |
11. +-------+----------------------------------------------------------------------------------------------------------+
  • 示例 4：创建普通分区表
1. CREATE TABLE tp1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4;

3. mysql> SHOW CREATE TABLE tp1;
4. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
5. | Table | Create Table                                                                                                                                             |
6. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
7. | tp1   | CREATE TABLE `tp1` (
8. `col1` INT DEFAULT NULL,
9. `col2` CHAR(5) DEFAULT NULL,
10. `col3` DATE DEFAULT NULL
11. ) partition by key algorithm = 2 (col3) partitions 4 |
12. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
13. 1 row in set (0.00 sec)

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

18. mysql> SHOW CREATE TABLE tp2;
19. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
20. | Table | Create Table                                                                                                                                |
21. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
22. | tp2   | CREATE TABLE `tp2` (
23. `col1` INT DEFAULT NULL,
24. `col2` CHAR(5) DEFAULT NULL,
25. `col3` DATE DEFAULT NULL
26. ) partition by key algorithm = 2 (col3) |
27. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
28. 1 row in set (0.00 sec)

30. -- 指定分区算法
31. CREATE TABLE tp3
32. (
33. col1 INT,
34. col2 CHAR(5),
35. col3 DATE
36. ) PARTITION BY KEY ALGORITHM = 1 (col3);

39. mysql> show create table tp3;
40. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
41. | Table | Create Table                                                                                                                                |
42. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
43. | tp3   | CREATE TABLE `tp3` (
44. `col1` INT DEFAULT NULL,
45. `col2` CHAR(5) DEFAULT NULL,
46. `col3` DATE DEFAULT NULL
47. ) partition by key algorithm = 1 (col3) |
48. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
49. 1 row in set (0.00 sec)

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

54. mysql> SHOW CREATE TABLE tp4;
55. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
56. | Table | Create Table                                                                                                                                                    |
57. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
58. | tp4   | CREATE TABLE `tp4` (
59. `col1` INT DEFAULT NULL,
60. `col2` CHAR(5) DEFAULT NULL,
61. `col3` DATE DEFAULT NULL
62. ) partition by linear key algorithm = 1 (col3) partitions 5 |
63. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
64. 1 row in set (0.01 sec)

66. -- 多列分区
67. CREATE TABLE tp5
68. (
69. col1 INT,
70. col2 CHAR(5),
71. col3 DATE
72. ) PARTITION BY KEY(col1, col2) PARTITIONS 4;

74. mysql> SHOW CREATE TABLE tp5;
75. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
76. | Table | Create Table                                                                                                                                                   |
77. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
78. | tp5   | CREATE TABLE `tp5` (
79. `col1` INT DEFAULT NULL,
80. `col2` CHAR(5) DEFAULT NULL,
81. `col3` DATE DEFAULT NULL
82. ) partition by key algorithm = 2 (col1, col2) partitions 4 |
83. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
84. 1 row in set (0.01 sec)

86. -- 创建主键列分区
87. CREATE TABLE tp6
88. (
89. col1 INT  NOT NULL PRIMARY KEY,
90. col2 DATE NOT NULL,
91. col3 INT  NOT NULL,
92. col4 INT  NOT NULL
93. ) PARTITION BY KEY(col1) PARTITIONS 4;

95. mysql> SHOW CREATE TABLE tp6;
96. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
97. | Table | Create Table                                                                                                                                                                        |
98. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
99. | tp6   | CREATE TABLE `tp6` (
100. `col1` INT NOT NULL,
101. `col2` DATE NOT NULL,
102. `col3` INT NOT NULL,
103. `col4` INT NOT NULL,
104. PRIMARY KEY (`col1`)
105. ) partition by key algorithm = 2 (col1) partitions 4 |
106. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
107. 1 row in set (0.01 sec)

109. -- 创建 HASH 分区
110. CREATE TABLE tp7
111. (
112. col1 INT,
113. col2 CHAR(5)
114. ) PARTITION BY HASH(col1);

116. mysql> SHOW CREATE TABLE tp7;
117. +-------+------------------------------------------------------------------------------------------------------+
118. | Table | Create Table                                                                                         |
119. +-------+------------------------------------------------------------------------------------------------------+
120. | tp7   | CREATE TABLE `tp7` (
121. `col1` INT DEFAULT NULL,
122. `col2` CHAR(5) DEFAULT NULL
123. ) partition by hash (col1) |
124. +-------+------------------------------------------------------------------------------------------------------+
125. 1 row in set (0.01 sec)

127. -- 创建 HASH 分区时指定分区数
128. CREATE TABLE tp8
129. (
130. col1 INT,
131. col2 CHAR(5)
132. ) PARTITION BY HASH(col1) PARTITIONS 4;

134. mysql> SHOW CREATE TABLE tp8;
135. +-------+-------------------------------------------------------------------------------------------------------------------+
136. | Table | Create Table                                                                                                      |
137. +-------+-------------------------------------------------------------------------------------------------------------------+
138. | tp8   | CREATE TABLE `tp8` (
139. `col1` INT DEFAULT NULL,
140. `col2` CHAR(5) DEFAULT NULL
141. ) partition by hash (col1) partitions 4 |
142. +-------+-------------------------------------------------------------------------------------------------------------------+
143. 1 row in set (0.00 sec)

145. -- 创建分区时，指定分区粒度
146. CREATE TABLE tp9
147. (
148. col1 INT,
149. col2 CHAR(5),
150. col3 DATETIME
151. ) PARTITION BY HASH (YEAR(col3));

153. mysql> SHOW CREATE TABLE tp9;
154. +-------+------------------------------------------------------------------------------------------------------------------------------------------+
155. | Table | Create Table                                                                                                                             |
156. +-------+------------------------------------------------------------------------------------------------------------------------------------------+
157. | tp9   | CREATE TABLE `tp9` (
158. `col1` INT DEFAULT NULL,
159. `col2` CHAR(5) DEFAULT NULL,
160. `col3` DATETIME DEFAULT NULL
161. ) partition by hash (year(col3)) |
162. +-------+------------------------------------------------------------------------------------------------------------------------------------------+
163. 1 row in set (0.00 sec)

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

173. mysql> SHOW CREATE TABLE tp10;
174. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
175. | Table | Create Table                                                                                                                                              |
176. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
177. | tp10  | CREATE TABLE `tp10` (
178. `col1` INT DEFAULT NULL,
179. `col2` CHAR(5) DEFAULT NULL,
180. `col3` DATE DEFAULT NULL
181. ) partition by linear hash (year(col3)) partitions 6 |
182. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
183. 1 row in set (0.00 sec)

185. -- 创建分区时，使用主键列作为 HASH 分区
186. 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;

188. mysql> SHOW CREATE TABLE tp12;
189. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
190. | Table | Create Table                                                                                                                                                            |
191. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
192. | tp12  | CREATE TABLE `tp12` (
193. `col1` INT NOT NULL,
194. `col2` DATE NOT NULL,
195. `col3` INT NOT NULL,
196. `col4` INT NOT NULL,
197. PRIMARY KEY (`col1`)
198. ) partition by hash (col1) partitions 4 |
199. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
200. 1 row in set (0.01 sec)
  • 示例 5：主键自增
1. drop table if exists t1;
2. create table t1(a bigint primary key auto_increment, b varchar(10));
3. insert into t1(b) values ('bbb');
4. insert into t1 values (3, 'ccc');
5. insert into t1(b) values ('bbb1111');

7. mysql> select * from t1 order by a;
8. +------+---------+
9. | a    | b       |
10. +------+---------+
11. |    1 | bbb     |
12. |    3 | ccc     |
13. |    4 | bbb1111 |
14. +------+---------+
15. 3 rows in set (0.01 sec)

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

19. mysql> select * from t1 order by a;
20. +------+----------+
21. | a    | b        |
22. +------+----------+
23. |    1 | bbb      |
24. |    2 | aaaa1111 |
25. |    3 | ccc      |
26. |    4 | bbb1111  |
27. +------+----------+
28. 4 rows in set (0.00 sec)

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

32. mysql> select * from t1 order by a;
33. +------+----------+
34. | a    | b        |
35. +------+----------+
36. |    1 | bbb      |
37. |    2 | aaaa1111 |
38. |    3 | ccc      |
39. |    4 | bbb1111  |
40. |    5 | aaaa1111 |
41. +------+----------+
42. 5 rows in set (0.01 sec)

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

47. mysql> select * from t1 order by a;
48. +------+----------+
49. | a    | b        |
50. +------+----------+
51. |    1 | bbb      |
52. |    2 | aaaa1111 |
53. |    3 | ccc      |
54. |    4 | bbb1111  |
55. |    5 | aaaa1111 |
56. |  100 | xxxx     |
57. |  101 | xxxx     |
58. +------+----------+
59. 7 rows in set (0.00 sec)

限制

  1. 不支持使用 ALTER TABLE table_name DROP PRIMARY KEY 语句删除表中的主键。
  2. 不支持使用 ALTER TABLE table_name AUTO_INCREMENT = n; 语句修改自增列初始值。
  3. 在 MatrixOne 中，仅语法上支持使用系统变量 set @@auto_increment_increment=n 来设置递增步长，也仅语法支持使用系统变量 set @@auto_increment_offset=n 来设置默认自增列初始值，但实际上并不生效；当前支持设置自增列的初始值 AUTO_INCREMENT=n，但步长仍然默认为 1。