背景概述

分区表的好处就不多说了，我这里需要的场景是要对数据库中的某一个表做备份，以现在的时间为起点备份多少天以前的数据，并且删除该表中已经备份的数据。由于该表中的数据一般都是按天查询，并且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 '0',
  `SERVICE_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_SVC_DESC` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_SVC_STATUS` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_SVC_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQ_NO` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ORG_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `EMP_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `SYS_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `USER_TOKEN` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `TRAN_TIME` datetime(6) NULL DEFAULT NULL,
  `SB_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQ_PUT_TIME` datetime(6) NULL DEFAULT NULL,
  `RESP_TIME` datetime(6) NULL DEFAULT NULL,
  `REQUEST_PUT_MSG` bigint(20) NULL DEFAULT NULL,
  `RESPONSE_REPICK_MSG` bigint(20) NULL DEFAULT NULL,
  `INTERVAL_TIME` bigint(20) NULL DEFAULT NULL,
  `READ_FLAG` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQUEST_MSG_KEY` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ESB_NODE` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQUEST_MSG_DETAIL` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `RESPONSE_MSG_DETAIL` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `ERROR_STACK_MSG_DETAIL` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `START_TIME3` datetime(6) NULL DEFAULT NULL,
  `START_TIME1` datetime(6) NULL DEFAULT NULL,
  `END_TIME3` datetime(6) NULL DEFAULT NULL,
  `END_TIME1` datetime(6) NULL DEFAULT NULL,
  `INTERVAL_TIME3` bigint(20) NULL DEFAULT NULL,
  `INTERVAL_TIME1` bigint(20) NULL DEFAULT NULL,
  `CREATED_BY` bigint(20) NULL DEFAULT -1,
  `CREATION_DATE` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `LAST_UPDATED_BY` bigint(20) NULL DEFAULT -1,
  `LAST_UPDATE_DATE` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  `OBJECT_VERSION_NUMBER` bigint(20) NULL DEFAULT 1,
  `BUS_REQUEST_END_TIME` datetime(6) NULL DEFAULT NULL,
  `BUS_RESPONSE_START_TIME` datetime(6) NULL DEFAULT NULL,
  `BUSINESS_CODE_ONE` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_TWO` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_THREE` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_FOUR` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BUSINESS_CODE_FIVE` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `REQUEST_MSG_SIZE` bigint(20) NULL DEFAULT NULL,
  `RESPONSE_MSG_SIZE` bigint(20) NULL DEFAULT NULL,
  PRIMARY KEY (`INST_ID`,`CREATION_DATE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N1`(`START_TIME`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N2`(`END_TIME`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N3`(`SB_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N4`(`SERVICE_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N5`(`SERVICE_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N6`(`SERVICE_URI`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N7`(`READ_FLAG`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N8`(`SB_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N9`(`READ_FLAG`, `SERVICE_URI`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N10`(`SERVICE_URI`, `SB_CODE`) USING BTREE,
  INDEX `HMW_SERVICE_INSTANCES_N11`(`READ_FLAG`, `SB_CODE`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12325 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic PARTITION BY RANGE COLUMNS(CREATION_DATE)
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB);

创建存储过程

DELIMITER $$
#该表所在数据库名称
USE `hmw`$$
DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    #当前日期存在的分区的个数
    DECLARE ROWS_CNT INT UNSIGNED;
    #目前日期，为当前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分区的名称，格式为p20180620
    DECLARE PARTITIONNAME VARCHAR(9);
    #当前分区名称的分区值上限，即为 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_DAY datetime(0);
    SET TARGET_DATE = NOW() + INTERVAL 1 DAY;
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;
    SET PARTITION_ADD_DAY = DATE(NOW() + INTERVAL 2 DAY);
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (
            '",PARTITION_ADD_DAY,"') ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
       SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
END$$
DELIMITER ;

该存储过程是根据数据库和表名来按天创建分区表，分区的明明是pyyyymmdd的格式。

创建event事件，按天执行存储过程

DELIMITER $$
#该表所在的数据库名称
USE `hmw`$$
CREATE EVENT IF NOT EXISTS `daily_generate_partition`
ON SCHEDULE EVERY 1 DAY   #执行周期，还有天、月等等
STARTS '2019-12-03 16:59:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
    CALL create_partition_by_day('hmw','hmw_service_instance');
		CALL create_partition_by_day('hmw','hmw_service_instance_backup');
END$$
DELIMITER ;

常用命令

查询表分区：
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS t where t.TABLE_NAME=‘hmw_service_instance’

查询事件 ：

SHOW EVENTS

删除事件：

DROP EVENT IF EXISTS daily_generate_partition;

删除表分区：

alter table hmw_service_instance_backup drop partition p20191205;

添加表分区：

alter table emp add partition (partition p3 values less than (4000));

事件不执行

原因出在没有开启event_scheduler！！！
1、查看event_scheduler状态

SHOW VARIABLES LIKE 'event_scheduler'

2、使用命令开启（临时开启，重启mysql又还原回去了）

set global event_scheduler = on;

3、修改配置（永久修改）

配置文件的[mysqld]部分加上event_scheduler=ON即可，一般是my.cnf文件。

mysql 创建分区 按照某个日期字段 日号 mysql分区表按日期分区_表分区