MySQL周分区 生成周分区辅助存储过程

转载自：http://www.cnblogs.com/krisy/archive/2013/01/10/2854918.html

按周分区的建表语句如下:

CREATE TABLE `article` (
  `Article_ID` int(11) NOT NULL AUTO_INCREMENT,
  ...

  ...

  `Extracted_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `PRIMARYKeyID` (`Article_ID`),
  KEY `AK_Time` (`Extracted_Time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章表'
PARTITION BY RANGE (UNIX_TIMESTAMP(Extracted_Time))
(

  -- 第一个的分区一般是没有数据的, 因为有时候查询某个分区的数据, 会自动去查第一个分区, 为了加快查询速度, 特别设置第一个分区的时间是很久以前的  
  PARTITION P20100101 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01 00:00:00')) ENGINE = InnoDB, 

  PARTITION P20120923VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-23 00:00:00')) ENGINE = InnoDB, 
  PARTITION P20120930VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-30 00:00:00')) ENGINE = InnoDB, 
  PARTITION PMaxValueVALUES LESS THAN MAXVALUE ENGINE = InnoDB

);

如果要事先建好未来几年的分区(当然也可以以后用event自动调度存储过程来自动创建分区), 如何去生成分区的语句, 
生成结果如:

PARTITION P20120923VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-23 00:00:00')) ENGINE = InnoDB, 
PARTITION P20120930VALUES LESS THAN (UNIX_TIMESTAMP('2012-09-30 00:00:00')) ENGINE = InnoDB

... 
生成方法如下, 生成的分区语句保存在表temp_generate_partition_unix_time的字段partition_sentence中

drop table temp_generate_partition_unix_time;
create table temp_generate_partition_unix_time(
    id int auto_increment PRIMARY key,
    year_week varchar(10),
    date date,
    unix_time BIGINT,
    partition_sentence varchar(255)
);

CREATE PROCEDURE `sp_generate_partition_unixtime`(week_total_count_in int, begin_date_in varchar(10))
BEGIN
     /***********************************************
              生成周分区数据
      ***********************************************/

     truncate table temp_generate_partition_unix_time;

     set @i=1;
     loop1: WHILE @i<=week_total_count_in DO
         set @date=DATE_ADD(begin_date_in,INTERVAL (@i-1)*7 DAY);
         INSERT into temp_generate_partition_unix_time(year_week,date,unix_time,partition_sentence)
         SELECT YEARWEEK(@date),@date,UNIX_TIMESTAMP(@date),CONCAT('PARTITION P',REPLACE(@date,'-',''),' VALUES LESS THAN (UNIX_TIMESTAMP(''',@date,' 00:00:00'')) ENGINE = InnoDB,');
        set @i=@i+1;
     END WHILE loop1;
END;

call sp_generate_partition_unixtime(180,'2012-09-23');
SELECT * from temp_generate_partition_unix_time;