MySQL-- 每半月一个分区，自动维护_mysql表分区多久分一次-CSDN博客

建表语句

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)<=15) THEN 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;