How to change the partition child name in MySQL?-CodePudding

I mistakenly add the p20221121 name to the last partition. I want to change it to pmax.

Is there any way to do that in MySQL?

#Creation query
ALTER TABLE `myTable` PARTITION BY RANGE (TO_DAYS(`partDate`))( 
    PARTITION p20221117 VALUES LESS THAN  (TO_DAYS('2022-11-17')), 
    PARTITION p20221118 VALUES LESS THAN  (TO_DAYS('2022-11-18')), 
    PARTITION p20221119 VALUES LESS THAN  (TO_DAYS('2022-11-19')), 
    PARTITION p20221120 VALUES LESS THAN  (TO_DAYS('2022-11-20')), 
    PARTITION p20221121 VALUES LESS THAN MAXVALUE       
);

CodePudding user response：

There are examples of REORGANIZE PARTITION on this page: https://dev.mysql.com/doc/refman/8.0/en/partitioning-management-range-list.html

Here's a quick demo:

mysql> create table mytable (partDate date);
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE `myTable` PARTITION BY RANGE (TO_DAYS(`partDate`))( 
    ->     PARTITION p20221117 VALUES LESS THAN  (TO_DAYS('2022-11-17')), 
    ->     PARTITION p20221118 VALUES LESS THAN  (TO_DAYS('2022-11-18')), 
    ->     PARTITION p20221119 VALUES LESS THAN  (TO_DAYS('2022-11-19')), 
    ->     PARTITION p20221120 VALUES LESS THAN  (TO_DAYS('2022-11-20')), 
    ->     PARTITION p20221121 VALUES LESS THAN MAXVALUE       
    -> );
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE myTable REORGANIZE PARTITION p20221121 
  INTO ( PARTITION pMax VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table myTable\G
*************************** 1. row ***************************
       Table: myTable
Create Table: CREATE TABLE `myTable` (
  `partDate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`partDate`))
(PARTITION p20221117 VALUES LESS THAN (738841) ENGINE = InnoDB,
 PARTITION p20221118 VALUES LESS THAN (738842) ENGINE = InnoDB,
 PARTITION p20221119 VALUES LESS THAN (738843) ENGINE = InnoDB,
 PARTITION p20221120 VALUES LESS THAN (738844) ENGINE = InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

This does need to copy any data currently in the partition being reorganized, but it does not need to touch any other partitions.