背景概述
分区表的好处就不多说了,我这里需要的场景是要对数据库中的某一个表做备份,以现在的时间为起点备份多少天以前的数据,并且删除该表中已经备份的数据。由于该表中的数据一般都是按天查询,并且delete加了where条件之后并不是在数据库中直接删除数据,而是将数据文件的标识位删除,没有整理文件,因此不会彻底释放空间,时间久了就会存在大量的垃圾数据,所以就想到了用分区表。而按天分区就可以很好的解决以上的问题。
实现方案
创建分区表
创建分区表的时候跟普通创建表基本上一样,只需要在末尾加上如下语句:
PARTITION BY RANGE COLUMNS(CREATION_DATE)
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB)
该句说的是按范围分区,而CREATION_DATE就是分区的条件,这个字段必须是你创建的表中有的字段,并且必须是主键之一。后面括号中的内容是创建一个分区,命名为p0,并把所有CREATION_DATE值小于’2019-01-01’的数据插入到该分区,这里可以直接创建多个分区,写法如下:
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN ('2019-01-02') ENGINE = InnoDB)
完整的创建表代码:
CREATE TABLE `hmw_service_instance_backup` (
`INST_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`SERVICE_URI` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`STATUS` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`REQUEST_MSG` bigint(20) NULL DEFAULT NULL,
`RESPONSE_MSG` bigint(20) NULL DEFAULT NULL,
`START_TIME` datetime(6) NULL DEFAULT NULL,
`END_TIME` datetime(6) NULL DEFAULT NULL,
`RESPONSE_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`ERROR_MSG` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`ERROR_STACK_MSG` bigint(20) NULL DEFAULT NULL,
`CONVERSATION_ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`CONSUMER` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`PARENT_CON_ID` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT