2026年6月 – 但行好事莫问前程

用表交换分区和子分区
在MySQL 5.7中，可以使用ALTER table pt exchange partition p with table nt来交换一个表分区或子分区，其中pt是已分区的表，p是要与未分区的表nt交换的pt的分区或子分区，前提是以下条件成立:
1.表nt本身没有分区。

2.表nt不是一个临时表。

3.表pt和表nt的结构在其他方面是相同的。

4.表nt不包含外键引用，其他表也没有任何外键引用nt。

5.nt中没有行位于p的分区定义边界之外。如果使用了WITHOUT VALIDATION选项，则不适用此条件。在MySQL 5.7.5中增加了[{WITH|WITHOUT} VALIDATION]选项。

除了ALTER TABLE语句通常需要的ALTER、INSERT和CREATE权限外，还必须有DROP权限才能执行ALTER TABLE … EXCHANGE PARTITION。

您还应该注意ALTER TABLE … EXCHANGE PARTITION有以下影响:
.执行ALTER TABLE…EXCHANGE PARTITION不调用分区表或要交换的表上的任何触发器。

.交换表中的AUTO_INCREMENT列将被重置。

.IGNORE关键字对IGNORE关键字对ALTER TABLE…不起作用。交换分区。不起作用。

ALTER TABLE …EXCHANGE PARTITION语句的语法这里给出了，其中pt是已分区表，p是要交换的分区或子分区，nt是要交换的非分区表：
ALTER TABLE pt
EXCHANGE PARTITION p
WITH TABLE nt;

可选地，您可以添加 WITH VALIDATION 或 WITHOUT VALIDATION 子句。当指定 WITHOUT VALIDATION 时，在将分区与非分区表交换时，ALTER TABLE…EXCHANGE PARTITION 操作不会逐行验证，允许数据库管理员承担确保行在分区定义边界内的责任。WITH VALIDATION 是默认行为，无需显式指定。[{WITH|WITHOUT} VALIDATION] 选项在 MySQL 5.7.5 中添加。

在单个 ALTER TABLE EXCHANGE PARTITION 语句中，只能将一个分区或子分区与一个非分区表进行交换。若要交换多个分区或子分区，请使用多个 ALTER TABLE EXCHANGE PARTITION 语句。EXCHANGE PARTITION 不能与其他 ALTER TABLE 选项结合使用。分区表所使用的分区和（如适用）子分区类型可以是 MySQL 5.7 支持的任何类型。

用非分区表交换分区
假设已经创建了一个分区表e，并使用以下SQL语句填充：

mysql> CREATE TABLE e (
    -> id INT NOT NULL,
    -> fname VARCHAR(30),
    -> lname VARCHAR(30)
    -> )
    -> PARTITION BY RANGE (id) (
    -> PARTITION p0 VALUES LESS THAN (50),
    -> PARTITION p1 VALUES LESS THAN (100),
    -> PARTITION p2 VALUES LESS THAN (150),
    -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO e VALUES
    -> (1669, "Jim", "Smith"),
    -> (337, "Mary", "Jones"),
    -> (16, "Frank", "White"),
    -> (2005, "Linda", "Black");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

现在我们创建了e的一个未分区副本e2。这可以使用mysql客户端来完成，如下所示：

mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

你可以通过查询INFORMATION_SCHEMA.PARTITIONS表来查看表e中哪些分区包含了哪些行,如下所示：

mysql> SELECT PARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.01 sec)

对于分区后的InnoDB表，在INFORMATION_SCHEMA.PARTITIONS的TABLE_ROWS列中给出行数。只是SQL优化中使用的一个估计值，并不总是精确的。

要将表e中的分区p0与表e2交换，可以使用ALTER TABLE语句：

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.01 sec)

更准确地说，这条语句会导致在分区中找到的任何行与在表中找到的行进行交换。你可以通过查询INFORMATION_SCHEMA.PARTITIONS来观察这是如何发生的。和之前一样，发现找表分区p0不再存在行数据：

mysql> SELECT PARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

如果你查询表e2，你可以看到“缺失”行现在可以在那里找到：

mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

与分区交换的表不一定是空的。为了证明这一点，我们首先向表e中插入一个新行，通过选择一个id值小于50的列来确保该行存储在分区p0中，然后通过查询分区表来验证这一点：

mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.01 sec)

现在我们再次使用与之前相同的ALTER table语句将分区p0与表e2交换：

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.01 sec)

下面查询的输出显示，在发出ALTER TABLE语句之前，存储在分区p0中的表行和存储在表e2中的表行现在交换了位置：

mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.01 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

不匹配的行
您应该记住，在发出ALTER TABLE … EXCHANGE PARTITION语句之前，在未分区的表中找到的任何行必须满足将它们存储在目标分区中的条件；否则，语句执行失败。要了解这是如何发生的，首先在e2中插入一条超出表e的p0分区定义边界的行。例如，插入一条id列值过大的行；然后，尝试再次用分区交换表：

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1737 (HY000): Found a row that does not match the partition

只有WITHOUT VALIDATION选项允许此操作成功：

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

当一个分区与包含与分区定义不匹配的行的表交换时，数据库管理员的责任是修复不匹配的行，这可以使用REPAIR TABLE或AALTER TABLE … REPAIR PARTITION.

mysql> select * from e partition(p0);
+----+---------+----------+
| id | fname   | lname    |
+----+---------+----------+
| 41 | Michael | Green    |
| 51 | Ellen   | McDonald |
+----+---------+----------+
2 rows in set (0.00 sec)

mysql> repair table e;
+--------+--------+----------+------------------------+
| Table  | Op     | Msg_type | Msg_text               |
+--------+--------+----------+------------------------+
| test.e | repair | warning  | Moved 1 misplaced rows |
| test.e | repair | status   | OK                     |
+--------+--------+----------+------------------------+
2 rows in set (0.01 sec)

mysql> select * from e partition(p0);
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

不逐行验证的交换分区
为了避免在与有许多行的表交换分区时进行耗时的验证，可以通过向ALTER TABLE … EXCHANGE PARTITION语句追加WITHOUT VALIDATION来跳过逐行验证步骤。

下面的例子比较了在使用和不使用验证的情况下，用非分区表交换分区时执行时间的差异。分区表（表e）包含两个分区，每个分区有100万行。删除表e中p0中的行，将p0与一个100万行的未分区表交换。使验证操作耗时0.74秒。相比之下，无验证操作需要0.01秒。

# Create a partitioned table with 1 million rows in each partition
CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (1000001),
PARTITION p1 VALUES LESS THAN (2000001),
);
SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
| 2000000  |
+----------+
1 row in set (0.27 sec)
# View the rows in each partition
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME |  TABLE_ROWS |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)
# Create a nonpartitioned table of the same structure and populate it with 1 million rows
CREATE TABLE e2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
);
mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)
# Create another nonpartitioned table of the same structure and populate it with 1 million rows
CREATE TABLE e3 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
);
mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)
# Drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)
# Confirm that there are no rows in partition p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)
# Confirm that the partition was exchanged with table e2
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Once again, drop the rows from p0 of table e
mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)
# Confirm that there are no rows in partition p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)
# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)
# Confirm that the partition was exchanged with table e3
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

如果一个分区与包含与分区定义不匹配的行的表交换，则数据库管理员有责任修复不匹配的行，这可以使用REPAIR TABLE或ALTER TABLE … REPAIR PARTITION修复分区。

管理哈希和键分区
按哈希或按键分区的表在更改分区设置方面非常相似，两者与按范围或列表分区的表在许多方面有所不同。因此，本节讨论的是对按哈希或按键分区表的修改。

您不能像从按范围或列表分区的表中删除分区那样，从哈希或按键分区的表中删除分区。但是，你可以使用ALTER TABLE … COALESCE PARTITION语句来合并哈希分区或键分区。假设你有一个包含客户端数据的表，分为12个分区。clients表定义如下:

mysql> CREATE TABLE clients (
    -> id INT,
    -> fname VARCHAR(30),
    -> lname VARCHAR(30),
    -> signed DATE
    -> )
    -> PARTITION BY HASH( MONTH(signed) )
    -> PARTITIONS 12;
Query OK, 0 rows affected (0.07 sec)

要将分区数量从12个减少到8个，请执行以下ALTER TABLE命令：

mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

COALESCE同样适用于按HASH、KEY、LINEAR HASH或LINEAR KEY分区的表。下面的例子与前一个类似，不同之处在于这张表是按线性键分区的：

mysql> CREATE TABLE clients_lk (
    -> id INT,
    -> fname VARCHAR(30),
    -> lname VARCHAR(30),
    -> signed DATE
    -> )
    -> PARTITION BY LINEAR KEY(signed)
    -> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

COALESCE PARTITION后面的数字是要合并到剩余分区中的分区数量——换句话说，它是要从表中删除的分区数量。

如果您试图删除比表所拥有的更多的分区，则结果是如下所示的错误：

mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead

将客户端表的分区数量从12增加到18。使用ALTER TABLE … ADD PARTITION来添加分区如下所示：

mysql> ALTER TABLE clients ADD PARTITION PARTITIONS 6;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

月份：2026年6月

MySQL 5.7 用非分区表交换分区

MySQL 5.7 管理哈希和键分区