MySQL :: MySQL 8.3 Reference Manual :: 26.2.3.1 RANGE COLUMNS partitioning

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / RANGE COLUMNS partitioning

26.2.3.1 范围列分区

范围列分区类似于范围分区，但允许您使用多个列值定义分区。此外，您还可以使用非整数类型的列来定义范围。

RANGE COLUMNS 分区与 RANGE 分区有以下几个关键区别：

RANGE COLUMNS 不接受表达式，只接受列名。
RANGE COLUMNS 接受一个或多个列的列表。

RANGE COLUMNS 分区基于元组（列值列表）之间的比较，而不是基于标量值之间的比较。行的放置在 RANGE COLUMNS 分区中也基于元组之间的比较；这将在本节后面讨论。
RANGE COLUMNS 分区列不限于整数列；字符串、DATE 和 DATETIME 列也可以用作分区列。（见第 26.2.3 节，“COLUMNS 分区”，了解详细信息。）

使用 RANGE COLUMNS 创建表的基本语法如下所示：

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]

Note

并不是所有 CREATE TABLE 选项都可以在创建分区表时使用。有关完整信息，请参阅第 15.1.20 节，“CREATE TABLE 语句”。

在上面的语法中，column_list 是一个或多个列的列表（有时称为分区列列表），value_list 是一个值列表（即分区定义值列表）。每个分区定义都需要提供一个 value_list，每个 value_list 都必须与 column_list 中的列数相同。一般来说，如果您在 COLUMNS 子句中使用 N 列，那么每个 VALUES LESS THAN 子句也必须提供一个包含 N 个值的列表。

分区列列表和每个分区的值列表中的元素必须以相同的顺序出现。此外，每个值列表中的元素必须与对应的列列表中的元素具有相同的数据类型。但是，分区列列表和值列表中的列名顺序不需要与 CREATE TABLE 语句中的表列定义顺序相同。与使用 RANGE 分区一样，您可以使用 MAXVALUE 代表一个值，使得任何合法值插入给定列时总是小于该值。下面是一个使用 CREATE TABLE 语句的示例，演示了这些点：

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

表 rcx 包含列 a、b、c 和 d。提供给 COLUMNS 子句的分区列列表使用了这 3 列，以 a、d、c 的顺序。每个用于定义分区的值列表都包含 3 个值，以相同的顺序；即每个值列表元组的形式为 (INT、INT、CHAR(3)），对应于列 a、d 和 c 的数据类型（以该顺序）。

行的放置到分区是通过比较要插入的行的元组与 COLUMNS 子句中的列列表来确定的，用于定义表的分区。因为我们比较的是元组（即值列表或集合）而不是标量值，因此 VALUES LESS THAN 在 RANGE COLUMNS 分区中的语义与简单的 RANGE 分区不同。在 RANGE 分区中，生成表达式值等于 VALUES LESS THAN 中的限制值的行永远不会被放置在对应的分区中；然而，在使用 RANGE COLUMNS 分区时，有时可能将行的分区列列表的第一个元素的值等于 VALUES LESS THAN 值列表的第一个元素的值的行放置在对应的分区中。

考虑以下 RANGE 分区表的创建语句：

CREATE TABLE r1 (
    a INT,
    b INT
)
PARTITION BY RANGE (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我们将 3 行插入到这个表中，使得每行的 a 列值为 5，那么所有 3 行都将被存储在分区 p1 中，因为 a 列值在每种情况下都不小于 5，如我们可以通过执行适当的查询来查看信息模式 PARTITIONS 表：

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'r1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

现在考虑一个类似的表 rc1，它使用 RANGE COLUMNS 分区，引用了 a 和 b 两个列，创建如下：

CREATE TABLE rc1 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

如果我们将完全相同的行插入到 rc1 中，就像我们刚刚插入到 r1 中一样，行的分布将非常不同：

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p3             |          1 |
+----------------+------------+
2 rows in set (0.00 sec)

这是因为我们比较的是行而不是标量值。我们可以比较插入的行值与 VALUES THAN LESS THAN 子句中用于定义分区 p0 的限制行值，如下所示：

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

2 个元组 (5,10) 和 (5,11) 小于 (5,12)，因此它们被存储在分区 p0 中。由于 5 不小于 5，12 不小于 12，(5,12) 被认为不小于 (5,12)，因此被存储在分区 p1 中。

前面的示例中的 SELECT 语句也可以使用明确的行构造器来编写，如下所示：

SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);

有关 MySQL 中行构造器的使用的更多信息，请参阅第 15.2.15.5 节，“行子查询”。

对于使用单个分区列的 RANGE COLUMNS 分区表，行的存储在分区中的方式与等效的 RANGE 分区表相同。以下 CREATE TABLE 语句创建了一个使用 1 个分区列的 RANGE COLUMNS 分区表：

CREATE TABLE rx (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我们将行 (5,10)、(5,11) 和 (5,12) 插入到这个表中，我们可以看到它们的放置方式与我们之前创建和填充的表 r 相同：

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rx';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

也可以创建使用 RANGE COLUMNS 分区的表，其中一个或多个列的限制值在连续的分区定义中重复出现。只要使用的元组值严格递增即可。例如，每个以下 CREATE TABLE 语句都是有效的：

CREATE TABLE rc2 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

CREATE TABLE rc3 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (20,40),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

以下语句也成功，即使它看起来不可能，因为分区 p0 的列 b 的限制值为 25，分区 p1 的限制值为 20，而列 c 的限制值为 100 和 50：

CREATE TABLE rc4 (
    a INT,
    b INT,
    c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,20,100),
    PARTITION p2 VALUES LESS THAN (10,30,50),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

设计 RANGE COLUMNS 分区表时，可以使用 mysql 客户端测试连续的分区定义，如下所示：

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

如果 CREATE TABLE 语句包含不按严格递增顺序排列的分区定义，将失败并显示错误，如下所示：

mysql> CREATE TABLE rcf (
    ->     a INT,
    ->     b INT,
    ->     c INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,b,c) (
    ->     PARTITION p0 VALUES LESS THAN (0,25,50),
    ->     PARTITION p1 VALUES LESS THAN (20,20,100),
    ->     PARTITION p2 VALUES LESS THAN (10,30,50),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    ->  );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

当您遇到这种错误时，可以通过比较它们的列列表来确定哪些分区定义无效。在这种情况下，问题出在分区 p2 的定义，因为用于定义它的元组不小于用于定义分区 p3 的元组，如下所示：

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

使用 RANGE COLUMNS 时，也可以在多个 VALUES LESS THAN 子句中出现 MAXVALUE，但是在每个列的后续分区定义中，限制值应该递增，不应该有多个分区定义使用 MAXVALUE 作为所有列值的上限，并且该分区定义应该出现在 PARTITION ... VALUES LESS THAN 子句列表的最后。此外，不能在多个分区定义中使用 MAXVALUE 作为第一个列的限制值。

如前所述，使用 RANGE COLUMNS 分区还可以使用非整数列作为分区列。（参见第 26.2.3 节，“COLUMNS Partitioning”，以获取完整的列列表。）考虑一个名为 employees 的表（未分区），使用以下语句创建：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

使用 RANGE COLUMNS 分区，可以创建一个基于员工姓氏的四个分区的表，如下所示：

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

或者，可以使用以下 ALTER TABLE 语句将之前创建的 employees 表分区：

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Note

因为不同的字符集和排序规则具有不同的排序顺序，因此在使用字符串列作为分区列时，字符集和排序规则可能会影响行存储在哪个分区中。此外，在创建表后更改数据库、表或列的字符集或排序规则可能会改变行的分布。例如，在使用区分大小写的排序规则时，'and' 排序在 'Andersen' 之前，但是在使用不区分大小写的排序规则时，情况正好相反。

有关 MySQL 如何处理字符集和排序规则的信息，请参见第 12 章，《字符集、排序规则、Unicode》。

类似地，可以使用以下 ALTER TABLE 语句将 employees 表分区，以便每行存储在基于员工入职年代的多个分区中：

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
    PARTITION p0 VALUES LESS THAN ('1970-01-01'),
    PARTITION p1 VALUES LESS THAN ('1980-01-01'),
    PARTITION p2 VALUES LESS THAN ('1990-01-01'),
    PARTITION p3 VALUES LESS THAN ('2000-01-01'),
    PARTITION p4 VALUES LESS THAN ('2010-01-01'),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

有关 PARTITION BY RANGE COLUMNS 语法的更多信息，请参见第 15.1.20 节，“CREATE TABLE 语句”。

PREV HOME UP NEXT