mysql-- 每半月一个分区，自动维护 建表语句 drop table if exists terminal_parameter; create table `terminal_parameter` ( `terminal_parameter_id` int(11) not null auto_increment, `serial` int(11) default null, `network_type` char(1) default nu
mysql-- 每半月一个分区，自动维护
建表语句
drop table if exists terminal_parameter;
create table `terminal_parameter` (
`terminal_parameter_id` int(11) not null auto_increment,
`serial` int(11) default null,
`network_type` char(1) default null,
`mcc` int(8) default null,
`mnc` int(8) default null,
`lac` int(8) default null,
`cellid` int(8) default null,
`bsic_psc` int(8) default null,
`ta_ec_io` int(8) default null,
`bcch_rxlev_rscp` int(8) default null,
`arfcn_uarfcn` int(8) default null,
`rxq` int(8) default null,
`c1` int(8) default null,
`c2` int(8) default null,
`signal_intensity` int(8) default null,
`error_rate` int(8) default null,
`alarm_type` varchar(16) default null,
`txpower` int(8) default null,
`small_running_number` int(8) default null,
`createtime` datetime not null,
`userid` int(8) not null,
`terminal_id` int(8) default null,
`state` char(1) default '0',
`order_definition_id` int(8) default null,
`order_code` varchar(20) default null,
`charg_voltage` float(8,2) default null,
`battery_voltage` float(8,2) default null,
`temprad` float(8,2) default null,
`run_state` int(8) default null,
`switching_value1` int(8) default null,
`switching_value2` int(8) default null,
`bcch_freq` int(8) default null,
`rxlev` int(8) default null,
`rxlev_full` int(8) default null,
`rxlev_sub` int(8) default null,
`rxqual` int(8) default null,
`rxqual_full` int(8) default null,
`rxqual_sub` int(8) default null,
`idle_ts` int(8) default null,
`timing_advance` int(8) default null,
`tch_efr_out` int(8) default null,
`tch_efr_in` int(8) default null,
`dtx` int(8) default null,
`major_cycle_frequency` int(8) default null,
primary key (`terminal_parameter_id`,`createtime`),
key `idx_createtime` (`createtime`),
key `idx_terminal_id` (`terminal_id`)
) engine=innodb default charset=utf8
partition by range(to_days (createtime))
(
partition p20101115 values less than (to_days('2010-11-15')),
partition p20101130 values less than (to_days('2010-11-30')),
partition p20101215 values less than (to_days('2010-12-15')),
partition p20101231 values less than (to_days('2010-12-31')),
partition p20110115 values less than (to_days('2011-01-15')),
partition p20110131 values less than (to_days('2011-01-31')),
partition p20110215 values less than (to_days('2011-02-15')),
partition p20110228 values less than (to_days('2011-02-28')),
partition p20110315 values less than (to_days('2011-03-15')),
partition p20110331 values less than (to_days('2011-03-31')),
partition p20110415 values less than (to_days('2011-04-15')),
partition p20110430 values less than (to_days('2011-04-30'))
);
存储过程代码：
* 每隔15天执行一次
/* 程序功能：循环使用分区，每半个月一个分区，保留6个月的数据
时间：2010-11-09 */
drop procedure if exists set_partition;
create procedure set_partition()
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_schema='mydb_1' and table_name='terminal_parameter' order by partition_ordinal_position desc limit 1;
/* 判断最大分区的时间段，如果是前半个月的，那么根据情况需要加13,14,15,16天
如果是后半个月的，那么直接加15天。 +0 是为了把日期都格式化成yyyymmdd这样的格式*/
if (day(@p12_name) case day(last_day(@p12_name))
when 31 then set @max_date= date(date_add(@p12_name+0,interval 16 day))+0 ;
when 30 then set @max_date= date(date_add(@p12_name+0,interval 15 day))+0 ;
when 29 then set @max_date= date(date_add(@p12_name+0,interval 14 day))+0 ;
when 28 then set @max_date= date(date_add(@p12_name+0,interval 13 day))+0 ;
end case;
else
set @max_date= date(date_add(@p12_name+0, interval 15 day))+0;
end if;
/* 修改表，在最大分区的后面增加一个分区，时间范围加半个月 */
set @s1=concat('alter table terminal_parameter add partition (partition p',@max_date,' values less than (to_days (''',date(@max_date),''')))');
prepare stmt2 from @s1;
execute stmt2;
deallocate prepare stmt2;
/* 取出最小的分区的名称，并删除掉 。
注意：删除分区会同时删除分区内的数据，慎重 */
select partition_name into @p0_name from information_schema.partitions where table_schema='mydb_1' and table_name='terminal_parameter' order by partition_ordinal_position limit 1;
set @s=concat('alter table terminal_parameter drop partition ',@p0_name);
prepare stmt1 from @s;
execute stmt1;
deallocate prepare stmt1;
/* 提交 */
commit ;
end;
计划任务代码：
create event e_set_partition
on schedule
every 15 day starts '2011-04-30 23:59:59'
do
call set_partition();