MySQL 分区表按月创建分区可以提高查询效率、方便数据维护、增强可靠性以及优化性能。因此，在处理大量数据时，使用分区表是一个非常好的选择。

下面通过一个例子来说明如何创建分区表并且按月分区。

1.创建表

创建表，并使用 RANGE COLUMNS 分区。按创建时间 create_time 字段分区，分区名使用 p0、p1、p2、p3 ... 的形式，create_time 字段小于 2019-01-01 的数据将进入 p0 分区，依次类推。

CREATE TABLE "box_fenqu" (
  "id" bigint(36) NOT NULL AUTO_INCREMENT COMMENT '主键',
  "create_by" varchar(50) DEFAULT NULL COMMENT '创建人',
  "create_time" datetime NOT NULL COMMENT '创建日期',
  "update_by" varchar(50) DEFAULT NULL COMMENT '更新人',
  "update_time" datetime DEFAULT NULL COMMENT '更新日期',
  "sys_org_code" varchar(64) DEFAULT NULL COMMENT '所属部门',
  "status" int(10) DEFAULT '0' COMMENT '状态',
  "number" varchar(32) DEFAULT NULL COMMENT '编号',
  "zi_number" varchar(32) DEFAULT NULL COMMENT '自编号',
  "house_address" varchar(32) DEFAULT NULL COMMENT '仓库地址',
  "sb_number" varchar(32) DEFAULT NULL COMMENT '设备id',
  "point_id" varchar(32) DEFAULT NULL COMMENT '投放点id',
  "point" varchar(32) DEFAULT NULL COMMENT '投放点',
  "confirm" int(32) DEFAULT '0' COMMENT '商户/企业用户确认入库，默认为0（未确认）1是已确认',
  "last_point" varchar(32) DEFAULT NULL COMMENT '最近一次投放点名',
  PRIMARY KEY ("id","create_time") USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2120001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2019-02-01') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2019-03-01') ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN ('2019-04-01') ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN ('2019-05-01') ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN ('2019-06-01') ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN ('2019-07-01') ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN ('2019-08-01') ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN ('2019-09-01') ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN ('2019-10-01') ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN ('2019-11-01') ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

2.创建存储过程

创建存储过程 proc_create_partition，用它来直接对 box_fenqu 表进行分区。其实就是将分区名加 1，如 p2 加 1 就是 p3；指定的日期范围就是往后面进一个月，保证最新的分区总是 MAXVALUES 的，如 20200401 就变成 20200401 和 20200501，其中 20200401 指定范围，20200501 为 MAXVALUES。

1.创建用于对单张表分区的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition`(in_tbname VARCHAR (64))
BEGIN
    SELECT DATABASE() INTO @dbname;
    SET @tbname=in_tbname;
    #查询表的最近一次分区
    SELECT REPLACE(partition_name, 'p', '') INTO @PMAX FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_SCHEMA = @dbname AND table_name=@tbname ORDER BY partition_ordinal_position DESC LIMIT 1;
    #查询表的最近一次分区
    SELECT REPLACE(partition_description, "'", '') INTO @DNAME FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_SCHEMA = @dbname AND table_name=@tbname ORDER BY partition_ordinal_position DESC LIMIT 1,1;
    SET @t=CONCAT(
        'alter table `',
        @dbname,
        '`.',
        @tbname,
        ' reorganize partition p',
        @PMAX,
        ' into(partition p',
        @PMAX,
        ' values less than (''',
        date(DATE_ADD( @DNAME, INTERVAL 1 MONTH )),
        '''),',
        'partition p',
        @PMAX + 1,
        ' values less than MAXVALUE)'
    );
    SELECT @t;
    PREPARE stmt FROM @t;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    COMMIT;
END

这样传入 box_fenqu 表名即可进行增加分区了： CALL proc_create_partition('box_fenqu');

2.创建存储过程调用单表分区存储过程对所有表进行分区

如果有多个表都需要定时的动态增加分区的话，就可以再写个存储过程，这个存储过程将对当前库下所有分区表都进行遍历，然后增加分区。

CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition_all`()
BEGIN
   DECLARE tbname varchar(64);
   DECLARE tmpSql varchar(256);
   DECLARE done INT DEFAULT FALSE ;
    #查询已手动分区的表
    DECLARE part_cursor CURSOR FOR (SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND partition_expression IS NOT NULL AND table_name NOT LIKE '%bak');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    #循环对表添加分区
    OPEN part_cursor;
        myLoop: LOOP
            FETCH part_cursor INTO tbname;
            IF done THEN
                LEAVE myLoop;
            END IF;
            #调用分区存储过程
            CALL proc_create_partition(tbname);
            COMMIT;
        END LOOP myLoop;
    CLOSE part_cursor;
END

3.创建事件

创建事件从 2019 年 8 月起每月 1 号调用存储过程给表添加分区。

CREATE DEFINER=`root`@`%` EVENT `e_create_partition`
ON SCHEDULE EVERY 1 MONTH STARTS '2019-08-01 00:00:00'
ON COMPLETION PRESERVE DISABLE
DO CALL proc_create_partition_all()

4.开启事件

alter event `e_create_partition` enable;

参考文档：
https://www.codenong.com/cs106588491/
https://www.jianshu.com/p/e91cac8cd1b1