没想到分区加了字段，居然影响后续操作。模拟一下当时出问题的场景。PA表为分区表，A表为和它一样表结构的非分区表。如图1
图1
模拟写入几条数据
mysql> insert into pa (a,create_time) values (1,'2020-10-01');
Query OK, 1 row affected (0.01 sec)
mysql> insert into pa (a,create_time) values (2,'2022-10-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into pa (a,create_time) values (3,'2023-10-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into pa (a,create_time) values (4,'2024-10-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into pa (a,create_time) values (5,'2025-10-01');
Query OK, 1 row affected (0.00 sec)
查询PA表和A表。如图2
图2
PA表有5条数据，A表无数据。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
执行交互分区（为什么要这样，是因为这样好对A表做逻辑备份。）
mysql> ALTER TABLE pa EXCHANGE PARTITION p1 WITH TABLE a;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from pa;
+----+------+---------------------+
| id | a | create_time |
+----+------+---------------------+
| 2 | 2 | 2022-10-01 00:00:00 |
| 3 | 3 | 2023-10-01 00:00:00 |
| 4 | 4 | 2024-10-01 00:00:00 |
| 5 | 5 | 2025-10-01 00:00:00 |
+----+------+---------------------+
4 rows in set (0.01 sec)
mysql> select * from a;
+----+------+---------------------+
| id | a | create_time |
+----+------+---------------------+
| 1 | 1 | 2020-10-01 00:00:00 |
+----+------+---------------------+
1 row in set (0.01 sec)
可以看到这个执行的得到了我们的预期，PA表的第一个分区被移走了。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
这种操作只能一次做一个分区到非分区。如果要做第二个，那么要再次建立一个相同结构的非分区表。
CREATE TABLE `b` (
`id` int NOT NULL AUTO_INCREMENT,
a int,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`create_time`));
mysql> ALTER TABLE pa EXCHANGE PARTITION p2 WITH TABLE b;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from pa;
+----+------+---------------------+
| id | a | create_time |
+----+------+---------------------+
| 3 | 3 | 2023-10-01 00:00:00 |
| 4 | 4 | 2024-10-01 00:00:00 |
| 5 | 5 | 2025-10-01 00:00:00 |
+----+------+---------------------+
3 rows in set (0.01 sec)
mysql> select * from b;
+----+------+---------------------+
| id | a | create_time |
+----+------+---------------------+
| 2 | 2 | 2022-10-01 00:00:00 |
+----+------+---------------------+
1 row in set (0.00 sec)
实验结果也符合我们的预期。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
但是问题来了。在分区表上加了一个字段（这也就是我遇到的问题，这个真没想到）。然后不能再使用交互分区了。
出了 1731 的错误码。这个网上几乎没有说明。
mysql> alter table pa add c int;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE `c` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> a int,
-> `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> c int,
-> PRIMARY KEY (`id`,`create_time`));
Query OK, 0 rows affected (0.03 sec)
mysql> select * from pa;
+----+------+---------------------+------+
| id | a | create_time | c |
+----+------+---------------------+------+
| 3 | 3 | 2023-10-01 00:00:00 | NULL |
| 4 | 4 | 2024-10-01 00:00:00 | NULL |
| 5 | 5 | 2025-10-01 00:00:00 | NULL |
+----+------+---------------------+------+
3 rows in set (0.00 sec)
mysql> select * from c;
Empty set (0.00 sec)
mysql> ALTER TABLE pa EXCHANGE PARTITION p3 WITH TABLE c;
ERROR 1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and table
虽然通过以下的命令看似能解决。
mysql> alter table pa ALGORITHM=copy;
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE pa EXCHANGE PARTITION p3 WITH TABLE c;
Query OK, 0 rows affected (0.04 sec)
但是马上意识到这个操作本身就是强制拷贝表。数据量小看似没有问题。但是实际问题太大了。模拟创建一个稍微有点数据量的表，如图3，还是执行这个ALGORITHM=copy
图3
从这个数据而言仅仅33M的表，如图4，就要10秒。所以在大表上不能这样操作。
图4
最好的办法是建立一模一样的分区表进行移植，并且确保以后这样的表禁止DDL。