22.2.3.1 RANGE COLUMNS 分区

范围列分区与范围分区相似，但是使您可以基于多个列值使用范围来定义分区。另外，您可以使用非整数类型的列来定义范围。

RANGE COLUMNS分区与RANGE分区在以下方面有很大不同：

RANGE COLUMNS不接受 table 达式，仅接受列名。
RANGE COLUMNS接受一列或多列的列 table。

RANGE COLUMNS分区基于 Tuples(列值列 table)之间的比较，而不是标量值之间的比较。行在RANGE COLUMNS分区中的放置也基于 Tuples 之间的比较；本节稍后将对此进行讨论。

RANGE COLUMNS分区列不限于整数列； string，DATE和DATETIME列也可以用作分区列。 (有关详细信息，请参见第 22.2.3 节“列分区”。)

创建以RANGE COLUMNS分区的 table 的基本语法如下所示：

CREATE TABLE table_name
PARTITIONED 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

此处未显示创建分区 table 时可以使用的所有CREATE TABLE选项。有关完整的信息，请参见第 13.1.18 节“ CREATE TABLE 语句”。

在刚刚显示的语法中，* column_list 是一个或多个列的列 table(有时称为分区列列 table)， value_list 是值的列 table(即，它是分区定义值列 table)。必须为每个分区定义提供 value_list ，并且每个 value_list 必须具有与 column_list 带有列的相同数量的值。一般来说，如果在COLUMNS子句中使用 N 列，则还必须为每个VALUES LESS THAN子句提供 N *值的列 table。

分区列列 table 和值列 table 中定义每个分区的元素必须以相同 Sequences 出现。此外，值列 table 中的每个元素必须与列列 table 中的相应元素具有相同的数据类型。但是，分区列列 table 和值列 table 中列名称的 Sequences 不必与CREATE TABLE语句主要部分中的 table 列定义的 Sequences 相同。与用RANGE分区的 table 一样，您可以使用MAXVALUEtable 示一个值，以便插入给定列的任何合法值始终小于该值。这是一个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)

tablercx包含列a，b，c，d。提供给COLUMNS子句的分区列列 table 使用其中的 3 列，Sequences 为a，d，c。每个用于定义分区的值列 table 均包含 3 个值，其 Sequences 相同。也就是说，每个值列 tableTuples 都具有(INT，INT，CHAR(3))形式，该形式对应于a，d和c列使用的数据类型(按此 Sequences)。

通过将要插入的行中与COLUMNS子句中的列列 table 相匹配的行中的 Tuples 与VALUES LESS THAN子句中用于定义 table 分区的 Tuples 进行比较，来确定行在分区中的位置。因为我们是在比较 Tuples(即值的列 table 或值集)，而不是标量值，所以与RANGE COLUMNS分区一起使用的VALUES LESS THAN的语义与简单RANGE分区的情况有些不同。在RANGE分区中，生成等于VALUES LESS THAN中的限制值的 table 达式值的行永远不会放在相应的分区中；但是，当使用RANGE COLUMNS分区时，有时可能会将其分区列列 table 的第一个元素的值等于VALUES LESS THAN值列 table 中的第一个元素的行放置在相应的分区中。

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

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

如果我们在此 table 中插入 3 行，使得a的列值是每行5，则所有 3 行都存储在分区p1中，因为a列值在每种情况下都不少于 5，我们可以通过执行针对INFORMATION_SCHEMA.PARTITIONStable 的正确查询：

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)

现在考虑一个类似的 tablerc1，该 table 使用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';
+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p            | p0             |          2 |
| p            | p1             |          1 |
+--------------+----------------+------------+
2 rows in set (0.00 sec)

这是因为我们正在比较行而不是标量值。我们可以将插入的行值与用于定义 tablerc1中分区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 个 Tuples(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 中使用行构造器的更多信息，请参见第 13.2.10.5 节“行子查询”。

对于仅使用单个分区列由RANGE COLUMNS分区的 table，分区中行的存储与由RANGE分区的等效 table 的存储相同。以下CREATE TABLE语句使用 1 个分区列创建由RANGE COLUMNS分区的 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)行插入此 table，我们可以看到它们的位置与我们先前创建并填充的 tabler的位置相同：

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';
+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p            | p0             |          0 |
| p            | p1             |          3 |
+--------------+----------------+------------+
2 rows in set (0.00 sec)

也可以创建由RANGE COLUMNS分区的 table，其中在连续分区定义中重复一或多个列的限制值。只要用于定义分区的列值的 Tuples 严格增加，就可以执行此操作。例如，以下每个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 的极限值为；对于分区p1，列c的极限值为 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分区的 table 时，您始终可以通过使用mysqlClient 端比较所需的 Tuples 来测试连续的分区定义，如下所示：

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

出现此类错误时，可以通过在列列 table 之间进行“小于”比较来推断哪些分区定义无效。在这种情况下，问题在于分区p2的定义，因为用于定义分区p2的 Tuples 不少于用于定义分区p3的 Tuples，如下所示：

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用作所有列值的上限，并且该分区定义应出现在列 table 的最后PARTITION ... VALUES LESS THAN子句。此外，不能将MAXVALUE用作多个分区定义中第一列的限制值。

如前所述，使用RANGE COLUMNS分区也可以将非整数列用作分区列。 (有关它们的完整列 table，请参见第 22.2.3 节“列分区”。)考虑使用以下语句创建的名为employees(未分区)的 table：

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分区，您可以创建此 table 的版本，该版本根据员工的姓氏将每行存储在四个分区之一中，如下所示：

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语句，使使用此方案对先前创建的employeestable 进行分区：

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

因为不同的字符集和排序规则具有不同的排序 Sequences，所以在使用字符串列作为分区列时，正在使用的字符集和排序规则可能会影响给定行被RANGE COLUMNS分区的 table 的哪个分区存储。此外，在创建此类 table 后更改给定数据库，table 或列的字符集或排序规则，可能会导致行的分配方式发生变化。例如，当使用区分大小写的排序规则时，'and'在'Andersen'之前排序，但是当使用不区分大小写的排序规则时，则相反。

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

同样，您可以使employeestable 进行分区，以使每行存储在几个分区之一中，该分区基于使用以下所示的ALTER TABLE语句雇用相应员工的十年：

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语法的更多信息，请参见第 13.1.18 节“ CREATE TABLE 语句”。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35