创建存储过程
-- ----------------------------
-- Procedure structure for procSetDeBroadbandScoreDetail
-- ----------------------------
DROP PROCEDURE IF EXISTS `procSetDeBroadbandScoreDetail`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `procSetDeBroadbandScoreDetail`()
BEGIN
SELECT
REPLACE (partition_name, 'p', '') INTO @PMAX
FROM
INFORMATION_SCHEMA. PARTITIONS
WHERE
TABLE_SCHEMA = 'probe_fomsts'
AND table_name = 'de_broadband_score_detail'
ORDER BY
partition_ordinal_position DESC
LIMIT 1;
SELECT
REPLACE (
partition_description,
"'",
''
) INTO @DNAME
FROM
(
SELECT
*
FROM
INFORMATION_SCHEMA. PARTITIONS
WHERE
TABLE_SCHEMA = 'probe_fomsts'
AND table_name = 'de_broadband_score_detail'
ORDER BY
partition_ordinal_position DESC
LIMIT 1,2
) g
WHERE
TABLE_SCHEMA = 'probe_fomsts'
AND table_name = 'de_broadband_score_detail'
ORDER BY
partition_ordinal_position DESC
LIMIT 1;
SET @t=CONCAT('alter table probe_fomsts.de_broadband_score_detail reorganize partition p',@PMAX,' into(partition p',@PMAX,' values less than (''',DATE_ADD(@DNAME,INTERVAL 1 HOUR),'''),partition p',@PMAX+1,' values less than MAXVALUE)');
SELECT @t;
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
COMMIT;
END
;;
DELIMITER ;
创建事件调用存储过程
-- ----------------------------
-- Event structure for e_procSetDeWebsiteScoreInfoHour
-- ----------------------------
DROP EVENT IF EXISTS `e_procSetDeWebsiteScoreInfoHour`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` EVENT `e_procSetDeWebsiteScoreInfoHour` ON SCHEDULE EVERY 1 HOUR STARTS '2017-11-01 16:09:27' ON COMPLETION PRESERVE ENABLE DO BEGIN
CALL procSetDeWebsiteScoreInfoHour();
END
;;
DELIMITER ;