zabbix-3.0.3 mysql表分区的方法

lsdwyl

DELIMITER $$　　CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
　　BEGIN
　　/*
　　SCHEMANAME = The DB schema in which to make changes
　　TABLENAME = The table with partitions to potentially delete
　　DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
　　*/
　　DECLARE done INT DEFAULT FALSE;
　　DECLARE drop_part_name VARCHAR(16);/*
　　Get a list of all the partitions that are older than the date
　　in DELETE_BELOW_PARTITION_DATE. All partitions are prefixed with
　　a "p", so use SUBSTRING TO get rid of that character.
　　*/
　　DECLARE myCursor CURSOR FOR
　　SELECT partition_name
　　FROM information_schema.partitions
　　WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE;
　　DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;/*
　　Create the basics for when we need to drop the partition. Also, create
　　@drop_partitions to hold a comma-delimited list of all partitions that
　　should be deleted.
　　*/
　　SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
　　SET @drop_partitions = "";/*
　　Start looping through all the partitions that are too old.
　　*/
　　OPEN myCursor;
　　read_loop: LOOP
　　FETCH myCursor INTO drop_part_name;
　　IF done THEN
　　LEAVE read_loop;
　　END IF;
　　SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
　　END LOOP;
　　IF @drop_partitions != "" THEN
　　/*
　　1. Build the SQL to drop all the necessary partitions.
　　2. Run the SQL to drop the partitions.
　　3. Print out the table partitions that were deleted.
　　*/
　　SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
　　PREPARE STMT FROM @full_sql;
　　EXECUTE STMT;
　　DEALLOCATE PREPARE STMT;SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
　　ELSE
　　/*
　　No partitions are being deleted, so print out "N/A" (Not applicable) to indicate
　　that no changes were made.
　　*/
　　SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;
　　END IF;
　　END$$
　　DELIMITER ;