1、在进行自动增加分区前一定得先对表手动分几个区
alter table tb_3a_huandan_detail partition by range (to_days(servicestarttime))( partition p20160523 values less than (to_days('2016-05-23')), partition p20160524 values less than (to_days('2016-05-24')), partition p20160525 values less than (to_days('2016-05-25')), partition p20160526 values less than (to_days('2016-05-26')), partition p20160527 values less than (to_days('2016-05-27')))
2、分区存过如下：
delimiter $$use `nres`$$drop procedure if exists `create_partition_3ahuadan`$$create definer=`nres`@`%` procedure `create_partition_3ahuadan`()begin/* 事务回滚，其实放这里没什么作用，alter table是隐式提交，回滚不了的。*/ declare exit handler for sqlexception rollback; start transaction;/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */ select replace(partition_name,'p','') into @p12_name from information_schema.partitions where table_name='tb_3a_huandan_detail' order by partition_ordinal_position desc limit 1; set @max_date= date(date_add(@p12_name+0, interval 1 day))+0;/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */ set @s1=concat('alter table tb_3a_huandan_detail add partition (partition p',@max_date,' values less than (to_days (''',date(@max_date),''')))'); /* 输出查看增加分区语句*/ select @s1; prepare stmt2 from @s1; execute stmt2; deallocate prepare stmt2;/* 取出最小的分区的名称，并删除掉 。 注意：删除分区会同时删除分区内的数据，慎重 */ /*select partition_name into @p0_name from information_schema.partitions where table_name='tb_3a_huandan_detail' order by partition_ordinal_position limit 1; set @s=concat('alter table tb_3a_huandan_detail drop partition ',@p0_name); prepare stmt1 from @s; execute stmt1; deallocate prepare stmt1; *//* 提交 */ commit ; end$$delimiter ;
3、增加定时事件
delimiter ||create event partition_3ahuadan_event on schedule every 1 day starts '2016-05-27 23:59:59' do begin call nres.`create_partition_3ahuadan`; end ||delimiter ;
以上就是mysql每天自动增加分区如何实现的详细内容。