MySQL日志表按月分区

1.创建日志表

DROP TABLE IF EXISTS syslogs;CREATE TABLE `syslogs` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `content` varchar(16) DEFAULT NULL,  `ip` varchar(16) DEFAULT NULL,  `createtime` datetime NOT NULL,  PRIMARY KEY (`id`,`createtime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.测试数据

delimiter //drop procedure if exists test_data;create procedure test_data(IN num int )begin    declare v1 int default 0;    declare ipVal varchar(50);    declare timeVal varchar(50);    declare contentVal varchar(50);    declare exit handler for sqlexception rollback;    start transaction;    while v1 < num    do        set v1 = v1 +1;        set ipVal = concat(round(1+rand()*254),'.',round(1+rand()*254),'.',round(1+rand()*254),'.',round(1+rand()*254));        set contentVal = 'test';        set timeVal = concat(round(2014 + rand()*2),'-',round(1 + rand()*11),'-',round(1 + rand()*27),' ',round(1+rand()*22),':',round(1+rand()*58),':',round(1+rand()*58));        insert into syslogs(content,ip,createtime) values ('test',ipVal,timeVal);    end while;    delete from syslogs where createtime > now();    commit;end//

3.日志表分区初始化

/* *初始化表分区 *表没有数据，添加本月分区 *表有数据，将所有数据按月分区 @tableName 表名 @columName 时间字段列名*/delimiter //drop procedure if exists partitionInit;create procedure partitionInit(IN tableName varchar(50),IN columName varchar(50))begin    set @s0 = concat('select max(',columName,') into @a from ',tableName);    prepare stmt0 from @s0;    execute stmt0;    deallocate prepare stmt0;    set @s1 = concat('select min(',columName,') into @b from ',tableName);    prepare stmt1 from @s1;    execute stmt1;    deallocate prepare stmt1;    set @maxDate = @a;    set @minDate = @b;    set @s2 = concat('alter table ',tableName,' partition by RANGE(to_days(',columName,'))(');    set @endDate = now();    if @minDate is null    then set @minDate = @endDate;         set @maxDate = @endDate;    end if;    set @minDate = date(date(@minDate)-day(@minDate)+1);    set @maxDate = date(date(@maxDate)-day(@maxDate)+1);    while to_days(@minDate)<to_days(@maxDate)      do      set @s2= concat(@s2,' PARTITION ',tableName,'_',date_format(@minDate,'%Y%m'),' VALUES less than (to_days("',date_add(@minDate,interval 1 month),'")),');      set @minDate=  date_add(@minDate,interval 1 month);    end while;    set @s2=concat(@s2,' PARTITION ',tableName,'_',date_format(@minDate,'%Y%m'),' VALUES less than (to_days("',date_add(@minDate,interval 1 month),'")));');    prepare stmt2 from @s2;    execute stmt2;    deallocate prepare stmt2;end//

4.添加次月分区

/* *添加下月表分区 前提：表已经分区过 @tableName 表名 @columName 时间字段列名*/delimiter //drop procedure if exists partitionAdd;create procedure partitionAdd(IN tableName varchar(50),IN columName varchar(50))begin    set @enddate = date_add(date(now())-day(now())+1,interval 2 month);    set @s=concat('alter table ',tableName,' add partition (partition ',tableName,'_',date_format(date_add(date(now())-day(now())+1,interval 1 month),'%Y%m'),' VALUES less than (to_days("',@enddate,'")));');    prepare stmt from @s;    execute stmt;    deallocate prepare stmt;end//

5.定时任务调度

CREATE EVENT autoPartition    ON SCHEDULE    EVERY 1 month    DO    call partitionAdd('syslogs','createtime');