mysql5.6 5.5建表语句_MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (4)

表分区

partition_options 可用于控制使用 CREATE TABLE 语句创建的表的分区。

并非本节开头的 partition_options 语法中显示的所有选项都适用于所有分区类型。关于每种类型的具体信息，请参见下面的列表。

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

● PARTITION BY

如果使用，partition_options 子句以 PARTITION BY 开头。此子句包含用于确定分区的函数；该函数返回从1到 num 的整数值，其中 num 是分区的数目。(一个表可以包含的用户定义分区的最大数目是1024个；本节后面讨论的子分区的数量包含在这个最大值中。)

● HASH(expr)

散列一个或多个列以创建用于放置和定位行的键。expr 是使用一个或多个表列的表达式。它可以是任何有效的MySQL表达式(包括MySQL函数)，它可以生成单个整数值。例如，这两个语句都是使用 PARTITION BY HASH 的有效 CREATE TABLE 语句：

1. CREATE TABLE t1 (col1 INT, col2 CHAR(5))

2. PARTITION BY HASH(col1);

3.

4. CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)

5. PARTITION BY HASH ( YEAR(col3) );

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

PARTITION BY HASH 使用 expr 除以分区数的模数。(林员外注：这里原文好像有错误，余数和模数写混乱了，应该不是 remainder：PARTITION BY HASH uses the remainder of expr divided by the number of partitions (that is, the modulus).)

LINEAR 关键字需要一些不同的算法。在这种情况下，存储行的分区的数目是一个或多个逻辑 AND 操作的结果来计算的。

● KEY(column_list)

这与HASH类似，只是MySQL提供了散列函数，以保证数据分布均匀。column_list 参数只是一个由1个或多个表列组成的列表(最多16个)。此示例显示了一个按键分区的简单表，共有4个分区：

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)

PARTITION BY KEY(col3)

PARTITIONS 4;

对于按键分区的表，可以用 LINEAR 关键字来使用线性分区。这与使用 HASH 分区的表具有相同的效果。也就是说，使用 & 运算符而不是模数来找到分区号。此示例使用按键的线性分区在5个分区之间分配数据：

1. CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)

2. PARTITION BY LINEAR KEY(col3)

3. PARTITIONS 5;

ALGORITHM={1 | 2} 选项支持和 [SUB]PARTITION BY [LINEAR] KEY 一起使用。ALGORITHM=1 时，服务器使用与MySQL 5.1相同的键散列函数；ALGORITHM=2 表示服务器使用MySQL 5.5及更高版本中新的 KEY 分区表默认实现和使用的键散列函数。(MySQL 5.5及更高版本中使用的键散列函数创建的分区表不能用于MySQL 5.1服务器。)不指定该选项与使用 ALGORITHM=2 的效果相同。此选项主要用于在MySQL 5.1和更高版本之间升级或降级的 [LINEAR] KEY 分区表，或在MySQL 5.5或更高版本的服务器上按 KEY 或 LINEAR KEY 创建分区的表，可用于MySQL 5.1服务器。

MySQL 5.7(及以后版本)中的 mysqldump 将这个选项写在版本注释中，如下所示:

1. CREATE TABLE t1 (a INT)

2. /*!50100 PARTITION BY KEY */ /*!50611 ALGORITHM = 1 */ /*!50100 ()

3. PARTITIONS 3 */

这会使 MySQL 5.6.10 和更早版本的服务器忽略该选项，否则会导致这些版本中出现语法错误。如果计划将在MySQL5.7服务器(包含按照 KEY 进行分区或者子分区的表)上生成的转储文件加载到5.6.11之前版本的MySQL5.6服务器中，在继续之前，请务必参考MySQL5.6中的更改。(如果要将MySQL 5.7(实际上是5.6.11或更高版本的服务器)生成的包含 KEY 分区或子分区表的转储文件加载到MySQL 5.5.30或更早版本的服务器中，则此处的信息也适用。)

同样在 MySQL5.6.11 及更高版本中，当需要在 SHOW CREATE TABLE 的输出中使用与 mysqldump 相同的版本注释，可以设置 ALGORITHM=1。在 SHOW CREATE TABLE 输出中总是忽略 ALGORITHM=2 设置的影响，即使在创建原始表时指定了此选项。

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

● RANGE(expr)

在本例中，expr 用一组 VALUES LESS THAN 运算符显示一组值。使用范围分区时，必须使用 VALUES LESS THAN 定义至少一个分区。不能将 VALUES IN 与范围分区一起使用。

注意

对于按 RANGE (范围)分区的表，VALUES LESS THAN 必须用于整型字面值或计算结果为单个整数值的表达式。在 MySQL 8.0 中，可以在使用 PARTITION BY RANGE COLUMNS 定义的表中克服此限制，如本节后面所述。

假设您有一个表，希望根据以下方案对包含年份值的列进行分区。

分区号:

年份范围:

0

1990 及以前

1

1991 to 1994

2

1995 to 1998

3

1999 to 2002

4

2003 to 2005

5

2006 及以后

实现这种分区方案的表可以通过 CREATE TABLE 语句实现：

1. CREATE TABLE t1 (

2. year_col INT,

3. some_data INT

4. )

5. PARTITION BY RANGE (year_col) (

6. PARTITION p0 VALUES LESS THAN (1991),

7. PARTITION p1 VALUES LESS THAN (1995),

8. PARTITION p2 VALUES LESS THAN (1999),

9. PARTITION p3 VALUES LESS THAN (2002),

10. PARTITION p4 VALUES LESS THAN (2006),

11. PARTITION p5 VALUES LESS THAN MAXVALUE

12. );

PARTITION ... VALUES LESS THAN ... 语句以连续的方式工作。VALUES LESS THAN MAXVALUE 用于指定大于指定的最大值的“剩余”值。

VALUES LESS THAN 子句按顺序工作的方式类似于 switch ... case 程序块(在许多编程语言中都有，如C、Java和PHP)中的 case 部分。也就是说，子句的排列方式必须确保每个连续的 VALUES LESS THAN 中指定的上限大于前一个值的上限，引用 MAXVALUE 的一个位于列表中的最后。

● RANGE COLUMNS(column_list)

此 RANGE 上的变化有助于在多个列上使用范围条件(即，具有诸如 WHERE a = 1 AND b < 10 或 WHERE a = 1 AND b = 10 AND c < 10)的查询进行分区控制。它允许通过在 COLUMNS 子句中使用列列表和每个 PARTITION ... VALUES LESS THAN (value_list) 分区定义子句使用列值集合来指定多个列值范围。(在最简单的情况下，此集合包含一个列。)column_list 和 value_list 中可以引用的最大列数为16。

COLUMNS 子句中使用的 column_list 只能包含列的名称；列表中的每个列必须是以下MySQL数据类型之一：整数类型；字符串类型；以及时间或日期列类型。不允许使用BLOB、TEXT、SET、ENUM、BIT或空间数据类型的列；也不允许使用浮点数字类型的列，也不能在 COLUMNS 子句中使用函数或算术表达式。

分区定义中使用的 VALUES LESS THAN 子句必须为 COLUMNS() 子句中出现的每个列指定一个字面值；也就是说，用于每个 VALUES LESS THAN 子句的值列表必须包含与 COLUMNS 子句中列出的列数相同的值。如果试图在 VALUES LESS THAN 子句中使用多于或少于 COLUMNS 子句中的值，则会导致语句失败，提示：Inconsistency in usage of column lists for partitioning...。出现在 VALUES LESS THAN 中的任何值都不能为 NULL。对于给定列(第一列除外)可以多次使用 MAXVALUE，如下例所示：

1. CREATE TABLE rc (

2. a INT NOT NULL,

3. b INT NOT NULL

4. )

5. PARTITION BY RANGE COLUMNS(a,b) (

6. PARTITION p0 VALUES LESS THAN (10,5),

7. PARTITION p1 VALUES LESS THAN (20,10),

8. PARTITION p2 VALUES LESS THAN (50,MAXVALUE),

9. PARTITION p3 VALUES LESS THAN (65,MAXVALUE),

10. PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)

11. );

用于 VALUES LESS THAN 值列表的每个值必须与相应列的类型完全匹配；不进行转换。例如，不能将字符串 '1' 匹配整数类型的列(必须改用数字1)，也不能将数字1与字符串类型的列相匹配(在这种情况下，必须使用带引号的字符串：'1')。

● LIST(expr)

这在基于表列分配分区时非常有用，该列具有一组受限制的可能值，例如州或国家代码。在这种情况下，与某个州或国家有关的所有行都可以分配给一个分区，也可以将一个分区保留给一组特定的州或国家。它与 RANGE 类似，只是只有 VALUES IN 可用于指定每个分区的允许值。

VALUES IN 与要匹配的值列表一起使用。例如，可以创建如下分区方案：

1. CREATE TABLE client_firms (

2. id INT,

3. name VARCHAR(35)

4. )

5. PARTITION BY LIST (id) (

6. PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),

7. PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),

8. PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),

9. PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)

10. );

使用列表分区时，必须使用 VALUES IN 定义至少一个分区。不能把 VALUES LESS THAN 用于 PARTITION BY LIST。

注意

对于通过 LIST 分区的表，与 VALUES IN 一起使用的值列表只能由整数值组成。在MySQL 8.0中，可以使用 LIST COLUMNS 分区来克服这个限制，这将在本节后面介绍。

● LIST COLUMNS(column_list)

LIST 上的这个变量有助于使用多个列上的比较条件(即，具有诸如 WHERE a = 5 AND b = 5 或 WHERE a = 1 AND b = 10 AND c = 5)的查询进行分区控制。它允许通过使用 COLUMNS 子句中的列的列表和每个 PARTITION ... VALUES IN (value_list) 分区定义子句中的列值集合来指定多个列的值。

关于 LIST COLUMNS(column_list) 中使用的列列表和 VALUES IN(value_list) 中使用的值列表的数据类型规则分别与 RANGE COLUMNS(column_list) 中使用的列列表和 VALUES LESS THAN(value_list) 中使用的值列表的数据类型规则相同，区别是在 VALUES IN 子句中，不允许使用 MAXVALUE，可以使用 NULL。

在 VALUES IN 和 PARTITION BY LIST COLUMNS 语句中使用的值列表它和 PARTITION BY LIST 一起使用的值列表有一个重要区别。当与 PARTITION BY LIST COLUMNS 一起使用时，VALUES IN 子句中的每个元素必须是一组列值；每个集合中的值的数目必须与 COLUMNS 子句中使用的列数相同，并且这些值的数据类型必须与列的数据类型相匹配(并且以相同的顺序出现)。在最简单的情况下，集合由一列组成。用于 column_list 的最大列数和 value_list 中的最大元素数量是16。

以下 CREATE TABLE 语句定义的表提供了一个使用 LIST COLUMNS 分区的示例：

1. CREATE TABLE lc (

2. a INT NULL,

3. b INT NULL

4. )

5. PARTITION BY LIST COLUMNS(a,b) (

6. PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),

7. PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),

8. PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),

9. PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )

6. );

● PARTITIONS num

分区数可以使用 PARTITIONS num 子句指定，其中 num 是分区数。如果同时使用了这个子句和 PARTITION 子句，num 必须等于使用 PARTITION 子句声明的分区的总数。

注意

在创建表时使用 RANGE 或 LIST 分区，无论是否使用 PARTITIONS 子句，都必须在表定义中至少包含一个 PARTITION VALUES 子句(请参见下文)。

● SUBPARTITION BY

一个分区可以划分为多个子分区。这可以通过使用 SUBPARTITION BY 子句来做到。可以通过 HASH 或 KEY 来划分子分区。这两个都可以是线性的。它们的工作方式与前面描述的等效分区类型的方式相同。(无法按 LIST 或 RANGE 划分子分区。)

可以使用 SUBPARTITIONS 关键字后跟一个整数值来表示子分区的数量。

● 对 PARTITIONS 或 SUBPARTITIONS 子句中使用的值要进行严格检查，此值必须遵守以下规则：

■ 该值必须是非零的正整数。

■ 不允许有前导零。

■ 该值必须是整数字面量，不能是表达式。例如，PARTITIONS 0.2E+01 是不允许的，即使0.2E+01的计算结果是2。

● partition_definition

每个分区可以使用 partition_definition 子句单独定义。构成此子句的各个部分如下：

■ PARTITION partition_name

指定分区的逻辑名称。

■ VALUES

对于范围分区，每个分区必须包含一个 VALUES LESS THAN 子句；对于列表分区，必须为每个分区指定 VALUES IN 子句，用于确定要在这个分区中存储哪些行。

■ [STORAGE] ENGINE

对于 PARTITION 和 SUBPARTITION 语句，MySQL都接受 [STORAGE] ENGINE 选项。目前，使用此选项的唯一方法是将所有分区或所有子分区设置为相同的存储引擎，如果试图在同一个表中为分区或子分区设置不同的存储引擎，会引发错误 ERROR 1469 (HY000): The mix of handlers in the partitions is not permitted in this version of MySQL。

■ COMMENT

可选的 COMMENT 子句可用于指定描述分区的字符串。示例：

1. COMMENT = 'Data for the years previous to 1999'

分区注释的最大长度为1024个字符。

■ DATA DIRECTORY 和 INDEX DIRECTORY

DATA DIRECTORY 和 INDEX DIRECTORY 可用于分别指示存储此分区的数据和索引的目录。data_dir 和 index_dir 都必须是系统的绝对路径名。

从MySQL 8.0.21开始，DATA DIRECTORY 子句中指定的目录必须是 InnoDB 已知的。

必须具有 FILE 权限才能使用 DATA DIRECTORY 或 INDEX DIRECTORY 分区选项。

示例：

1. CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)

2. PARTITION BY LIST(YEAR(adate))

3. (

4. PARTITION p1999 VALUES IN (1995, 1999, 2003)

5. DATA DIRECTORY = '/var/appdata/95/data'

6. INDEX DIRECTORY = '/var/appdata/95/idx',

7. PARTITION p2000 VALUES IN (1996, 2000, 2004)

8. DATA DIRECTORY = '/var/appdata/96/data'

9. INDEX DIRECTORY = '/var/appdata/96/idx',

10. PARTITION p2001 VALUES IN (1997, 2001, 2005)

11. DATA DIRECTORY = '/var/appdata/97/data'

12. INDEX DIRECTORY = '/var/appdata/97/idx',

13. PARTITION p2002 VALUES IN (1998, 2002, 2006)

14. DATA DIRECTORY = '/var/appdata/98/data'

15. INDEX DIRECTORY = '/var/appdata/98/idx'

16. );

DATA DIRECTORY 和 INDEX DIRECTORY 在 MyISAM 表 CREATE TABLE 语句的 table_option 子句中的行为方式是相同的。

每个分区可以指定一个数据目录和一个索引目录。如果未指定，默认情况下，数据和索引存储在表的数据库目录中。

如果 NO_DIR_IN_CREATE 生效，则在创建分区表时将忽略 DATA DIRECTORY 和 INDEX DIRECTORY 选项。

■ MAX_ROWS 和 MIN_ROWS

可分别用于指定分区中存储的最大行数和最小行数。max_number_of_rows 和 min_number_of_rows 的值必须是正整数。与具有相同名称的表级选项一样，这些选项仅作为对服务器的“建议”，而不是硬限制。

■ TABLESPACE

可以通过 TABLESPACE innodb_file_per_table 来为分区指定一个 InnoDB 独立表空间。所有分区必须属于同一存储引擎。

不支持将 InnoDB 表分区放在共享的 InnoDB 表空间中。共享表空间包括 InnoDB 系统表空间和通用表空间。

● subpartition_definition

分区定义可以选择包含一个或多个 subpartition_definition 子句。每一个都至少包含 SUBPARTITION name，其中 name 是子分区的标识符。除了用 SUBPARTITION 替换 PARTITION 关键字之外，子分区定义的语法与分区定义的语法是相同的。

子分区必须通过 HASH 或 KEY 来完成，并且只能在 RANGE 或 LIST 分区上进行。

按生成列进行分区

允许按生成列进行分区。例如:

1. CREATE TABLE t1 (

2. s1 INT,

3. s2 INT AS (EXP(s1)) STORED

4. )

5. PARTITION BY LIST (s2) (

6. PARTITION p1 VALUES IN (1)

7. );

分区将生成的列视为常规列，这样就可以解决函数不允许用于分区的限制。前面的示例演示了这种技术：EXP() 不能直接用于 PARTITION BY 子句，但是允许使用 EXP() 定义的生成列。