Range columns partitioning is similar to range partitioning, but enables you to define partitions using ranges based on multiple column values. In addition, you can define the ranges using columns of types other than integer types.
RANGE COLUMNS
partitioning differs
significantly from RANGE
partitioning in
the following ways:
RANGE COLUMNS
does not accept expressions, only names of columns.RANGE COLUMNS
accepts a list of one or more columns.RANGE COLUMNS
partitions are based on comparisons between tuples (lists of column values) rather than comparisons between scalar values. Placement of rows inRANGE COLUMNS
partitions is also based on comparisons between tuples; this is discussed further later in this section.RANGE COLUMNS
partitioning columns are not restricted to integer columns; string,DATE
andDATETIME
columns can also be used as partitioning columns. (See Section 26.2.3, “COLUMNS Partitioning”, for details.)
The basic syntax for creating a table partitioned by
RANGE COLUMNS
is shown here:
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][, ...]
Not all CREATE TABLE
options
that can be used when creating partitioned tables are shown
here. For complete information, see
Section 15.1.20, “CREATE TABLE Statement”.
In the syntax just shown,
column_list
is a list of one or
more columns (sometimes called a
partitioning column
list), and value_list
is
a list of values (that is, it is a
partition definition value
list). A value_list
must
be supplied for each partition definition, and each
value_list
must have the same
number of values as the column_list
has columns. Generally speaking, if you use
N
columns in the
COLUMNS
clause, then each VALUES
LESS THAN
clause must also be supplied with a list
of N
values.
The elements in the partitioning column list and in the value
list defining each partition must occur in the same order. In
addition, each element in the value list must be of the same
data type as the corresponding element in the column list.
However, the order of the column names in the partitioning
column list and the value lists does not have to be the same
as the order of the table column definitions in the main part
of the CREATE TABLE
statement.
As with table partitioned by RANGE
, you can
use MAXVALUE
to represent a value such that
any legal value inserted into a given column is always less
than this value. Here is an example of a
CREATE TABLE
statement that
helps to illustrate all of these points:
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)
Table rcx
contains the columns
a
, b
,
c
, d
. The partitioning
column list supplied to the COLUMNS
clause
uses 3 of these columns, in the order a
,
d
, c
. Each value list
used to define a partition contains 3 values in the same
order; that is, each value list tuple has the form
(INT
, INT
,
CHAR(3)
), which corresponds to the data
types used by columns a
,
d
, and c
(in that
order).
Placement of rows into partitions is determined by comparing
the tuple from a row to be inserted that matches the column
list in the COLUMNS
clause with the tuples
used in the VALUES LESS THAN
clauses to
define partitions of the table. Because we are comparing
tuples (that is, lists or sets of values) rather than scalar
values, the semantics of VALUES LESS THAN
as used with RANGE COLUMNS
partitions
differs somewhat from the case with simple
RANGE
partitions. In
RANGE
partitioning, a row generating an
expression value that is equal to a limiting value in a
VALUES LESS THAN
is never placed in the
corresponding partition; however, when using RANGE
COLUMNS
partitioning, it is sometimes possible for a
row whose partitioning column list's first element is
equal in value to the that of the first element in a
VALUES LESS THAN
value list to be placed in
the corresponding partition.
Consider the RANGE
partitioned table
created by this statement:
CREATE TABLE r1 (
a INT,
b INT
)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
If we insert 3 rows into this table such that the column value
for a
is 5
for each row,
all 3 rows are stored in partition p1
because the a
column value is in each case
not less than 5, as we can see by executing the proper query
against the Information Schema
PARTITIONS
table:
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)
Now consider a similar table rc1
that uses
RANGE COLUMNS
partitioning with both
columns a
and b
referenced in the COLUMNS
clause, created
as shown here:
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)
);
If we insert exactly the same rows into rc1
as we just inserted into r1
, the
distribution of the rows is quite different:
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)
This is because we are comparing rows rather than scalar
values. We can compare the row values inserted with the
limiting row value from the VALUES THAN LESS
THAN
clause used to define partition
p0
in table rc1
, like
this:
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)
The 2 tuples (5,10)
and
(5,11)
evaluate as less than
(5,12)
, so they are stored in partition
p0
. Since 5 is not less than 5 and 12 is
not less than 12, (5,12)
is considered not
less than (5,12)
, and is stored in
partition p1
.
The SELECT
statement in the
preceding example could also have been written using explicit
row constructors, like this:
SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);
For more information about the use of row constructors in MySQL, see Section 15.2.15.5, “Row Subqueries”.
For a table partitioned by RANGE COLUMNS
using only a single partitioning column, the storing of rows
in partitions is the same as that of an equivalent table that
is partitioned by RANGE
. The following
CREATE TABLE
statement creates a table
partitioned by RANGE COLUMNS
using 1
partitioning column:
CREATE TABLE rx (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS (a) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
If we insert the rows (5,10)
,
(5,11)
, and (5,12)
into
this table, we can see that their placement is the same as it
is for the table r
we created and populated
earlier:
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)
It is also possible to create tables partitioned by
RANGE COLUMNS
where limiting values for one
or more columns are repeated in successive partition
definitions. You can do this as long as the tuples of column
values used to define the partitions are strictly increasing.
For example, each of the following CREATE
TABLE
statements is valid:
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)
);
The following statement also succeeds, even though it might
appear at first glance that it would not, since the limiting
value of column b
is 25 for partition
p0
and 20 for partition
p1
, and the limiting value of column
c
is 100 for partition
p1
and 50 for partition
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)
);
When designing tables partitioned by RANGE
COLUMNS
, you can always test successive partition
definitions by comparing the desired tuples using the
mysql client, like this:
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)
If a CREATE TABLE
statement
contains partition definitions that are not in strictly
increasing order, it fails with an error, as shown in this
example:
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
When you get such an error, you can deduce which partition
definitions are invalid by making “less than”
comparisons between their column lists. In this case, the
problem is with the definition of partition
p2
because the tuple used to define it is
not less than the tuple used to define partition
p3
, as shown here:
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)
It is also possible for MAXVALUE
to appear
for the same column in more than one VALUES LESS
THAN
clause when using RANGE
COLUMNS
. However, the limiting values for individual
columns in successive partition definitions should otherwise
be increasing, there should be no more than one partition
defined where MAXVALUE
is used as the upper
limit for all column values, and this partition definition
should appear last in the list of PARTITION ...
VALUES LESS THAN
clauses. In addition, you cannot
use MAXVALUE
as the limiting value for the
first column in more than one partition definition.
As stated previously, it is also possible with RANGE
COLUMNS
partitioning to use non-integer columns as
partitioning columns. (See
Section 26.2.3, “COLUMNS Partitioning”, for a complete listing
of these.) Consider a table named employees
(which is not partitioned), created using the following
statement:
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
);
Using RANGE COLUMNS
partitioning, you can
create a version of this table that stores each row in one of
four partitions based on the employee's last name, like
this:
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)
);
Alternatively, you could cause the
employees
table as created previously to be
partitioned using this scheme by executing the following
ALTER
TABLE
statement:
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)
);
Because different character sets and collations have
different sort orders, the character sets and collations in
use may effect which partition of a table partitioned by
RANGE COLUMNS
a given row is stored in
when using string columns as partitioning columns. In
addition, changing the character set or collation for a
given database, table, or column after such a table is
created may cause changes in how rows are distributed. For
example, when using a case-sensitive collation,
'and'
sorts before
'Andersen'
, but when using a collation
that is case-insensitive, the reverse is true.
For information about how MySQL handles character sets and collations, see Chapter 12, Character Sets, Collations, Unicode.
Similarly, you can cause the employees
table to be partitioned in such a way that each row is stored
in one of several partitions based on the decade in which the
corresponding employee was hired using the
ALTER
TABLE
statement shown here:
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)
);
See Section 15.1.20, “CREATE TABLE Statement”, for additional information
about PARTITION BY RANGE COLUMNS
syntax.