MySQL :: MySQL 8.3 Reference Manual :: 26.3.1 Management of RANGE and LIST Partitions

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Management of RANGE and LIST Partitions

26.3.1 范围和列表分区管理

添加和删除范围和列表分区的处理方式相似，因此我们在本节中讨论这两种分区管理方式。有关使用哈希或键分区表的信息，请参见第 26.3.2 节，“哈希和键分区管理”。

从使用范围或列表分区的表中删除分区可以使用ALTER TABLE语句的 DROP PARTITION 选项。假设您创建了一个使用范围分区的表，然后使用以下 CREATE TABLE 和 INSERT 语句填充了 10 条记录：

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005),
    ->         PARTITION p4 VALUES LESS THAN (2010),
    ->         PARTITION p5 VALUES LESS THAN (2015)
    ->     );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'alarm clock', '1997-11-05'),
    ->     (3, 'chair', '2009-03-10'),
    ->     (4, 'bookcase', '1989-01-10'),
    ->     (5, 'exercise bike', '2014-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'espresso maker', '2011-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '2006-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

您可以看到哪些项目应该被插入到分区 p2 中，如下所示：

mysql> SELECT * FROM tr
    ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

您也可以使用分区选择来获取这些信息，如下所示：

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

有关更多信息，请参见第 26.5 节，“分区选择”。

要删除名为 p2 的分区，请执行以下命令：

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

Note

NDBCLUSTER 存储引擎不支持 ALTER TABLE ... DROP PARTITION。然而，它支持本章中描述的其他与分区相关的 ALTER TABLE 扩展。

非常重要的是，当您删除一个分区时，也将删除该分区中存储的所有数据。您可以通过重新运行之前的 SELECT 查询来验证这一点：

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

Note

DROP PARTITION 支持原生分区就地 API，并可以与 ALGORITHM={COPY|INPLACE} 一起使用。DROP PARTITION 与 ALGORITHM=INPLACE 删除分区中的数据并删除分区。然而，DROP PARTITION 与 ALGORITHM=COPY 或 old_alter_table=ON 重建分区表并尝试将数据从删除的分区移到另一个具有兼容的 PARTITION ... VALUES 定义的分区中。无法移到其他分区的数据将被删除。

因此，您必须拥有表的 DROP 权限才能在该表上执行 ALTER TABLE ... DROP PARTITION。

如果您想删除所有分区的所有数据，同时保留表定义和分区方案，请使用 TRUNCATE TABLE 语句。（参见第 15.1.37 节，“TRUNCATE TABLE 语句”。）

如果您想更改表的分区方案而不丢失数据，请使用 ALTER TABLE ... REORGANIZE PARTITION 代替。请参见下文或第 15.1.9 节，“ALTER TABLE 语句”，以获取有关 REORGANIZE PARTITION 的信息。

如果您现在执行 SHOW CREATE TABLE 语句，您可以看到表的分区结构如何变化：

mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

当您将新行插入更改后的表中，purchased 列的值在 '1995-01-01' 和 '2004-12-31' 之间（包括这两个日期），这些行将被存储在分区 p3 中。您可以通过以下方式验证这一点：

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|   11 | pencil holder  | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)

服务器不会报告 ALTER TABLE ... DROP PARTITION 操作删除的行数，就像等效的 DELETE 查询那样。

删除 LIST 分区使用与删除 RANGE 分区相同的 ALTER TABLE ... DROP PARTITION 语法。然而，在删除分区后对表的使用有一个重要的区别：您不能再将任何包含在已删除分区的值列表中的行插入表中。（见第 26.2.2 节，“LIST 分区”，以获取示例。）

要将新范围或列表分区添加到已分区的表中，请使用 ALTER TABLE ... ADD PARTITION 语句。对于按 RANGE 分区的表，可以使用该语句将新范围添加到现有分区的末尾。假设您有一个包含组织成员数据的分区表，定义如下：

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

假设进一步，您的组织的最低年龄是 16 岁。随着 2015 年的结束，您意识到您很快需要准备好接纳出生于 2000 年（及以后）的成员。您可以按照以下方式修改 members 表以容纳出生于 2000 年到 2010 年的新成员：

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

对于按范围分区的表，您可以使用 ADD PARTITION 将新分区添加到分区列表的末尾。但是，尝试在此方式添加新分区到现有分区之前或之间将导致错误，如下所示：

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

您可以通过重新组织第一个分区将其拆分为两个新分区来解决这个问题，如下所示：

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

使用 SHOW CREATE TABLE 可以看到 ALTER TABLE 语句已经产生了预期的效果：

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

另请参阅第 15.1.9.1 节，“ALTER TABLE 分区操作”。

您也可以使用 ALTER TABLE ... ADD PARTITION 将新分区添加到按 LIST 分区的表中。假设表 tt 使用以下 CREATE TABLE 语句定义：

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

您可以添加一个新分区来存储 data 列值为 7、14 和 21 的行，如下所示：

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

请注意，您不能添加一个新的 LIST 分区，包括已经在现有分区的值列表中的任何值。如果您尝试这样做，将导致错误：

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
                    in list partitioning

因为任何 data 列值为 12 的行已经被分配到分区 p1 中，您不能在表 tt 上创建一个新的分区，包括 12 在其值列表中。要实现这一点，您可以删除 p1，然后添加 np，然后添加一个新的 p1，具有修改后的定义。然而，如前所述，这将导致存储在 p1 中的所有数据丢失——这通常不是您想要做的事情。另一个解决方案可能是创建一个具有新分区的表副本，然后使用 CREATE TABLE ... SELECT ... 将数据复制到其中，然后删除旧表并重命名新表，但这在处理大量数据时可能非常耗时。此外，在需要高可用性的情况下，这也可能不可行。

您可以在单个 ALTER TABLE ... ADD PARTITION 语句中添加多个分区，如下所示：

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
  PARTITION p1 VALUES LESS THAN (1991),
  PARTITION p2 VALUES LESS THAN (1996),
  PARTITION p3 VALUES LESS THAN (2001),
  PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

幸运的是，MySQL 的分区实现提供了重新定义分区而不丢失数据的方法。让我们首先看两个简单的示例，涉及 RANGE 分区。回忆一下 members 表，现在定义如下：

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

假设您想将所有出生于 1960 年前的成员行移到一个单独的分区中。正如我们已经看到的那样，这不能使用 ALTER TABLE ... ADD PARTITION。然而，您可以使用另一个与分区相关的 ALTER TABLE 扩展来实现这一点：

ALTER TABLE members REORGANIZE PARTITION n0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

实际上，这个命令将分区 p0 分割成两个新的分区 s0 和 s1。它还将存储在 p0 中的数据移到新的分区中，根据两个 PARTITION ... VALUES ... 子句中的规则，因此 s0 只包含 dob 小于 1960 的记录，而 s1 包含 dob 大于或等于 1960 但小于 1970 的记录。

也可以使用 REORGANIZE PARTITION 子句来合并相邻的分区。可以通过以下语句来撤销前一个语句对 members 表的影响：

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

在使用 REORGANIZE PARTITION 分割或合并分区时，不会丢失任何数据。在执行上述语句时，MySQL 将存储在分区 s0 和 s1 中的所有记录移到分区 p0 中。

以下是 REORGANIZE PARTITION 的通用语法：

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

其中，tbl_name 是分区表的名称，partition_list 是一个逗号分隔的现有分区名称列表，partition_definitions 是一个逗号分隔的新分区定义列表，遵循与 partition_definitions 列表相同的规则，用于 CREATE TABLE。您不仅限于将多个分区合并成一个，或者将一个分区拆分成多个，而是在使用 REORGANIZE PARTITION 时。例如，您可以将 members 表的所有四个分区重新组织成两个，如下所示：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

您也可以使用 REORGANIZE PARTITION 与列表分区表。让我们回到添加新分区到列表分区表 tt 中的问题，并且由于新分区具有已经存在于现有分区值列表中的值而失败。我们可以通过添加一个仅包含非冲突值的分区，然后重新组织新分区和现有分区，以便将现有分区中的值移到新分区中：

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

以下是使用 ALTER TABLE ... REORGANIZE PARTITION 重新分区表时需要注意的一些关键点：

用于确定新分区方案的 PARTITION 选项遵循与 CREATE TABLE 语句相同的规则。

新的 RANGE 分区方案不能有任何重叠的范围；新的 LIST 分区方案不能有任何重叠的值集。
在 partition_definitions 列表中的分区组合应该涵盖与 partition_list 中命名的分区相同的范围或值集。

例如，分区 p1 和 p2 一起涵盖了 members 表中 1980 年至 1999 年的范围。任何重新组织这些两个分区的操作都应该涵盖相同的年份范围。
对于按 RANGE 分区的表，您只能重新组织相邻的分区；您不能跳过范围分区。

例如，您不能使用以 ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... 开头的语句重新组织示例 members 表，因为 p0涵盖了 1970 年之前的年份，而 p2涵盖了 1990 年至 1999 年之间的年份，因此它们不是相邻的分区。（您不能跳过分区 p1 在这种情况下。）
您不能使用 REORGANIZE PARTITION 更改表的分区类型（例如，您不能将 RANGE 分区更改为 HASH 分区或反之）。您也不能使用该语句更改分区表达式或列。要在不删除和重新创建表的情况下完成这两项任务，可以使用 ALTER TABLE ... PARTITION BY ...，如下所示：
```
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
```

PREV HOME UP NEXT