真没想到，MySQL8.0 交换分区竟然有这样的 bug
根据之前的文章 MySQL 非分区表如何改造成分区表？我们知道分区表很适合用来做大表转储。最近生产做转储变更，发现 MySQL 8.0 交换分区竟然有个bug，即分区表上加字段后再交换分区，会报错 Error Code: 1731. Non matching attribute 'INSTANT COLUMN(s)' between partition and table.复现场景如下：
mysql> CREATE TABLE `TEST` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`NAME` varchar(200) DEFAULT NULL,
`CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`CREATE_TIME`,`ID`),
KEY `IDX_NAME` (`NAME`),
KEY `IDX_ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2293142 DEFAULT CHARSET=utf8mb4 COMMENT='测试表'
PARTITION by RANGE COLUMNS(CREATE_TIME)
(PARTITION P202301 VALUES LESS THAN ('2023-02-01') ENGINE = InnoDB,
PARTITION P202302 VALUES LESS THAN ('2023-03-01') ENGINE = InnoDB,
PARTITION P202303 VALUES LESS THAN ('2023-04-01') ENGINE = InnoDB,
PARTITION P202304 VALUES LESS THAN ('2023-05-01') ENGINE = InnoDB,
PARTITION P202305 VALUES LESS THAN ('2023-06-01') ENGINE = InnoDB,
PARTITION P202306 VALUES LESS THAN ('2023-07-01') ENGINE = InnoDB,
PARTITION P202307 VALUES LESS THAN ('2023-08-01') ENGINE = InnoDB,
PARTITION P202308 VALUES LESS THAN ('2023-09-01') ENGINE = InnoDB,
PARTITION P202309 VALUES LESS THAN ('2023-10-01') ENGINE = InnoDB,
PARTITION P202310 VALUES LESS THAN ('2023-11-01') ENGINE = InnoDB,
PARTITION P202311 VALUES LESS THAN ('2023-12-01') ENGINE = InnoDB,
PARTITION P202312 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
PARTITION PMAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
<br>
mysql> alter table TEST add `ADDRESS` varchar(200) DEFAULT NULL COMMENT '地址';
<br>
mysql> CREATE TABLE `TEST_P202301` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`NAME` varchar(200) DEFAULT NULL COMMENT '名字',
`CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`ADDRESS` varchar(200) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`CREATE_TIME`,`ID`),
KEY `IDX_NAME` (`NAME`),
KEY `IDX_ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2293142 DEFAULT CHARSET=utf8mb4 COMMENT='测试表';
<br>
<br>
mysql> alter table TEST EXCHANGE PARTITION P202301 WITH TABLE TEST_P202301 without validation;
Error Code: 1731. Non matching attribute 'INSTANT COLUMN(s)' between partition and table.
