); 

When using list partitioning, you must define at least one partition using VALUES IN . You cannot use VALUES LESS THAN  with PARTITION BY LIST . 

• LIST COLUMNS( column_list ) 

Note 

For tables partitioned by LIST , the value list used with VALUES IN  must consist of integer values only. In MySQL 8.0, you can overcome this limitation using partitioning by LIST COLUMNS , which is described later in this section. 

This variant on LIST  facilitates partition pruning for queries using comparison conditions on multiple columns (that is, having conditions such as WHERE a = 5 AND b = 5  or WHERE a = 1 AND b = 10 AND c = 5 ). It enables you to specify values in multiple columns by using a list of columns in the COLUMNS  clause and a set of column values in each PARTITION ... VALUES IN ( value_list ) partition definition clause. 

The rules governing regarding data types for the column list used in LIST COLUMNS( column_list ) and the value list used in VALUES IN( value_list )  are the same as those for the column list used in RANGE COLUMNS( column_list )  and the value list used in VALUES LESS THAN( value_list ) , respectively, except that in the VALUES IN  clause, MAXVALUE  is not permitted, and you may use NULL . 

There is one important difference between the list of values used for VALUES IN  with PARTITION BY LIST COLUMNS  as opposed to when it is used with PARTITION BY LIST . When used with PARTITION BY LIST COLUMNS , each element in the VALUES IN  clause must be a set  of column values; the number of values in each set must be the same as the number of columns used in the COLUMNS  clause, and the data types of these values must match those of the columns (and occur in the same order). In the simplest case, the set consists of a single column. The maximum number of columns that can be used in the column_list  and in the elements making up the value_list  is 16. 

The table defined by the following CREATE TABLE  statement provides an example of a table using LIST COLUMNS  partitioning: 

CREATE TABLE lc ( a INT NULL, b INT NULL ) PARTITION BY LIST COLUMNS(a,b) ( PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) ); 

• PARTITIONS num 

The number of partitions may optionally be specified with a PARTITIONS num  clause, where num  is the number of partitions. If both this clause and  any PARTITION  clauses are used, num  must be equal to the total number of any partitions that are declared using PARTITION  clauses. 

Note 

Whether or not you use a PARTITIONS  clause in creating a table that is partitioned by RANGE  or LIST , you must still include at least one PARTITION VALUES  clause in the table definition (see below). 

