mysql 表分区按照数据量自动分区+定时器

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 ;

mysql 表分区按照数据量自动分区+定时器,古老的榕树,5-wow.com