2021-04-02

-- ----------------------------
-- Procedure structure for procedure_arrangeByMonth
-- ----------------------------
DROP PROCEDURE IF EXISTS `procedure_arrangeByMonth`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_arrangeByMonth`(IN tableName VARCHAR(20))
    COMMENT '按月归档表分区的存储过程，由定时任务调用'
BEGIN
		-- 定义上个月的第一天
		DECLARE startDate DATE;
		-- 定义上个月的第一天
		DECLARE endDate DATE;
		-- 定义上个月所有分区表的分区名称
		DECLARE StrP VARCHAR(200);
		-- 定义上个月的月份作为分区名称
		DECLARE lastMonth VARCHAR(20);
		-- 定义上个月分区中最大的分区日期
		DECLARE lastMaxDate LONG;
		
		
		-- EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
		-- DATE_SUB() 函数从日期减去指定的时间间隔。
		-- INTERVAL 1 MONTH 表示上个月，INTERVAL 0 MONTH 表示当前月
		-- DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

		-- 获取上个月的第一天，赋值给startDate
		SELECT DATE_SUB(DATE_SUB(DATE_FORMAT(now(),'%y-%m-%d'),INTERVAL EXTRACT(DAY FROM now())-1 DAY),INTERVAL 1 MONTH) INTO startDate FROM DUAL;
		-- 获取上个月的最后一天，赋值给startDate
		SELECT DATE_SUB(DATE_SUB(DATE_FORMAT(now(),'%y-%m-%d'),INTERVAL EXTRACT(DAY FROM now()) DAY),INTERVAL 0 MONTH) INTO endDate FROM DUAL;
		-- 获取上个月所有分区表的分区名称，以逗号分割进行列出，赋给StrP
		SELECT GROUP_CONCAT(partition_name) INTO StrP FROM INFORMATION_SCHEMA.PARTITIONS WHERE  table_name = tableName AND partition_description BETWEEN TO_DAYS(startDate) AND TO_DAYS(endDate);
		-- 获取上个月的月份的月份,赋值给lastMonth，输出格式如：201709
		SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 MONTH), '%Y%m') INTO lastMonth  FROM DUAL;
		-- 获取上个月分区中最大的分区日期，赋值给lastMaxDate
		SELECT MAX(partition_description) des INTO lastMaxDate FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name = tableName AND partition_description BETWEEN TO_DAYS(startDate) AND TO_DAYS(endDate);	
		-- 拼接SQL语句，将上个月的所有分区，进行复合成一个新的分区
		SET @v_sql=CONCAT('alter table ',tableName,' reorganize partition ',StrP,' INTO (partition ',CONCAT('par',lastMonth),' values less than (',lastMaxDate,'))');
    PREPARE stmt FROM @v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for procedure_partitionByWeek
-- ----------------------------
DROP PROCEDURE IF EXISTS `procedure_partitionByWeek`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_partitionByWeek`(IN tableName VARCHAR(20),IN timeColName VARCHAR(20))
    COMMENT '每周按时添加表分区的存储过程，由定时任务调用'
BEGIN
	-- 定义现有的分区数量
    DECLARE p_id INT;
	-- 定义以日期作为分区名称
    DECLARE currentDate VARCHAR(20);
	-- 定义下一周的日期
    DECLARE nextDate DATE;
	-- 定义分区表中现有的最大的分区日期
    DECLARE lastDate LONG;

    -- 获取表中的现有的分区数量
    SELECT COUNT(partition_name) INTO p_id FROM INFORMATION_SCHEMA.PARTITIONS 
                                        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
    IF p_id=0 THEN
		-- 如果没有表中分区，获取一周后第一天的时间值，根据此值设置时间分区，赋值给nextDate
		SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) INTO nextDate FROM DUAL;
		SELECT DATE_FORMAT(nextDate, '%Y%m%d') INTO currentDate FROM DUAL;
		
		-- 拼接SQL语句，创建分区语句
		SET @v_sql=CONCAT('alter table ',tableName,' partition by range(TO_DAYS(',timeColName,'))(partition ',CONCAT('par',currentDate),' values less than (TO_DAYS(\'',nextDate,'\')))');
    ELSE
        -- 获取表中现有的最大的分区日期，赋值给lastDate
        SELECT MAX(partition_description) des INTO lastDate FROM INFORMATION_SCHEMA.PARTITIONS 
                                        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = tableName;
		-- 获取下一周的日期，赋值给nextDate
        SELECT DATE_ADD(FROM_DAYS(lastDate),INTERVAL 7 DAY) INTO nextDate FROM DUAL;
		SELECT DATE_FORMAT(nextDate, '%Y%m%d') INTO currentDate  FROM DUAL;
		
		-- 拼接SQL语句，添加分区语句
        SET @v_sql=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',currentDate),' values less than (TO_DAYS(\'',nextDate,'\')))');
    END IF;
    PREPARE stmt FROM @v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
;;
DELIMITER ;

-- 第一次执行procedure_partitionByWeek存储过程
CALL procedure_partitionByWeek("tagtaccountlog","OPTIME");
CALL procedure_partitionByWeek("tagtprepaylog","OPTIME");

-- 开启mysql事件
SET GLOBAL event_scheduler = ON;

-- ----------------------------
-- Event structure for event_arrange_month
-- ----------------------------
DROP EVENT IF EXISTS `event_arrange_month`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `event_arrange_month` ON SCHEDULE EVERY 1 MONTH STARTS DATE_SUB(DATE_ADD(CURDATE(), INTERVAL 1 MONTH ),INTERVAL 0 second) ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN
	CALL procedure_arrangeByMonth("tagtaccountlog");
	CALL procedure_arrangeByMonth("tagtprepaylog");
END
;;
DELIMITER ;

-- ----------------------------
-- Event structure for event_create_week_par
-- ----------------------------
DROP EVENT IF EXISTS `event_create_week_par`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `event_create_week_par` ON SCHEDULE EVERY 1 WEEK STARTS DATE_SUB(DATE_ADD(CURDATE(), INTERVAL 7 DAY),INTERVAL 0 second) ON COMPLETION PRESERVE ENABLE 
DO 
BEGIN
	CALL procedure_partitionByWeek("tagtaccountlog","OPTIME");
	CALL procedure_partitionByWeek("tagtprepaylog","OPTIME");
END
;;
DELIMITER ;