【mysql 表分区增加分区】遇到PMORE 分区或者PMAX分区如何扩展分区

背景

日志分区表，按月分区，在5年前创建分区表的时候，已经创建了5年60个分区，系统经过5年运行，日志表分区马上就满了，且当时未完善分区表自动扩展分区的功能，现需要在当前分区的情况下增加分区。

增加分区

-- 增加分区
alter table t_trans_log add partition(
PARTITION `P2302` VALUES LESS THAN ('2023-03-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2303` VALUES LESS THAN ('2023-04-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2304` VALUES LESS THAN ('2023-05-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2305` VALUES LESS THAN ('2023-06-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2306` VALUES LESS THAN ('2023-07-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2307` VALUES LESS THAN ('2023-08-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2308` VALUES LESS THAN ('2023-09-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2309` VALUES LESS THAN ('2023-10-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2310` VALUES LESS THAN ('2023-11-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2311` VALUES LESS THAN ('2023-12-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2312` VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 
);

若原本分区中有PMORE分区，或者有PMAX分区，通过上述方法，则会报错：

1481 - MAXVALUE can only be used in last partition definition

解决方案：

alter table t_fop_trans_log REORGANIZE PARTITION PMORE INTO (
PARTITION `P2201` VALUES LESS THAN ('2022-02-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2202` VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2203` VALUES LESS THAN ('2022-04-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2204` VALUES LESS THAN ('2022-05-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2205` VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2206` VALUES LESS THAN ('2022-07-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2207` VALUES LESS THAN ('2022-08-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2208` VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2209` VALUES LESS THAN ('2022-10-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2210` VALUES LESS THAN ('2022-11-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2211` VALUES LESS THAN ('2022-12-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2212` VALUES LESS THAN ('2023-01-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION PMORE VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

完美解决。