content_image/801080.jpg
-> 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’; 

<img src='content_image/801080.jpg'>

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 ) 

