建立 EVENT 每小時建立 MySQL 表分區

標籤：MySQL

DELIMITER $$

 

USE `testdb`$$

CREATE EVENT IF NOT EXISTS `create_partition`

ON SCHEDULE

EVERY 1 hour 

DO BEGIN

set @starttime=now();

set @[email protected] - 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

建立 EVENT 每小時建立 MySQL 表分區