范围列分区类似于范围分区,但使您能够使用基于多个列值的范围来定义分区。此外,您可以使用非整数类型的列来定义范围。
RANGE COLUMNS
RANGE
分区在以下方面
与分区有很大不同:
RANGE COLUMNS
不接受表达式,只接受列名。RANGE COLUMNS
接受一个或多个列的列表。RANGE COLUMNS
分区基于 元组(列值列表)之间的比较,而不是标量值之间的比较。行在RANGE COLUMNS
分区中的放置也是基于元组之间的比较;这将在本节后面进一步讨论。RANGE COLUMNS
分区列不限于整数列;字符串,DATE
列DATETIME
也可以作为分区列。(有关详细信息,请参阅 第 24.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.20 节,“CREATE TABLE 语句”。
在刚刚显示的语法中,
column_list
是一个或多个列的列表(有时称为
分区列列表),并且value_list
是值列表(即,它是
分区定义值列表)。value_list
必须为每个分区定义提供
A ,并且每个分区定义必须具有与
has 列value_list
相同数量的值。column_list
一般来说,如果您
N
在子句中使用列
,则还必须为COLUMNS
每个子句提供一个值列表。
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
因为我们比较的是元组(即值的列表或集合)而不是标量值,所以与分区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.11.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 的限制值为b
partition 的 25 和 partition
的p0
20
p1
,并且 column 的限制值为
c
partition 的 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
分区也可以使用非整数列作为分区列。(有关
这些的完整列表,请参阅第 24.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.20 节,“CREATE TABLE 语句” 。PARTITION BY RANGE COLUMNS