表分区的过程
防盗链，来自博客 http://www.itnihao.com 创建存储过程 分区创建的存储过程
DELIMITER $$ CREATEPROCEDURE`partition_create`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT) BEGIN
/*
SCHEMANAME = The DB schema in which to make changes
TABLENAME = The table with partitions to potentially delete
PARTITIONNAME = The name of the partition to create
*/
/*
Verify that the partition does not already exist
* DECLARE RETROWS INT; SELECTCOUNT(1)INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME ANDTABLE_NAME= TABLENAME AND partition_name = PARTITIONNAME; IF RETROWS =0THEN /*
1. Print a message indicating that a partition was created
2. Create the SQL to create the partition
3. Execute the SQL from #2. */ SELECT CONCAT("partition_create(", SCHEMANAME,",", TABLENAME,",", PARTITIONNAME,",", CLOCK,")")AS msg; SET @SQL= CONCAT('ALTER TABLE ', SCHEMANAME,'.', TABLENAME,' ADD PARTITION (PARTITION ', PARTITIONNAME,' VALUES LESS THAN (', CLOCK,'));'); PREPARE STMT FROM @SQL; EXECUTE STMT;
DEALLOCATE PREPARE STMT; ENDIF; END$$
DELIMITER ; 分区删除的存储过程
DELIMITER $$ CREATEPROCEDURE`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 INTDEFAULTFALSE; 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 ANDTABLE_NAME= TABLENAME ANDCAST(SUBSTRING(partition_name FROM2)ASUNSIGNED)< DELETE_BELOW_PARTITION_DATE; DECLARE CONTINUE HANDLER FORNOT 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; ENDIF; 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 indicatethat no changes were made.
*/ SELECT CONCAT(SCHEMANAME,".", TABLENAME)AS`table`,"N/A"AS`partitions_deleted`; ENDIF; END$$
DELIMITER ; 分区维护的存储过程
DELIMITER $$ CREATEPROCEDURE`partition_maintenance`(SCHEMA_NAME VARCHAR(32),TABLE_NAMEVARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT) BEGIN DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16); DECLARE PARTITION_NAME VARCHAR(16); DECLARE LESS_THAN_TIMESTAMP INT; DECLARE CUR_TIME INT; CALL partition_verify(SCHEMA_NAME,TABLE_NAME, HOURLY_INTERVAL); SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')); IFDATE(NOW())='2014-04-01'THEN SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NOW(),INTERVAL1DAY),'%Y-%m-%d 00:00:00')); ENDIF; SET @__interval =1;
create_loop: LOOP IF @__interval > CREATE_NEXT_INTERVALS THEN
LEAVE create_loop; ENDIF; SET LESS_THAN_TIMESTAMP = CUR_TIME +(HOURLY_INTERVAL * @__interval *3600); SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL *(@__interval -1)*3600,'p%Y%m%d%H00'); CALL partition_create(SCHEMA_NAME,TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP); SET @__interval=@__interval+1; END LOOP; SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(),INTERVAL KEEP_DATA_DAYS DAY),'%Y%m%d0000'); CALL partition_drop(SCHEMA_NAME,TABLE_NAME, OLDER_THAN_PARTITION_DATE); END$$
DELIMITER ; 分区校验的存储过程
DELIMITER $$ CREATEPROCEDURE`partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11)) BEGIN DECLARE PARTITION_NAME VARCHAR(16); DECLARE RETROWS INT(11); DECLARE FUTURE_TIMESTAMP TIMESTAMP; /** Check if any partitions exist for the given SCHEMANAME.TABLENAME. */ SELECTCOUNT(1)INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME ANDTABLE_NAME= TABLENAME AND partition_name ISNULL; /* * If partitions do not exist, go ahead and partition the table*/ IF RETROWS =1THEN
/*
* Take the current date at 00:00:00 and add HOURLYINTERVAL to it. This is the timestamp below which we will store values.
* We begin partitioning based on the beginning of a day. This is because we don't want to generate a random partition
* that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
* end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000").
*/ SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE()," ",'00:00:00')); SET PARTITION_NAME = DATE_FORMAT(CURDATE(),'p%Y%m%d%H00'); -- Create the partitioning query SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME,".", TABLENAME," PARTITION BY RANGE(`clock`)"); SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL,"(PARTITION ", PARTITION_NAME," VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP),"));"); -- Run the partitioning query PREPARE STMT FROM @__PARTITION_SQL; EXECUTE STMT;
DEALLOCATE PREPARE STMT; ENDIF; END$$
DELIMITER ;
mysql> show create table history\G;
*************************** 1. row ***************************
Table: history
Create Table: CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p201405060000 VALUES LESS THAN (1399392000) ENGINE = InnoDB,
PARTITION p201405070000 VALUES LESS THAN (1399478400) ENGINE = InnoDB,
PARTITION p201405080000 VALUES LESS THAN (1399564800) ENGINE = InnoDB,
PARTITION p201405090000 VALUES LESS THAN (1399651200) ENGINE = InnoDB,
PARTITION p201405100000 VALUES LESS THAN (1399737600) ENGINE = InnoDB,
PARTITION p201405110000 VALUES LESS THAN (1399824000) ENGINE = InnoDB,
PARTITION p201405120000 VALUES LESS THAN (1399910400) ENGINE = InnoDB,
PARTITION p201405130000 VALUES LESS THAN (1399996800) ENGINE = InnoDB,
PARTITION p201405140000 VALUES LESS THAN (1400083200) ENGINE = InnoDB,
PARTITION p201405150000 VALUES LESS THAN (1400169600) ENGINE = InnoDB,
PARTITION p201405160000 VALUES LESS THAN (1400256000) ENGINE = InnoDB,
PARTITION p201405170000 VALUES LESS THAN (1400342400) ENGINE = InnoDB,
PARTITION p201405180000 VALUES LESS THAN (1400428800) ENGINE = InnoDB,
PARTITION p201405190000 VALUES LESS THAN (1400515200) ENGINE = InnoDB) */
数据库
发布于：2021-07-04 09:45
|
阅读数：538
|
评论：0
QQ好友和群
QQ空间
