MySQL 8.4 参考手册  /  ...  /  RANGE COLUMNS 分区

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][, ...]
注意

此处未显示创建分区表时可用的所有 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 分区相比，VALUES LESS THAN 与 RANGE COLUMNS 分区一起使用的语义有所不同。在 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 分区，并在 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)
);

如果我们像刚刚插入到 r1 中那样将完全相同的行插入到 rc1 中，则行的分布会有很大差异

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 的 VALUES THAN LESS THAN 子句中的限制行值进行比较，如下所示

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

以下语句也会成功，即使乍一看似乎不会成功，因为列 b 的限制值为分区 p0 为 25，分区 p1 为 20，列 c 的限制值为分区 p1 为 100，分区 p2 为 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 时，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)
);
注意

由于不同的字符集和排序规则具有不同的排序顺序，因此，在使用字符串列作为分区列时，使用的字符集和排序规则可能会影响存储给定行的 RANGE COLUMNS 分区表的哪个分区。此外，在此类表创建后更改给定数据库、表或列的字符集或排序规则可能会导致行分配方式发生变化。例如，当使用区分大小写的排序规则时，'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 语句”。


上一页   首页   向上   下一页
相关文档
MySQL 8.4 发行说明
下载本手册
PDF (US Ltr) - 39.9Mb
PDF (A4) - 40.0Mb
手册页 (TGZ) - 258.5Kb
手册页 (Zip) - 365.5Kb
信息 (Gzip) - 4.0Mb
信息 (Zip) - 4.0Mb