content_image/791603.jpg
• RANGE( expr ) 

In this case, expr  shows a range of values using a set of VALUES LESS THAN  operators. When using range partitioning, you must define at least one partition using VALUES LESS THAN . You cannot use VALUES IN  with range partitioning. 

## Note 

For tables partitioned by RANGE , VALUES LESS THAN  must be used with either an integer literal value or an expression that evaluates to a single integer value. In MySQL 8.0, you can overcome this limitation in a table that is defined using PARTITION BY RANGE COLUMNS , as described later in this section. 

Suppose that you have a table that you wish to partition on a column containing year values, according to the following scheme. 

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

A table implementing such a partitioning scheme can be realized by the CREATE TABLE  statement shown here: 

CREATE TABLE t1 ( year_col INT, some_data INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2002), PARTITION p4 VALUES LESS THAN (2006), PARTITION p5 VALUES LESS THAN MAXVALUE ); 

PARTITION ... VALUES LESS THAN ...  statements work in a consecutive fashion. VALUES LESS THAN MAXVALUE  works to specify “leftover”  values that are greater than the maximum value otherwise specified. 

VALUES LESS THAN  clauses work sequentially in a manner similar to that of the case  portions of a switch ... case  block (as found in many programming languages such as C, Java, and PHP). That is, the clauses must be arranged in such a way that the upper limit specified in each successive VALUES LESS THAN  is greater than that of the previous one, with the one referencing MAXVALUE  coming last of all in the list. 

• RANGE COLUMNS( column_list ) 

This variant on RANGE  facilitates partition pruning for queries using range conditions on multiple columns (that is, having conditions such as WHERE a = 1 AND b < 10  or WHERE a = 1 AND b = 10 AND c < 10 ). It enables you to specify value ranges in multiple columns by using a list of columns 

