mysql 按小时建分区表_创建 EVENT 每小时创建 MySQL 表分区

DELIMITER $$

USE `testdb`$$

CREATE EVENT IF NOT EXISTS `create_partition`

ON SCHEDULE

EVERY 1 hour

DO BEGIN

set @starttime=now();

set @endtime=@starttime - interval date_format(@starttime,'%s') second - interval date_format(@starttime,'%i') minute + interval 2 hour;

set @pname=concat('P',date_format(@starttime+ interval 1 hour,'%Y%m%d%H'));

SET @SQL = CONCAT( 'ALTER TABLE testdb.t2 ADD PARTITION (PARTITION ', @PNAME, ' VALUES LESS THAN (UNIX_TIMESTAMP(\'', @ENDTIME, '\')));' );

PREPARE STMT FROM @SQL;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

END$$

DELIMITER ;

参考博文：http://www.zabbix.org/wiki/Docs/howto/mysql_partitioning