mysql创建分区表，按月分区

https://blog.csdn.net/weixin_43632687/article/details/107952481

# 创建日志表

CREATE TABLE `sys_log` (
  `log_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(10) DEFAULT NULL COMMENT '用户ID',
  `class_function` varchar(255) DEFAULT NULL COMMENT '类和方法名',
  `function_id` int(10) DEFAULT NULL COMMENT '功能id',
  `description` varchar(255) DEFAULT NULL COMMENT '功能名称',
  `sub_desc` varchar(255) DEFAULT NULL COMMENT '子功能',
  `ip` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `type` int(10) DEFAULT NULL COMMENT '类型【1：异常 2：使用方法】',
  PRIMARY KEY (`log_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 创建联合主键
删除原有主键，创建联合主键，这一步你可以在建表的时候就创建好。

ALTER TABLE `sys_log` DROP PRIMARY KEY,ADD PRIMARY KEY (log_id, create_time);

# 手动创建分区
手动创建分区，这一步你也可以在建表的时候就创建好。

ALTER TABLE `sys_log` PARTITION by RANGE COLUMNS (create_time) (PARTITION `p1` VALUES LESS THAN ('2019-01-01'),PARTITION `p2` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

# 创建存储过程
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

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

CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition_all`()BEGIN

  DECLARE tbname varchar(32);
  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()