转载自: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;