范围列分区
范围列分区类似于范围分区，但允许您使用基于多个列值的范围来定义分区。此外，还可以使用非整数类型的列来定义范围。
范围列分区与范围分区在以下几个方面有很大的不同：
.范围列分区不接受表达式，只接受列名。
.范围列分区接受一个或多个列的列表。
范围列分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。在范围列分区中行的放置也基于元组之间的比较；本节后面将进一步 讨论这一点。
.范围列分区列不限制为整数列；string、DATE和DATETIME列也可以用作分区列。
创建由范围列分区的表的基本语法如下所示：
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][, ...]
在上述语法中，column_list是一个包含一个或多个列的列表（有时称为分区列列表），value_list是一个值列表（也就是说，它是一个分区定 义值列表）。每个分区定义都必须提供一个value_list，每个value_list的值数量必须与column_list的列数量相同。一般来说，如果在columns 子句中使用N列，那么每个VALUES小于子句也必须提供N个值的列表。
分区列列表中的元素以及定义每个分区的值列表中的元素必须按相同的顺序出现。此外，值列表中的每个元素必须与列列表中对应的元素具有相 同的数据类型。不过，分区列列表中的列名顺序以及值列表中的顺序不必与 CREATE TABLE 语句主部分中的表列定义顺序相同。与按范围分区的 表一样，您可以使用 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.03 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的语义与简单范围分区的情况有所 不同。在范围分区中，生成的表达式值等于VALUES小于中的一个极限值的行永远不会被放置到相应的分区中；然而，当使用范围列分区时，有时 可能会出现这样的情况：分区列列表的第一个元素的值与小于值列表的第一个元素的值相等，那么就被放置到相应的分区中。
考虑下面这个语句创建的范围分区表
mysql> CREATE TABLE r1 (
-> a INT,
-> b INT
-> )
-> PARTITION BY RANGE (a) (
-> PARTITION p0 VALUES LESS THAN (5),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.02 sec)
如果我们在这个表中插入3行，使得a的每一行的列值为5，那么所有3行都存储在分区p1中，因为在每种情况下a列的值都不小于5，这可以通过对 INFORMATION_SCHEMA执行适当的查询来看到。分区表:
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，它使用范围列分区，在COLUMNS子句中同时引用a和b列，如下所示：
mysql> 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)
-> );
Query OK, 0 rows affected (0.01 sec)
如果我们在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的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)
两个元组（5,10）和（5,11）的值小于（5,12），因此它们被存储在p0分区中。由于5不小于5,12不小于12，因此（5,12）被认为不小于（5,12 ），并存储在分区p1中。
上例中的SELECT语句也可以使用显式的行构造函数编写，如下所示：
mysql> SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12); +-----------------------+-----------------------+-----------------------+ | ROW(5,10) < ROW(5,12) | ROW(5,11) < ROW(5,12) | ROW(5,12) < ROW(5,12) | +-----------------------+-----------------------+-----------------------+ | 1 | 1 | 0 | +-----------------------+-----------------------+-----------------------+ 1 row in set (0.00 sec)
对于只使用单个分区列的按范围列分区的表，在分区中存储行与按范围分区的表是一样的。下面的CREATE TABLE语句使用1个分区列创建一个按 范围列分区的表：
mysql> CREATE TABLE rx (
-> a INT,
-> b INT
-> )
-> PARTITION BY RANGE COLUMNS (a) (
-> PARTITION p0 VALUES LESS THAN (5),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.01 sec)
如果我们将（5,10），（5,11）和（5,12）行插入到这个表中，我们可以看到它们的位置与我们之前创建和填充的表r的位置相同：
mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.01 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)
还可以创建按范围列进行分区的表，其中一个或多个列的限制值在连续的分区定义中重复出现。只要用于定义分区的列值元组严格递增，就可以 这样做。例如，下面的CREATE TABLE语句都是有效的。
mysql> 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)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> 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)
-> );
Query OK, 0 rows affected (0.02 sec)
下面的语句也成功了，尽管乍一看可能不会成功，因为分区p0的b列的极限值是25，分区p1的极限值是20，分区p1的c列的极限值是100，分区p2 的极限值是50：
mysql> 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)
-> );
Query OK, 0 rows affected (0.02 sec)
在设计按范围列进行分区的表时，你总是可以使用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)
在使用范围列时，同一列的MAXVALUE也可以出现在多个VALUES LESS THAN子句中。然而，在连续的分区定义中，单个列的限制值应该增加，不应 该有超过一个分区定义中有MAXVALUE，它用作所有列值的上限，该分区定义应该出现在分区列表的PARTITION ... VALUES LESS THAN子句的最后 。此外，您不能在多个分区定义中使用MAXVALUE作为第一列的限制值。
如前所述，范围列分区也可以使用非整数列作为分区列。考虑一个名为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 );
使用范围列分区，你可以创建这个表的一个版本，根据员工的姓氏将每一行存储在4个分区中的一个中，如下所示：
mysql> 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)
-> );
Query OK, 0 rows affected (0.02 sec)
或者，用户也可以通过执行下面的ALTER TABLE语句，对前面创建的employees表进行分区：
mysql> 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)
-> );
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
由于不同的字符集和排序规则具有不同的排序顺序，当使用字符串列作为分区列时，使用的字符集和排序规则可能会影响给定行的按范围列分区 的表的哪个分区。此外，在创建给定的数据库、表或列之后，更改其字符集或排序规则可能会导致行分布方式的更改。例如，当使用区分大小写 的排序规则时，` and `排在` Andersen `之前，但当使用不区分大小写的排序规则时，则相反。
类似地，用户可以使用下面的ALTER table语句对employees表进行分区，根据雇员的年龄，将表中的每一行存储在几个分区中的一个中：
mysql> 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)
-> );
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0