mysql分区存储过程、事件实例

创建存储过程

	-- ----------------------------
	-- 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 ;