18.2.2 LIST Partitioning

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
between the two types of partitioning 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.

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.17, “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:

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 an ALTER TABLE employees DROP
PARTITION pWest statement, which can be executed much
more efficiently than the equivalent
DELETE statement (DELETE
FROM employees WHERE store_id IN (4,12,13,14,18)).
However, the ALTER
TABLE ... DROP PARTITION statement also removes the
partition itself from the definition for the table, and you must
execute an ALTER
TABLE ... ADD PARTITION statement to restore the
table's original partitioning scheme. (This problem is
resolved in MySQL 5.5, which adds the
ALTER TABLE ...
TRUNCATE PARTITION statement for removing all rows
from a given partition without affecting the table definition.)

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 18.2.5, “Subpartitioning”.

Unlike the case with RANGE partitioning,
there is no “catch-all” such as
MAXVALUE; all expected values for the
partitioning expression should be covered in PARTITION
... VALUES IN (...) clauses. An
INSERT statement containing an
unmatched partitioning column value fails with an error, as
shown in this example:

When inserting multiple rows using a single
INSERT statement the behavior
depends on whether the table uses a transactional storage
engine. For an InnoDB table, the
statement is considered a single transaction, so the presence of
any unmatched values causes the statement to fail completely,
and no rows are inserted. For a table using a nontransactional
storage engine such as MyISAM, any
rows coming before the row containing the unmatched value are
inserted, but any coming after it are not.

You can cause this type of error to be ignored by using the
IGNORE keyword. If you do so, rows containing
unmatched partitioning column values are not inserted, but any
rows with matching values are inserted, and
no errors are reported: