Mysql 每天日志表每天自动分区

目标：

visitor_log收集线上访问日志，数据越来越多，目标每天对visitor_log按天新增一个分区

计划任务开启

• 查看event是否开启 : SHOW VARIABLES LIKE ‘%event_sche%’;
• 将事件计划开启 : SET GLOBAL event_scheduler = 1;
• 将事件计划关闭 : SET GLOBAL event_scheduler = 0;

建表语句：

CREATE TABLE `visitor_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uicode` varchar(32) NOT NULL,
  `actcode` varchar(10) DEFAULT '',
  `F` varchar(30) DEFAULT '',
  `uid` bigint(20) NOT NULL,
  `element` varchar(32) DEFAULT '',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `i_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `day` int(11) DEFAULT NULL,
  `h` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,i_time),
  KEY `idx_itimef` (`i_time`,`F`)
) ENGINE=InnoDB AUTO_INCREMENT=4782637 DEFAULT CHARSET=utf8
partition by range(to_days(i_time))
(PARTITION p20180128 VALUES LESS THAN (to_days('2017-12-11')),
PARTITION p20180129 VALUES LESS THAN (to_days('2018-01-28')));

分区字段：i_time

创建计划任务:

DELIMITER  $$
CREATE EVENT  visitor_log_auto_partition
ON SCHEDULE 
              EVERY 1 day
    STARTS str_to_date('2018-01-29 00:00:00','%Y-%m-%d %T')
    COMMENT 'upv日志表,每天自动增加分区'
   DO
         BEGIN
 		  set @c_table='visitor_log';
set @c_day=DATE_SUB(curdate(),INTERVAL -1 DAY);
set @p_c_day=DATE_FORMAT(NOW(),'%Y%m%d');
set @p_name=concat('p_',@c_table,@p_c_day);
set @p_sql=concat('ALTER TABLE  ',@c_table,'  ADD PARTITION (PARTITION  ',@p_name,'   VALUES LESS THAN (TO_DAYS ("',@c_day,'")))');
select @p_sql;
PREPARE auto_create_partion from @p_sql;
execute auto_create_partion;
END  $$
delimiter ;

查看计划任务

show events;

删除计划任务：

DROP EVENT IF EXISTS visitor_log_auto_partition

表分区查看：

explain partitions select * from visitor_log;