Mysql删除分区，增加分区，分区数据清理

最近线上的分区表占用空间较大，需要进行分区数据的删除，记录如下，顺便把分区维护的其他命令也一并记录，方便后续进行查询使用。

之前建表的语句大概如下，省掉了其他一些字段

CREATE TABLE `bm_scenes_data_reminder` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `trace_id` varchar(50) DEFAULT NULL COMMENT '数据traceId',
  `bc_url` varchar(100) DEFAULT NULL COMMENT '数据bc.url',
  `data_source` varchar(50) DEFAULT NULL COMMENT '数据来源（topic）',
  `bz_resultCode` varchar(10) DEFAULT NULL COMMENT '错误码',
  `start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
  PRIMARY KEY (`id`,`start_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19771343 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (to_days(start_time))
(PARTITION p20210103 VALUES LESS THAN (738158) ENGINE = InnoDB,
 PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB,
 PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB,
 PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB,
 PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB,
 PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB,
 PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB,
 PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

查看每个分区的记录行数和占用大小

SELECT PARTITION_NAME,TABLE_ROWS,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bm_scenes_data_reminder';

+----------------+------------+-------------+
| PARTITION_NAME | TABLE_ROWS | DATA_LENGTH |
+----------------+------------+-------------+
| p20210103      |          0 |       16384 |
| p20210104      |          0 |       16384 |
| p20210105      |          0 |       16384 |
| p20210106      |          0 |       16384 |
| p20210107      |          0 |       16384 |
| p20210108      |          0 |       16384 |
| p20210109      |          0 |       16384 |
| p20210110      |          0 |       16384 |
| future         |          0 |       16384 |
+----------------+------------+-------------+
9 rows in set (0.00 sec)

清理分区数据为空，保留分区不删除，仅仅是清理数据，命令如下

alter table bm_scenes_data_reminder truncate partition p20210104;

删除分区

alter table bm_scenes_data_reminder drop partition p20210104;

删除后执行查看建表语句，可以看到p20210104分区没有了

在这里插入图片描述

增加分区

##如果希望将刚删除的p20210104分区重新加回去？怎么办。先尝试直接执行增加分区命令试试

ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);

结果如下，说明是不可行的。

mysql> ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>

##1步骤中不可行，==提示必须是在最后一个分区的后面才可以这样增加。== 因此如果一定要加回==p20210104==这个分区（即需要在中间部分增加分区）,只能将p20210104 后面的分区先全删除，再增加p20210104分区，再后p20210104 后面的分区重新加回去。操作如下：

##先删除p20210104分区后面的所有分区
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210105;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210106;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210107;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210108;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210109;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210110;
ALTER TABLE bm_scenes_data_reminder drop PARTITION future;

##增加p20210104分区
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);

##把p20210104分区后面的所有分区重新加回去
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

最后再查看一下ddl发现分区加回去了，但这种操作方式会把p20210104分区后面的所有分区数据删除，在正式线上环境中请慎重

在这里插入图片描述