MySQL 中的列表分区在许多方面类似于范围分区。正如使用 RANGE 进行分区一样，每个分区都必须明确定义。这两种分区类型的主要区别在于，在列表分区中，每个分区是根据某列值是否属于一组值列表中的成员来定义和选择的，而不是是否属于一组连续的值范围。这是通过使用 PARTITION BY LIST(expr) 实现的，其中 expr 是一个列值或基于列值的表达式并返回一个整数值，然后通过 VALUES IN (value_list) 定义每个分区，其中 value_list 是一个以逗号分隔的整数列表。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.

与按范围定义的分区不同，列表分区无需按特定顺序声明。有关更详细的语法信息，请参见 第 15.1.20 节，“CREATE TABLE 语句”。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 15.1.20, “CREATE TABLE Statement”.

对于下面的示例，我们假设要分区的表的基本定义由以下 CREATE TABLE 语句提供：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
);

（这是与 第 26.2.1 节，“范围分区” 示例相同的表。与其他分区示例一样，我们假设 default_storage_engine 为 InnoDB。）(This is the same table used as a basis for the examples in Section 26.2.1, “RANGE Partitioning”. As with the other partitioning examples, we assume that the default_storage_engine is InnoDB.)

假设有 20 家视频商店分布在 4 个连锁机构中，如下表所示。Suppose that there are 20 video stores distributed among 4 franchises as shown in the following table.

要以这样的方式对该表进行分区，使属于同一地区的商店的行存储在同一分区中，可以使用下列显示的 CREATE 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)
);

这使得将与特定地区相关的员工记录添加到表中或从表中删除变得非常容易。例如，假设西部地区的所有商店都卖给了另一家公司。在 MySQL 8.4 中，所有与该地区商店工作相关的员工行都可以通过查询 ALTER TABLE employees TRUNCATE PARTITION pWest 删除，其执行效率远高于等效的 DELETE 语句 DELETE FROM employees WHERE store_id IN (4,12,13,14,18);。（使用 ALTER TABLE employees DROP PARTITION pWest 也会删除所有这些行，但同时会从表的定义中移除分区 pWest；您需要使用 ALTER TABLE ... ADD PARTITION 语句来恢复表的原始分区方案。）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. In MySQL 8.4, all rows relating to employees working at stores in that region can be deleted with the query ALTER TABLE employees TRUNCATE 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);. (Using ALTER TABLE employees DROP PARTITION pWest would also delete all of these rows, but would also remove the partition pWest from the definition of the table; you would need to use an ALTER TABLE ... ADD PARTITION statement to restore the table's original partitioning scheme.)

与 RANGE 分区一样，可以将 LIST 分区与按哈希或键分区结合，以生成复合分区（子分区）。请参见 第 26.2.6 节，“子分区”。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 26.2.6, “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:

mysql> CREATE TABLE h2 (
    ->   c1 INT,
    ->   c2 INT
    -> )
    -> PARTITION BY LIST(c1) (
    ->   PARTITION p0 VALUES IN (1, 4, 7),
    ->   PARTITION p1 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3

当使用单个INSERT语句向单个InnoDB表插入多行时，InnoDB将该语句视为单个事务，因此任何不匹配的值都会导致语句完全失败，从而没有行被插入。When inserting multiple rows using a single INSERT statement into a single InnoDB table, InnoDB considers the statement a single transaction, so that the presence of any unmatched values causes the statement to fail completely, and so no rows are inserted.

您可以使用IGNORE关键字使此类错误被忽略，尽管每个包含不匹配分区列值的行都会发出警告，如下所示。You can cause this type of error to be ignored by using the IGNORE keyword, although a warning is issued for each row containing unmatched partitioning column values, as shown here.

mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)

mysql> TABLE h2;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 2

mysql> SHOW WARNINGS;
+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 1526 | Table has no partition for value 6 |
| Warning | 1526 | Table has no partition for value 3 |
+---------+------+------------------------------------+
2 rows in set (0.00 sec)

您可以在以下 TABLE 语句的输出中看到，包含不匹配分区列值的行被悄然拒绝，而不包含不匹配值的行则被插入到表中：You can see in the output of the following TABLE statement that rows containing unmatched partitioning column values were silently rejected, while rows containing no unmatched values were inserted into the table:

mysql> TABLE h2;
+------+------+
| c1   | c2   |
+------+------+
|    7 |    5 |
|    1 |    9 |
|    2 |    5 |
+------+------+
3 rows in set (0.00 sec)

MySQL 还提供对 LIST COLUMNS 分区的支持，这是一种 LIST 分区的变体，使您可以使用非整数类型的列作为分区列，并可以使用多列作为分区键。有关更多信息，请参见 第 26.2.3.2 节，“LIST COLUMNS 分区”。MySQL also provides support for LIST COLUMNS partitioning, a variant of LIST partitioning that enables you to use columns of types other than integer for partitioning columns, and to use multiple columns as partitioning keys. For more information, see Section 26.2.3.2, “LIST COLUMNS partitioning”.