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

26.2.3.1 RANGE COLUMNS 分区

基于多个列值的范围分区与基于单一整数类型列值的范围分区类似，但允许使用非整数类型的列来定义分区。另外，基于多个列值的范围分区还可以根据多个列值来定义分区。

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 分区中相同的 VALUES LESS THAN 的语义有所不同。在 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)

现在考虑一个类似于表 r1 的表 rc1，它使用 RANGE COLUMNS 分区，并且在 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)

这就是为什么我们在比较行值而不是标量值时会得到不同的结果。我们可以将插入的行值与定义表 rc1 中分区 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)

两个元组 (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 分区的表相同。以下是创建一个使用 1 个分区列的 RANGE COLUMNS 分区的表的 CREATE TABLE 语句：

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)
 );

以下语句也成功执行，即使在初看可能会认为它不会，因为列 b 的限制值为25分区 p0，而为20分区 p1，列 c 的限制值为100分区 p1，而为50分区 p2：

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> 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 时，MAXVALUE 可能会出现在多个 VALUES LESS THAN 子句中，但每个分区定义的列的限制值应该是递增的，不应有超过一个分区使用 MAXVALUE 作为所有列值的上限，并且这个分区定义应该出现在 PARTITION ... VALUES LESS THAN 子句列表的末尾。此外，您不能将 MAXVALUE 用作多个分区定义中的第一个列的限制值。

正如之前所述，使用 RANGE COLUMNS 分区时，也可以使用非整数列作为分区键。（请参阅第 26.2.3 节，“COLUMNS 分区”，以获取这些列的完整列表。）考虑一个名为 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)
);

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


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb