支持针对匹配给定 WHERE 条件的行显式选择分区和子分区。分区选择类似于分区剪枝，因为只检查特定分区的匹配情况，但有两个关键区别：Explicit selection of partitions and subpartitions for rows matching a given WHERE condition is supported. Partition selection is similar to partition pruning, in that only specific partitions are checked for matches, but differs in two key respects:

支持显式分区选择的 SQL 语句列在此处：SQL statements supporting explicit partition selection are listed here:

本节其余部分讨论显式分区选择在前述语句中的一般应用，并提供一些示例。The remainder of this section discusses explicit partition selection as it applies generally to the statements just listed, and provides some examples.

显式分区选择通过使用 PARTITION 选项实现。对于所有支持的语句，该选项使用如下所示的语法：Explicit partition selection is implemented using a PARTITION option. For all supported statements, this option uses the syntax shown here:

      PARTITION (partition_names)

      partition_names:
          partition_name, ...

此选项总是跟随分区所属表的名称。partition_names 是要使用的分区或子分区的逗号分隔列表。此列表中的每个名称必须是指定表中现有分区或子分区的名称；如果找不到任何分区或子分区，语句将以错误失败 (分区 'partition_name' 不存在)。在 partition_names 中列出的分区和子分区可以按任意顺序列出，且可能重叠。This option always follows the name of the table to which the partition or partitions belong. partition_names is a comma-separated list of partitions or subpartitions to be used. Each name in this list must be the name of an existing partition or subpartition of the specified table; if any of the partitions or subpartitions are not found, the statement fails with an error (partition 'partition_name' doesn't exist). Partitions and subpartitions named in partition_names may be listed in any order, and may overlap.

当使用 PARTITION 选项时，仅检查所列的分区和子分区中是否有匹配的行。此选项可以在 SELECT 语句中使用，以确定哪些行属于给定的分区。考虑一个名为 employees 的分区表，通过此处显示的语句创建和填充：When the PARTITION option is used, only the partitions and subpartitions listed are checked for matching rows. This option can be used in a SELECT statement to determine which rows belong to a given partition. Consider a partitioned table named employees, created and populated using the statements shown here:

SET @@SQL_MODE = '';

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)
    PARTITION BY RANGE(id)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (10),
        PARTITION p2 VALUES LESS THAN (15),
        PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

你可以像这样查看存储在分区 p1 中的行：You can see which rows are stored in partition p1 like this:

mysql> SELECT * FROM employees PARTITION (p1);
+----+-------+--------+----------+---------------+
| id | fname | lname  | store_id | department_id |
+----+-------+--------+----------+---------------+
|  5 | Mary  | Jones  |        1 |             1 |
|  6 | Linda | Black  |        2 |             3 |
|  7 | Ed    | Jones  |        2 |             1 |
|  8 | June  | Wilson |        3 |             1 |
|  9 | Andy  | Smith  |        1 |             3 |
+----+-------+--------+----------+---------------+
5 rows in set (0.00 sec)

结果与查询 SELECT * FROM employees WHERE id BETWEEN 5 AND 9 所获得的相同。The result is the same as obtained by the query SELECT * FROM employees WHERE id BETWEEN 5 AND 9.

要从多个分区中获取行，请将它们的名称作为逗号分隔的列表提供。例如，SELECT * FROM employees PARTITION (p1, p2) 会返回分区 p1 和 p2 中的所有行，同时排除其余分区的行。To obtain rows from multiple partitions, supply their names as a comma-delimited list. For example, SELECT * FROM employees PARTITION (p1, p2) returns all rows from partitions p1 and p2 while excluding rows from the remaining partitions.

对分区表的任何有效查询都可以使用 PARTITION 选项重写，以将结果限制为一个或多个所需分区。您可以使用 WHERE 条件、ORDER BY 和 LIMIT 选项等。您还可以使用带有 HAVING 和 GROUP BY 选项的聚合函数。以下每个查询在对先前定义的 employees 表运行时都会产生有效结果：Any valid query against a partitioned table can be rewritten with a PARTITION option to restrict the result to one or more desired partitions. You can use WHERE conditions, ORDER BY and LIMIT options, and so on. You can also use aggregate functions with HAVING and GROUP BY options. Each of the following queries produces a valid result when run on the employees table as previously defined:

mysql> SELECT * FROM employees PARTITION (p0, p2)
    ->     WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name           |
+----+----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)

mysql> SELECT store_id, COUNT(department_id) AS c
    ->     FROM employees PARTITION (p1,p2,p3)
    ->     GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 |        2 |
| 5 |        3 |
+---+----------+
2 rows in set (0.00 sec)

使用分区选择的语句可以应用于使用任何受支持分区类型的表。当使用 [LINEAR] HASH 或 [LINEAR] KEY 分区创建表且未指定分区名称时，MySQL 会自动将分区命名为 p0、p1、p2、...、pN-1，其中 N 是分区的数量。对于未明确命名的子分区，MySQL 会自动为每个分区 pX 中的子分区分配名称 pXsp0、pXsp1、pXsp2、...、pXspM-1，其中 M 是子分区的数量。在对该表执行 SELECT（或允许显式分区选择的其他 SQL 语句）时，您可以在 PARTITION 选项中使用这些生成的名称，如下所示：Statements using partition selection can be employed with tables using any of the supported partitioning types. When a table is created using [LINEAR] HASH or [LINEAR] KEY partitioning and the names of the partitions are not specified, MySQL automatically names the partitions p0, p1, p2, ..., pN-1, where N is the number of partitions. For subpartitions not explicitly named, MySQL assigns automatically to the subpartitions in each partition pX the names pXsp0, pXsp1, pXsp2, ..., pXspM-1, where M is the number of subpartitions. When executing against this table a SELECT (or other SQL statement for which explicit partition selection is allowed), you can use these generated names in a PARTITION option, as shown here:

mysql> CREATE TABLE employees_sub  (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     fname VARCHAR(25) NOT NULL,
    ->     lname VARCHAR(25) NOT NULL,
    ->     store_id INT NOT NULL,
    ->     department_id INT NOT NULL,
    ->     PRIMARY KEY pk (id, lname)
    -> )
    ->     PARTITION BY RANGE(id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (5),
    ->         PARTITION p1 VALUES LESS THAN (10),
    ->         PARTITION p2 VALUES LESS THAN (15),
    ->         PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (1.14 sec)

mysql> INSERT INTO employees_sub   # reuse data in employees table
    ->     SELECT * FROM employees;
Query OK, 18 rows affected (0.09 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees_sub PARTITION (p2sp1);
+----+---------------+
| id | name          |
+----+---------------+
| 10 | Lou Waters    |
| 14 | Fred Goldberg |
+----+---------------+
2 rows in set (0.00 sec)

您也可以在 SELECT 部分的 PARTITION 语句中使用 INSERT ... SELECT 选项，如下所示：You may also use a PARTITION option in the SELECT portion of an INSERT ... SELECT statement, as shown here:

mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO employees_copy
    ->     SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname  | lname    | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou    | Waters   |        2 |             4 |
| 11 | Jill   | Stone    |        1 |             4 |
| 12 | Roger  | White    |        3 |             2 |
| 13 | Howard | Andrews  |        1 |             2 |
| 14 | Fred   | Goldberg |        3 |             3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)

分区选择也可以与连接一起使用。假设我们使用这里显示的语句创建并填充两个表：Partition selection can also be used with joins. Suppose we create and populate two tables using the statements shown here:

CREATE TABLE stores (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    city VARCHAR(30) NOT NULL
)
    PARTITION BY HASH(id)
    PARTITIONS 2;

INSERT INTO stores VALUES
    ('', 'Nambucca'), ('', 'Uranga'),
    ('', 'Bellingen'), ('', 'Grafton');

CREATE TABLE departments  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
)
    PARTITION BY KEY(id)
    PARTITIONS 2;

INSERT INTO departments VALUES
    ('', 'Sales'), ('', 'Customer Service'),
    ('', 'Delivery'), ('', 'Accounting');

您可以显式选择连接中任意或所有表的分区（或子分区，或两者）。(PARTITION 选项用于从给定表中选择分区，它紧跟在表名之后，在所有其他选项之前，包括任何表别名。) 例如，以下查询获取所有在销售部或配送部工作的员工的姓名、员工编号、部门和城市（departments 表的分区 p1），这些员工位于 Nambucca 和 Bellingen 两个城市的商店（stores 表的分区 p0）：You can explicitly select partitions (or subpartitions, or both) from any or all of the tables in a join. (The PARTITION option used to select partitions from a given table immediately follows the name of the table, before all other options, including any table alias.) For example, the following query gets the name, employee ID, department, and city of all employees who work in the Sales or Delivery department (partition p1 of the departments table) at the stores in either of the cities of Nambucca and Bellingen (partition p0 of the stores table):

mysql> SELECT
    ->     e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
    ->     s.city AS City, d.name AS department
    -> FROM employees AS e
    ->     JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
    ->     JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
    -> ORDER BY e.lname;
+-------------+---------------+-----------+------------+
| Employee ID | Name          | City      | department |
+-------------+---------------+-----------+------------+
|          14 | Fred Goldberg | Bellingen | Delivery   |
|           5 | Mary Jones    | Nambucca  | Sales      |
|          17 | Mark Morgan   | Bellingen | Delivery   |
|           9 | Andy Smith    | Nambucca  | Delivery   |
|           8 | June Wilson   | Bellingen | Sales      |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)

有关 MySQL 中连接的一般信息，请参见 第 15.2.13.2 节，“JOIN 子句”。For general information about joins in MySQL, see Section 15.2.13.2, “JOIN Clause”.

当在 DELETE 语句中使用 PARTITION 选项时，仅会检查使用该选项列出的分区（以及子分区，如果有的话）中的行是否需要删除。其他分区将被忽略，如下所示：When the PARTITION option is used with DELETE statements, only those partitions (and subpartitions, if any) listed with the option are checked for rows to be deleted. Any other partitions are ignored, as shown here:

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname  | store_id | department_id |
+----+-------+--------+----------+---------------+
|  4 | Jim   | Smith  |        2 |             4 |
|  8 | June  | Wilson |        3 |             1 |
| 11 | Jill  | Stone  |        1 |             4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM employees PARTITION (p0, p1)
    ->     WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

只有分区 p0 和 p1 中符合 WHERE 条件的两行被删除。正如你在第二次运行 SELECT 的结果中所看到的，表中仍然存在一行匹配 WHERE 条件，但位于不同分区（p2）的行。Only the two rows in partitions p0 and p1 matching the WHERE condition were deleted. As you can see from the result when the SELECT is run a second time, there remains a row in the table matching the WHERE condition, but residing in a different partition (p2).

UPDATE 语句使用显式分区选择时表现相同；在确定要更新的行时，只有由 PARTITION 选项指定的分区中的行会被考虑，如执行以下语句所示：UPDATE statements using explicit partition selection behave in the same way; only rows in the partitions referenced by the PARTITION option are considered when determining the rows to be updated, as can be seen by executing the following statements:

mysql> UPDATE employees PARTITION (p0) 
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

mysql> UPDATE employees PARTITION (p2)
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        2 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

同样，当 PARTITION 与 DELETE 一起使用时，仅检查分区列表中列出的分区中的行是否需要删除。In the same way, when PARTITION is used with DELETE, only rows in the partition or partitions named in the partition list are checked for deletion.

对于插入行的语句，其行为有所不同，因为未能找到合适的分区会导致语句失败。这对于 INSERT 和 REPLACE 语句都是如此，如下所示：For statements that insert rows, the behavior differs in that failure to find a suitable partition causes the statement to fail. This is true for both INSERT and REPLACE statements, as shown here:

mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)

mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)

对于将多行写入使用 InnoDB 存储引擎的分区表的语句：如果 VALUES 后面的列表中的任何一行无法写入 partition_names 列表中指定的分区之一，则整个语句会失败且不会写入任何行。下面的示例显示了这种情况，针对 INSERT 语句，重复使用之前创建的 employees 表：For statements that write multiple rows to a partitioned table that using the InnoDB storage engine: If any row in the list following VALUES cannot be written to one of the partitions specified in the partition_names list, the entire statement fails and no rows are written. This is shown for INSERT statements in the following example, reusing the employees table created previously:

mysql> ALTER TABLE employees
    ->     REORGANIZE PARTITION p3 INTO (
    ->         PARTITION p3 VALUES LESS THAN (20),
    ->         PARTITION p4 VALUES LESS THAN (25),
    ->         PARTITION p5 VALUES LESS THAN MAXVALUE
    ->     );
Query OK, 6 rows affected (2.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(25) NOT NULL,
  `store_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> INSERT INTO employees PARTITION (p3, p4) VALUES
    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> INSERT INTO employees PARTITION (p3, p4, p5) VALUES
    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

上述情况对于写入多行的 INSERT 语句和 REPLACE 语句都是适用的。The preceding is true for both INSERT statements and REPLACE statements that write multiple rows.

对于使用提供自动分区的存储引擎（例如 NDB）的表，分区选择已被禁用。Partition selection is disabled for tables employing a storage engine that supplies automatic partitioning, such as NDB.