mysql partitions by range auto (mysql 自动创建按年月的表分区)

CREATE TABLE `test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键’, `datetime` int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘时间’, PRIMARY KEY (`id`,`datetime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘测试表分区’/*!50100 PARTITION BY RANGE (datetime)(PARTITION p201511 VALUES LESS THAN (UNIX_TIMESTAMP(“2015-12-01 00:00:00”)) ENGINE = InnoDB, PARTITION p201512 VALUES LESS THAN (UNIX_TIMESTAMP(“2016-01-01 00:00:00”)) ENGINE = InnoDB) */;

2、创建存储过程，用于下面MYSQL的event定时器调用,自动创建就是event来完成的。这段存储过程的内容就是创建下月分区的意思。

DELIMITER $$

#该表所在数据库名称USE `test`$$DROP PROCEDURE IF EXISTS `create_partition_by_month`$$CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))BEGIN DECLARE ROWS_CNT INT UNSIGNED; DECLARE BEGINTIME TIMESTAMP; DECLARE ENDTIME INT UNSIGNED; DECLARE PARTITIONNAME VARCHAR(16); DECLARE ENDTIME_DATETIME VARCHAR(30); SET BEGINTIME = DATE(NOW() – INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH); SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, ‘p%Y%m’ ); SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH); SET ENDTIME_DATETIME = FROM_UNIXTIME(ENDTIME); SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME; IF ROWS_CNT = 0 THEN

SET @SQL = CONCAT( ‘ALTER TABLE `’, IN_SCHEMANAME, ‘`.`’, IN_TABLENAME, ‘`’, ‘ ADD PARTITION (PARTITION ‘, PARTITIONNAME, ” VALUES LESS THAN (UNIX_TIMESTAMP(‘”, 

ENDTIME_DATETIME ,“‘)) ENGINE = InnoDB);” );

PREPARE STMT FROM @SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT; ELSE SELECT CONCAT(“partition `”, PARTITIONNAME, “` for table `”,IN_SCHEMANAME, “.”, IN_TABLENAME, “` already exists”) AS result; END IF;END$$DELIMITER ;

show procedure status where db=’logtop’;

DELIMITER $$

#该表所在的数据库名称USE `test`$$CREATE EVENT IF NOT EXISTS `e_part_manage`ON SCHEDULE EVERY 1 MINUTE #执行周期，还有天、月等等STARTS ‘2016-01-07 18:27:00’ON COMPLETION PRESERVEENABLECOMMENT ‘Creating partitions’DO BEGIN

#调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称 CALL test.create_partition_by_month(‘test’,‘test’);END$$DELIMITER ;

3.1查看已经创建的event

SELECT * FROM information_schema.EVENTS;