MySQL EXCHANGE PARTITION 怎样把历史数据移到归档表

如果历史数据已经集中在某个 RANGE 分区里，想把它交给独立归档表，MySQL 不一定要重建整张表。ALTER TABLE ... EXCHANGE PARTITION 可以让一个分区与一张非分区表交换行集合，适合把已经封存的历史分区快速移出在线分区表。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/partitioning-management-exchange.html

最稳妥的做法是：先用原分区表复制出归档表，再移除归档表的分区定义，确认表结构和分区边界都匹配，最后执行默认的 WITH VALIDATION 交换。交换完成后，目标分区的行进入归档表，归档表原有的行则进入目标分区。

EXCHANGE PARTITION 到底交换了什么

这个语句的关键不是“复制历史数据”，而是交换两个位置上的行集合。分区表 orders 的 p_old 分区和非分区表 orders_archive 是两个容器；执行交换后，原来属于 p_old 的行出现在 orders_archive，而归档表原来持有的行进入 p_old。

因此，归档表不是“备份副本”，交换后它接管了历史行，在线表的目标分区也不再保留这些行。如果归档表中原本有数据，必须把它也当作会被换回在线分区的数据来设计，不能把非空归档表误认为单向搬运目标。

先创建结构匹配的归档表

归档表必须是非分区表，不能是临时表；它与分区表的结构需要保持一致。实际操作中，用 CREATE TABLE ... LIKE 复制结构，再移除分区定义，比手写列和索引更不容易漏项。

-- 复制在线表的列、索引和表属性，避免手写结构时遗漏
CREATE TABLE orders_archive LIKE orders;

-- 归档表必须是非分区表，保留列和索引但移除分区定义
ALTER TABLE orders_archive REMOVE PARTITIONING;

-- 先看两张表的结构差异，再决定是否继续交换
SHOW CREATE TABLE orders;
SHOW CREATE TABLE orders_archive;

这里的“结构匹配”不只是列名和数据类型相同。MySQL 8.4 的交换条件还涉及字符集、排序规则、InnoDB 行格式、部分表属性以及索引相关定义。尤其是归档表不能有外键引用，也不能被其他表的外键引用；否则即使命令看起来只是在移动历史数据，也会被条件检查拦截。

交换前检查分区边界和表属性

假设 orders 按 order_id 做 RANGE 分区，p_old 的定义是 VALUES LESS THAN (100000)。归档表里的每一行都必须落在这个边界内。默认的 WITH VALIDATION 会执行逐行边界检查，更适合第一次迁移或来源不完全可控的归档流程。

-- 查看目标分区边界和表级元数据，先确定要交换的分区
SELECT
    PARTITION_NAME,
    PARTITION_DESCRIPTION,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
ORDER BY PARTITION_ORDINAL_POSITION;

-- 检查归档表是否混入目标分区边界之外的行
-- 下面的上界必须与 p_old 的分区定义一致
SELECT COUNT(*) AS out_of_range_rows
FROM orders_archive
WHERE order_id >= 100000;

-- 查看 InnoDB 行格式；两张表应采用相同设置
SELECT NAME, ROW_FORMAT
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME IN (CONCAT(DATABASE(), '/orders'),
               CONCAT(DATABASE(), '/orders_archive'));

还要同时确认两张表的字符集和排序规则相同，不能有不一致的 TABLESPACE 选项；如果分区级和表级设置了 MAX_ROWS、MIN_ROWS 或 AVG_ROW_LENGTH，也要按官方条件对齐。实际排障时，不要只盯着“列看起来一样”，因为这些表属性同样会决定交换能否通过。

用 WITH VALIDATION 执行历史分区交换

当归档表结构匹配、行边界也符合预期后，执行一次只包含交换操作的 ALTER TABLE。一个语句只能交换一个分区或子分区与一张非分区表，不能把它和其他 ALTER TABLE 选项拼在一起。

-- 默认使用 WITH VALIDATION，先确认归档表中的行满足 p_old 边界
ALTER TABLE orders
    EXCHANGE PARTITION p_old
    WITH TABLE orders_archive
    WITH VALIDATION;

-- 交换后分别查看在线表与归档表中的行数
SELECT COUNT(*) AS archive_rows FROM orders_archive;
SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
  AND PARTITION_NAME = 'p_old';

成功后，目标分区的历史行就由 orders_archive 持有，在线表的 p_old 接收归档表原有的行。若归档表开始时为空，最容易理解为“历史分区被移出”；如果归档表不是空表，则结果是两边的行集合互换，不能只按照单向搬迁来估算。

什么时候可以使用 WITHOUT VALIDATION

WITHOUT VALIDATION 会跳过交换时的逐行边界检查，把“归档表内的所有行确实属于目标分区”这个责任交给操作者。它可以减少大表交换时的检查成本，但不是让不匹配的数据变得合法，也不会替你修正越界行。

-- 只有在归档表边界已由外部流程可靠保证时，才考虑跳过逐行校验
ALTER TABLE orders
    EXCHANGE PARTITION p_old
    WITH TABLE orders_archive
    WITHOUT VALIDATION;

一个实用的安全策略是：首次迁移使用默认的 WITH VALIDATION；后续批次如果由同一套导入流程严格维护边界，再评估是否需要 WITHOUT VALIDATION。无论选择哪种方式，都应在变更前保留表结构和行数记录，并把交换对象、分区名、归档表名和操作时间写入变更记录。

常见失败原因与处理顺序

另外，EXCHANGE PARTITION 不会触发在线表或归档表上的触发器；交换表中的 AUTO_INCREMENT 列也有重置语义。需要依赖触发器同步审计数据的场景，应在交换前后另行设计记录流程，不要假设普通 INSERT 或 DELETE 会被隐式触发。

一套适合归档任务的操作清单

1. 确认历史范围对应的分区名和分区边界。
2. 用 CREATE TABLE ... LIKE 创建归档表，并执行 REMOVE PARTITIONING。
3. 核对列、索引、字符集、排序规则、InnoDB 行格式和表属性。
4. 核对外键关系、权限以及归档表当前是否为空。
5. 先用查询确认归档表没有越过目标分区边界的行。
6. 默认执行 WITH VALIDATION，交换后再查看分区元数据和归档表行数。
7. 只有在边界由可靠流程保证时，才评估 WITHOUT VALIDATION。

相关问题

EXCHANGE PARTITION 是复制还是移动？

它交换的是分区与非分区表之间的行集合归属，不是生成一份独立副本。归档表为空时，效果看起来像把历史分区移到归档表；归档表非空时，两边数据会互换。

归档表可以继续保留分区吗？

不能。用于交换的目标表必须是非分区表，临时表也不符合条件。通常做法是从分区表复制结构后移除分区定义。

为什么不建议一开始就使用 WITHOUT VALIDATION？

因为它跳过逐行边界检查，越界数据会把后续查询和维护带入不清晰的状态。除非数据管道已经可靠保证边界，否则应保留默认的 WITH VALIDATION。

交换后触发器会自动写归档日志吗？

不会。该操作不调用分区表或被交换表上的触发器；如果需要审计，应在变更流程中显式记录交换前后的对象、分区和行数。