22.2.3.1 范围列分区_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第24章分区 / 24.2 分区类型 / 24.2.3 列分区 / 22.2.3.1 范围列分区

22.2.3.1 范围列分区

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

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

RANGE COLUMNS不接受表达式，只接受列名。
RANGE COLUMNS接受一个或多个列的列表。
RANGE COLUMNS分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。行在RANGE COLUMNS分区中的放置也是基于元组之间的比较；这将在本节后面进一步讨论。
RANGE COLUMNS分区列不限于整数列；字符串， DATE列 DATETIME也可以作为分区列。（有关详细信息，请参阅第 22.2.3 节，“列分区”。）

创建分区表的基本语法 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显示创建分区表时可以使用的所有选项。有关完整信息，请参阅第 13.1.18 节，“CREATE TABLE 语句”。

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

分区列列表中的元素和定义每个分区的值列表中的元素必须以相同的顺序出现。此外，值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。但是，分区列列表和值列表中列名的顺序不必与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因为我们比较的是元组（即值的列表或集合）而不是标量值，所以与分区VALUES LESS THAN 一起使用的 as的语义RANGE COLUMNS与简单分区的情况有些不同 RANGE。在 RANGE分区中，生成表达式值等于 a 中限制值的行 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，正如我们通过对 INFORMATION_SCHEMA.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 在子句中引用COLUMNS，创建如下所示：

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

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

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 中使用行构造函数的更多信息，请参阅第 13.2.10.5 节，“行子查询”。

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

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';
+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p            | p0             |          0 |
| p            | 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)
 );

以下语句也成功，尽管乍一看似乎不会成功，因为 column 的限制值为bpartition 的 25 和 partition 的p020 p1，并且 column 的限制值为 cpartition 的 100 和 partition 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客户端比较所需的元组来测试连续的分区定义，如下所示：

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

当您遇到此类错误时，您可以通过在列列表之间进行“小于” 比较来推断哪些分区定义无效。在这种情况下，问题出在 partition 的定义上， p2因为用于定义它的元组不小于用于定义 partition 的元组 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)

使用时，也可能MAXVALUE在多个VALUES LESS THAN子句中出现同一列RANGE COLUMNS。但是，连续分区定义中各个列的限制值应该增加，否则不应定义超过一个分区，其中MAXVALUE用作所有列值的上限，并且此分区定义应出现在PARTITION ... VALUES LESS THAN子句列表的最后. 此外，您不能MAXVALUE将限制值用作多个分区定义中的第一列。

如前所述，RANGE COLUMNS分区也可以使用非整数列作为分区列。（有关这些的完整列表，请参阅第 22.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)
);

或者，您可以通过执行以下语句 employees使用此方案对先前创建的表进行分区：ALTER TABLE

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 如何处理字符集和排序规则的信息，请参阅第 10 章，字符集、排序规则、Unicode。

类似地，您可以使用此处显示的语句对employees 表进行分区，使每一行都存储在基于雇用相应员工的十年的多个分区之一中：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)
);

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