数据库根据当前时间分区

-- 创建数据表
CREATE TABLE `tb_user_bhavior_log` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`USER_ID` int(10) DEFAULT NULL,
`SESSION_ID` varchar(128) DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`USER_AGENT` varchar(256) DEFAULT NULL,
`EVENT_ID` int(4) DEFAULT NULL,
PRIMARY KEY (`ID`,`CREATE_TIME`),
KEY `idx_createtime` (`CREATE_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (CREATE_TIME))
(
PARTITION p20160501 VALUES LESS THAN (TO_DAYS('2016-05-01'))
);

-- 存储过程
/* 程序功能：循环使用分区，每1个月一个分区 */
drop procedure if exists Set_Partition;
create procedure Set_Partition()
begin
set @Max_date = date_add(curdate() - day(curdate()) + 1, interval 1 month)+0;

SELECT @Max_date;

/* 修改表，在最大分区的后面增加一个分区，时间范围加半个月 */
SET @s1=concat('ALTER TABLE tb_user_bhavior_log ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',date(@Max_date),''')))');
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
end

 -- 任务

CREATE EVENT e_Set_Partition
ON SCHEDULE
EVERY 1 MONTH STARTS '2016-05-28 23:59:58'
DO
call Set_Partition();

转载于:https://www.cnblogs.com/yixiwenwen/p/5458523.html