in the COLUMNS  clause and a set of column values in each PARTITION ... VALUES LESS THAN ( value_list )  partition definition clause. (In the simplest case, this set consists of a single column.) The maximum number of columns that can be referenced in the column_list  and value_list  is 16. 

The column_list  used in the COLUMNS  clause may contain only names of columns; each column in the list must be one of the following MySQL data types: the integer types; the string types; and time or date column types. Columns using BLOB , TEXT , SET , ENUM , BIT , or spatial data types are not permitted; columns that use floating-point number types are also not permitted. You also may not use functions or arithmetic expressions in the COLUMNS  clause. 

The VALUES LESS THAN  clause used in a partition definition must specify a literal value for each column that appears in the COLUMNS()  clause; that is, the list of values used for each VALUES LESS THAN  clause must contain the same number of values as there are columns listed in the COLUMNS clause. An attempt to use more or fewer values in a VALUES LESS THAN  clause than there are in the COLUMNS  clause causes the statement to fail with the error Inconsistency in usage of column lists for partitioning... . You cannot use NULL  for any value appearing in VALUES LESS THAN . It is possible to use MAXVALUE  more than once for a given column other than the first, as shown in this example: 

CREATE TABLE rc ( a INT NOT NULL, b INT NOT NULL ) PARTITION BY RANGE COLUMNS(a,b) ( PARTITION p0 VALUES LESS THAN (10,5), PARTITION p1 VALUES LESS THAN (20,10), PARTITION p2 VALUES LESS THAN (50,MAXVALUE), PARTITION p3 VALUES LESS THAN (65,MAXVALUE), PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE) ); 

Each value used in a VALUES LESS THAN  value list must match the type of the corresponding column exactly; no conversion is made. For example, you cannot use the string ’1’  for a value that matches a column that uses an integer type (you must use the numeral 1  instead), nor can you use the numeral 1 for a value that matches a column that uses a string type (in such a case, you must use a quoted string: ’1’ ). 

For more information, see Section 24.2.1, “RANGE Partitioning” , and Section 24.4, “Partition Pruning” . 

• LIST( expr ) 

This is useful when assigning partitions based on a table column with a restricted set of possible values, such as a state or country code. In such a case, all rows pertaining to a certain state or country can be assigned to a single partition, or a partition can be reserved for a certain set of states or countries. It is similar to RANGE , except that only VALUES IN  may be used to specify permissible values for each partition. 

VALUES IN  is used with a list of values to be matched. For instance, you could create a partitioning scheme such as the following: 

CREATE TABLE client_firms ( id INT, name VARCHAR(35) ) PARTITION BY LIST (id) ( PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) 

