List partitioning in MySQL is similar to range partitioning in many ways. As in partitioning by RANGE, each partition must be explicitly defined. The chief difference is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. This is done by using PARTITION BY LIST(expr) where expr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers.

Note: In MySQL 5.1, it is possible to match against only a list of integers (and possibly NULL — see Section 16.2.6, “How MySQL Partitioning Handles NULL Values”) when partitioning by LIST.

Unlike the case with partitions defined by range, list partitions do not need to be declared in any particular order. For more detailed syntactical information, see Section 13.1.8, “CREATE TABLE Syntax”.

For the examples that follow, we assume that the basic definition of the table to be partitioned is provided by the CREATE TABLE statement shown here:

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,
    store_id INT
);

(This is the same table used as a basis for the examples in Section 16.2.1, “RANGE Partitioning”.)

Suppose that there are 20 video stores distributed among 4 franchises as shown in the following table:

To partition this table in such a way that rows for stores belonging to the same region are stored in the same partition, you could use the CREATE TABLE statement shown here:

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,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

This makes it easy to add or drop employee records relating to specific regions to or from the table. For instance, suppose that all stores in the West region are sold to another company. All rows relating to employees working at stores in that region can be deleted with the query ALTER TABLE employees DROP PARTITION pWest;, which can be executed much more efficiently than the equivalent DELETE statement DELETE FROM employees WHERE store_id IN (4,12,13,14,18);.

As with RANGE and HASH partitioning, if you wish to partition a table by a column whose value is not an integer or NULL, you must employ a partitioning expression based on that column which returns such a value. For example, suppose that the table containing employee data is defined as shown here:

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 CHAR(1),
    store_id INT
);

In this version of the employees table, the job code is a letter rather than a number. Each letter corresponds to a specific job, and we wish to partition the table in such a way that records for employees having similar jobs or working in the same department are grouped into the same partition, according to the following scheme:

Since we cannot use character values in value-lists, we need to convert these into integers or NULLs. For this purpose, we can use the ASCII() function on the column value. A partitioned table that implements this scheme is shown here:

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 CHAR(1),
    store_id INT
) 
PARTITION BY LIST( ASCII(job_code) ) (
    PARTITION management VALUES IN(68, 77, 79, 80),
    PARTITION sales VALUES IN(66, 76, 83),
    PARTITION technical VALUES IN(65, 69, 71, 73, 84),
    PARTITION clerical VALUES IN(75, 78, 89),
    PARTITION support VALUES IN(67, 70, 74, 82, 86),
    PARTITION unassigned VALUES IN(NULL, 0, 32)
);

Since expressions are not permitted in partition value lists, you must list the ASCII codes for the letters that are to be matched. Note that ASCII(NULL) returns NULL.

Important: If you try to insert a row such that the column value (or the partitioning expression's return value) is not found in any of the partitioning value lists, the INSERT query will fail with an error. For example, given the LIST partitioning scheme just outlined, this query will fail:

INSERT INTO employees VALUES 
    (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 'Q', 21);

Failure occurs because 81 (the ASCII code for the uppercase letter 'Q' is not found in any of the value lists used to define any of the partitions. There is no “catch-all” definition for list partitions analogous to VALUES LESS THAN(MAXVALUE) which accommodates values not found in any of the value lists. In other words, any value which is to be matched must be found in one of the value lists.

As with RANGE partitioning, it is possible to combine LIST partitioning with partitioning by hash or key to produce a composite partitioning (subpartitioning). See Section 16.2.5, “Subpartitioning”.