DELIMITER $$
DROP PROCEDURE IF EXISTS `set_partition`$$
CREATE PROCEDURE `set_partition`(tableName VARCHAR(45),#表名 timefield VARCHAR(45),#分区时间字段 parnum INT#每个分区数据量大小 ) BEGIN DECLARE flag INT DEFAULT 0; /* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/ DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION; WHILE flag=0 DO BEGIN SET @rnum=0,@pname=0,@num=0,@lasttime=0,@tfunction=‘‘; /* 到系统表查出最后分区条数,最后分区名*/ SELECT TABLE_ROWS,PARTITION_name INTO @rnum,@pname FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA=‘riertrace‘ AND table_name=tableName ORDER BY partition_ordinal_position DESC LIMIT 1; /* 到系统表查出表分区数*/ SELECT COUNT(*) INTO @num FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA=‘riertrace‘ AND table_name =tableName; IF @rnum>parnum THEN BEGIN #读出下个分区的时间的信息来拆分最后分区； SET @s1=CONCAT(‘select ‘,timefield,‘ into @lasttime from ‘,tablename,‘ order by id desc limit ‘,@rnum-parnum,‘,1‘); PREPARE stmt FROM @s1; EXECUTE stmt; #获取时间格式 SET @s2=CONCAT(‘select IF(data_type="datetime","to_days","UNIX_TIMESTAMP") into @tfunction from INFORMATION_SCHEMA.columns where TABLE_SCHEMA="riertrace" and table_name ="‘,tablename,‘" and column_name = "‘,timefield,‘"‘); PREPARE stmt2 FROM @s2; EXECUTE stmt2; /* 修改表，把最后分区的后面拆分为2个区 */ SET @s1=CONCAT(‘ALTER TABLE ‘,tableName,‘ REORGANIZE PARTITION ‘,@pname,‘ INTO (PARTITION p‘,DATE_FORMAT(@lasttime,"%Y%m%d"),‘ VALUES LESS THAN (‘,@tfunction,‘("‘,DATE_ADD(@lasttime, INTERVAL 1 DAY),‘")), PARTITION ‘,@pname,‘ VALUES LESS THAN MAXVALUE)‘); PREPARE stmt2 FROM @s1; EXECUTE stmt2; END; ELSE BEGIN SET flag=1; END; END IF; END; END WHILE; /* 提交 */ COMMIT ; END$$
DELIMITER ;
ALTER TABLE `riertrace`.`t_mediainfo` ENGINE = MyISAM, CHANGE `EnableTime` `EnableTime` DATETIME NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (`ID`, `EnableTime`), ADD INDEX (`Barcode`, `ExpectTime`);
ALTER TABLE `riertrace`.`medialist` ENGINE = MyISAM PARTITION BY RANGE(UNIX_TIMESTAMP(operateTime)) ( PARTITION p20060101 VALUES LESS THAN (UNIX_TIMESTAMP(‘2006-01-01‘)), PARTITION pnow VALUES LESS THAN MAXVALUE); ALTER TABLE `riertrace`.`pagebarcode` ENGINE = MyISAM PARTITION BY RANGE(to_days(operateTime)) ( PARTITION p20060101 VALUES LESS THAN (to_days(‘2006-01-01‘)), PARTITION pnow VALUES LESS THAN MAXVALUE); ALTER TABLE `riertrace`.`t_mediainfo` ENGINE = MyISAM PARTITION BY RANGE(to_days(enableTime)) ( PARTITION p20060101 VALUES LESS THAN (to_days(‘2006-01-01‘)), PARTITION pnow VALUES LESS THAN MAXVALUE);
call set_partition(‘medialist‘,‘operateTime‘,3500);
call set_partition(‘pagebarcode‘,‘operateTime‘,3500);
call set_partition(‘t_mediainfo‘,‘enableTime‘,3500);
DELIMITER || CREATE EVENT auto_set_partitions ON SCHEDULE EVERY 15 DAY DO BEGIN
call set_partition(‘medialist‘,‘operateTime‘,3500);
call set_partition(‘pagebarcode‘,‘operateTime‘,3500);
call set_partition(‘t_mediainfo‘,‘enableTime‘,3500); END || DELIMITER ;