DROP PROCEDURE IF EXISTS createTablePartition;
CREATE PROCEDURE createTablePartition()
BEGIN
DECLARE currentYear INT DEFAULT 2013;
DECLARE last_year INT DEFAULT 2012;
DECLARE next_year INT DEFAULT 2014;
DECLARE janunary_max_day VARCHAR(32) DEFAULT '01-31';
DECLARE march_max_day VARCHAR(32) DEFAULT '03-31';
DECLARE may_max_day VARCHAR(32) DEFAULT '05-31';
DECLARE july_max_day VARCHAR(32) DEFAULT '07-31';
DECLARE aug_max_day VARCHAR(32) DEFAULT '08-31';
DECLARE october_max_day VARCHAR(32) DEFAULT '10-31';
DECLARE decem_max_day VARCHAR(32) DEFAULT '12-31';
DECLARE april_max_day VARCHAR(32) DEFAULT '04-30';
DECLARE june_max_day VARCHAR(32) DEFAULT '06-30';
DECLARE novem_max_day VARCHAR(32) DEFAULT '11-30';
DECLARE septem_max_day VARCHAR(32) DEFAULT '09-30';
DECLARE febru_max_day VARCHAR(32) DEFAULT '02-22';
DECLARE temp_month VARCHAR(32);
DECLARE temp_day VARCHAR(32);
DECLARE the_max_day_in_month int;
DECLARE temp_strDate VARCHAR(32);
DECLARE temp_partition BLOB;
DECLARE i int;
DECLARE j int;
set i=1;
set j=1;
set febru_max_day=LAST_DAY(STR_TO_DATE(CONCAT(currentYear,'-',febru_max_day),'%Y-%m-%d %H:%i:%s'));#SET FEBRUARY LAST DAY
set currentYear=YEAR(CURDATE());# GET CURRENT YEAR
set next_year=currentYear+1;
set temp_partition=CONCAT('','ALTER TABLE test_history2 PARTITION BY RANGE(','TO_DAYS(COLLECT_TIME)) (');
while(i<=12)do
if i>9 THEN
set temp_month=CONCAT('',i);
else
set temp_month=CONCAT('0',i);
end if;
if (temp_month = MONTH(CONCAT(currentYear,'-',janunary_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',march_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',may_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',july_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',aug_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',october_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',decem_max_day)))THEN
set the_max_day_in_month = 31;
elseif (temp_month = MONTH(CONCAT(currentYear,'-',april_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',june_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',septem_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',novem_max_day)))THEN
set the_max_day_in_month = 30;
else
set the_max_day_in_month = DAY(febru_max_day);
end if;
set j=1;
while(j<=the_max_day_in_month)DO
if j>9 THEN
set temp_day=CONCAT('',j);
else
set temp_day=CONCAT('0',j);
end if;
set temp_strDate=CONCAT(currentYear,'-',temp_month,'-',temp_day,' ','00:00:00');
set temp_partition=CONCAT(temp_partition,'PARTITION p',currentYear,'',temp_month,'',temp_day,' VALUES LESS THAN(','TO_DAYS("',temp_strDate,'")),');
SET j=j+1;
end while;
# SELECT the_max_day_in_month,temp_month;
set i=i+1;
end while;
set temp_strDate=CONCAT(next_year,'-01','-','01',' ','00:00:00');
set temp_partition=CONCAT(temp_partition,'PARTITION p',next_year,'0101',' VALUES LESS THAN(','TO_DAYS("',temp_strDate,'")),');
set temp_partition=CONCAT(temp_partition,'PARTITION theMaxPartition VALUES LESS THAN MAXVALUE');
set temp_partition=CONCAT(temp_partition,');');
SELECT temp_partition;
SET @sql1=temp_partition;
PREPARE sql2 FROM @sql1;
EXECUTE sql2;
DEALLOCATE PREPARE sql2;
#first,get the max day for every month;
# SELECT LAST_DAY(STR_TO_DATE(CONCAT(currentYear,'-',febru_max_day),'%Y-%m-%d %H:%i:%s'));
END;
call createTablePartition();
CREATE PROCEDURE createTablePartition()
BEGIN
DECLARE currentYear INT DEFAULT 2013;
DECLARE last_year INT DEFAULT 2012;
DECLARE next_year INT DEFAULT 2014;
DECLARE janunary_max_day VARCHAR(32) DEFAULT '01-31';
DECLARE march_max_day VARCHAR(32) DEFAULT '03-31';
DECLARE may_max_day VARCHAR(32) DEFAULT '05-31';
DECLARE july_max_day VARCHAR(32) DEFAULT '07-31';
DECLARE aug_max_day VARCHAR(32) DEFAULT '08-31';
DECLARE october_max_day VARCHAR(32) DEFAULT '10-31';
DECLARE decem_max_day VARCHAR(32) DEFAULT '12-31';
DECLARE april_max_day VARCHAR(32) DEFAULT '04-30';
DECLARE june_max_day VARCHAR(32) DEFAULT '06-30';
DECLARE novem_max_day VARCHAR(32) DEFAULT '11-30';
DECLARE septem_max_day VARCHAR(32) DEFAULT '09-30';
DECLARE febru_max_day VARCHAR(32) DEFAULT '02-22';
DECLARE temp_month VARCHAR(32);
DECLARE temp_day VARCHAR(32);
DECLARE the_max_day_in_month int;
DECLARE temp_strDate VARCHAR(32);
DECLARE temp_partition BLOB;
DECLARE i int;
DECLARE j int;
set i=1;
set j=1;
set febru_max_day=LAST_DAY(STR_TO_DATE(CONCAT(currentYear,'-',febru_max_day),'%Y-%m-%d %H:%i:%s'));#SET FEBRUARY LAST DAY
set currentYear=YEAR(CURDATE());# GET CURRENT YEAR
set next_year=currentYear+1;
set temp_partition=CONCAT('','ALTER TABLE test_history2 PARTITION BY RANGE(','TO_DAYS(COLLECT_TIME)) (');
while(i<=12)do
if i>9 THEN
set temp_month=CONCAT('',i);
else
set temp_month=CONCAT('0',i);
end if;
if (temp_month = MONTH(CONCAT(currentYear,'-',janunary_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',march_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',may_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',july_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',aug_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',october_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',decem_max_day)))THEN
set the_max_day_in_month = 31;
elseif (temp_month = MONTH(CONCAT(currentYear,'-',april_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',june_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',septem_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',novem_max_day)))THEN
set the_max_day_in_month = 30;
else
set the_max_day_in_month = DAY(febru_max_day);
end if;
set j=1;
while(j<=the_max_day_in_month)DO
if j>9 THEN
set temp_day=CONCAT('',j);
else
set temp_day=CONCAT('0',j);
end if;
set temp_strDate=CONCAT(currentYear,'-',temp_month,'-',temp_day,' ','00:00:00');
set temp_partition=CONCAT(temp_partition,'PARTITION p',currentYear,'',temp_month,'',temp_day,' VALUES LESS THAN(','TO_DAYS("',temp_strDate,'")),');
SET j=j+1;
end while;
# SELECT the_max_day_in_month,temp_month;
set i=i+1;
end while;
set temp_strDate=CONCAT(next_year,'-01','-','01',' ','00:00:00');
set temp_partition=CONCAT(temp_partition,'PARTITION p',next_year,'0101',' VALUES LESS THAN(','TO_DAYS("',temp_strDate,'")),');
set temp_partition=CONCAT(temp_partition,'PARTITION theMaxPartition VALUES LESS THAN MAXVALUE');
set temp_partition=CONCAT(temp_partition,');');
SELECT temp_partition;
SET @sql1=temp_partition;
PREPARE sql2 FROM @sql1;
EXECUTE sql2;
DEALLOCATE PREPARE sql2;
#first,get the max day for every month;
# SELECT LAST_DAY(STR_TO_DATE(CONCAT(currentYear,'-',febru_max_day),'%Y-%m-%d %H:%i:%s'));
END;
call createTablePartition();