这个真没想到MySQL DDL也有隐患

没想到分区加了字段，居然影响后续操作。模拟一下当时出问题的场景。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。