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 COLUMNSpartitioning differs significantly fromRANGEpartitioning in the following ways:

The basic syntax for creating a table partitioned byRANGE COLUMNSis shown here:

CREATE TABLEtable_namePARTITION BY RANGE COLUMNS(column_list) ( PARTITIONpartition_nameVALUES LESS THAN (value_list)[, PARTITIONpartition_nameVALUES LESS THAN (value_list)][, ...] )column_list:column_name[,column_name][, ...]value_list:value[,value][, ...]

In the syntax just shown,column_listis a list of one or more columns (sometimes called apartitioning column list), andvalue_listis a list of values (that is, it is apartition definition value list). Avalue_listmust be supplied for each partition definition, and eachvalue_listmust have the same number of values as thecolumn_listhas columns. Generally speaking, if you useNcolumns in theCOLUMNSclause, then eachVALUES LESS THANclause must also be supplied with a list ofNvalues.

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 theCREATE TABLEstatement. As with table partitioned byRANGE, you can useMAXVALUEto represent a value such that any legal value inserted into a given column is always less than this value. Here is an example of aCREATE TABLEstatement 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)

Tablercxcontains the columnsa,b,c,d. The partitioning column list supplied to theCOLUMNSclause uses 3 of these columns, in the ordera,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)),它对应于所使用的数据类型umnsa,d, andc(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 theCOLUMNSclause with the tuples used in theVALUES LESS THANclauses to define partitions of the table. Because we are comparing tuples (that is, lists or sets of values) rather than scalar values, the semantics ofVALUES LESS THANas used withRANGE COLUMNSpartitions differs somewhat from the case with simpleRANGEpartitions. InRANGEpartitioning, a row generating an expression value that is equal to a limiting value in aVALUES LESS THANis never placed in the corresponding partition; however, when usingRANGE COLUMNSpartitioning, 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 aVALUES LESS THANvalue list to be placed in the corresponding partition.

Consider theRANGEpartitioned 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 forais5for each row, all 3 rows are stored in partitionp1because theacolumn value is in each case not less than 5, as we can see by executing the proper query against theINFORMATION_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)

Now consider a similar tablerc1that usesRANGE COLUMNSpartitioning with both columnsaandbreferenced in theCOLUMNSclause, 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 intorc1as we just inserted intor1, 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'; +--------------+----------------+------------+ | TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS | +--------------+----------------+------------+ | p | p0 | 2 | | p | p1 | 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 theVALUES THAN LESS THANclause used to define partitionp0in tablerc1, 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 partitionp0. 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 partitionp1.

TheSELECTstatement 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, seeSection 13.2.11.5, “Row Subqueries”.

For a table partitioned byRANGE COLUMNSusing only a single partitioning column, the storing of rows in partitions is the same as that of an equivalent table that is partitioned byRANGE. The followingCREATE TABLEstatement creates a table partitioned byRANGE COLUMNSusing 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 tablerwe created and populated earlier:

mysql >插入rx值(10),(11),(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)

It is also possible to create tables partitioned byRANGE COLUMNSwhere 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 followingCREATE TABLE声明是有效的:

创建表rc2 b (INT, INT)分区GE 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 columnbis 25 for partitionp0and 20 for partitionp1, and the limiting value of columncis 100 for partitionp1and 50 for partitionp2:

创建表rc4 (INT,我NT, 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 byRANGE COLUMNS, you can always test successive partition definitions by comparing the desired tuples using themysqlclient, 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 aCREATE TABLEstatement 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 partitionp2because the tuple used to define it is not less than the tuple used to define partitionp3, 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 forMAXVALUEto appear for the same column in more than oneVALUES LESS THANclause when usingRANGE 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 whereMAXVALUEis used as the upper limit for all column values, and this partition definition should appear last in the list ofPARTITION ... VALUES LESS THANclauses. In addition, you cannot useMAXVALUEas the limiting value for the first column in more than one partition definition.

As stated previously, it is also possible withRANGE COLUMNSpartitioning to use non-integer columns as partitioning columns. (SeeSection 24.2.3, “COLUMNS Partitioning”, for a complete listing of these.) Consider a table namedemployees(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 );

UsingRANGE COLUMNSpartitioning, 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 theemployeestable as created previously to be partitioned using this scheme by executing the followingALTER TABLEstatement:

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) );

Similarly, you can cause theemployeestable 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 theALTER TABLEstatement 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) );

SeeSection 13.1.20, “CREATE TABLE Statement”, for additional information aboutPARTITION BY RANGE COLUMNSsyntax.