MySql数据库中对表进行按月份的分区 那么表的分区会根据时间的增加而自己增加分区么

1、建立分区表

[sql] view plain copy
create table test_log
(
created datetime,
msg varchar(2000)
)partition by range columns(created)(
partition p20150301 values less than('2015-03-02')
);

2、增加分区的命令
[sql] view plain copy
alter table test_log add partition (partition p20150302 values less than('2015-03-03'));

3、删除分区的命令
[sql] view plain copy
alter table test_log drop partition p20150301;

4、查询分区信息
[sql] view plain copy
SELECT partition_name,

   cast(replace(partition_description, '''', '') AS date) AS val  

FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'test_log' AND TABLE_SCHEMA = 'test';

+----------------+------------+
| partition_name | val |
+----------------+------------+
| p20150302 | 2015-03-03 |
+----------------+------------+
1 row in set (0.01 sec)

5、建立proc维护分区
[sql] view plain copy
delimiter $$
DROP PROCEDURE IF EXISTS proc_test_log_pt

$$
CREATE PROCEDURE proc_test_log_pt()
BEGIN
DECLARE v_sysdate date;
DECLARE v_mindate date;
DECLARE v_maxdate date;
DECLARE v_pt varchar(20);
DECLARE v_maxval varchar(20);
DECLARE i int;

/*增加新分区*/
SELECT max(cast(replace(partition_description, '''', '') AS date)) AS val
INTO v_maxdate
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'test_log' AND TABLE_SCHEMA = 'test';

set v_sysdate = sysdate();

WHILE v_maxdate <= (v_sysdate + INTERVAL 7 DAY) DO
SET v_pt = date_format(v_maxdate ,'%Y%m%d');
SET v_maxval = date_format(v_maxdate + INTERVAL 1 DAY, '%Y-%m-%d');
SET @sql = concat('alter table test_log add partition (partition p', v_pt, ' values less than(''', v_maxval, '''))');
-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET v_maxdate = v_maxdate + INTERVAL 1 DAY;
END WHILE;

/*删除旧分区*/
SELECT min(cast(replace(partition_description, '''', '') AS date)) AS val
INTO v_mindate
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'test_log' AND TABLE_SCHEMA = 'test';

WHILE v_mindate <= (v_sysdate - INTERVAL 6 DAY) DO
SET v_pt = date_format(v_mindate - INTERVAL 1 DAY,'%Y%m%d');
SET @sql = concat('alter table test_log drop partition p', v_pt);
-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET v_mindate = v_mindate + INTERVAL 1 DAY;
END WHILE;

END$$

delimiter ; 6、调用proc
执行前[sql] view plain copy
mysql> select partition_name,partition_description FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='test_log' AND TABLE_SCHEMA='test';
+----------------+-----------------------+
| partition_name | partition_description |
+----------------+-----------------------+
| p20150301 | '2015-03-02' |
+----------------+-----------------------+
1 row in set (0.01 sec)

mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2015-03-09 15:12:59 |
+---------------------+
1 row in set (0.00 sec) 执行后
[sql] view plain copy
mysql> call proc_test_log_pt;
Query OK, 0 rows affected (1.13 sec)

mysql> select partition_name,partition_description FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='test_log' AND TABLE_SCHEMA='test';
+----------------+-----------------------+
| partition_name | partition_description |
+----------------+-----------------------+
| p20150303 | '2015-03-04' |
| p20150304 | '2015-03-05' |
| p20150305 | '2015-03-06' |
| p20150306 | '2015-03-07' |
| p20150307 | '2015-03-08' |
| p20150308 | '2015-03-09' |
| p20150309 | '2015-03-10' |
| p20150310 | '2015-03-11' |
| p20150311 | '2015-03-12' |
| p20150312 | '2015-03-13' |
| p20150313 | '2015-03-14' |
| p20150314 | '2015-03-15' |
| p20150315 | '2015-03-16' |
| p20150316 | '2015-03-17' |
+----------------+-----------------------+
14 rows in set (0.01 sec) 也可以这样执行
[plain] view plain copy
mysql -uroot -p3306 test -e 'call proc_test_log_pt' 7、可以在mysql的event或os的crontab中调用上面的proc
以event为例需要先开启event[sql] view plain copy
mysql> set global event_scheduler=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec) 新建一个event，为了测试方便，间隔设置为分钟
[sql] view plain copy
DELIMITER $$
drop event if exists auto_pt $$
create event auto_pt
on schedule
every 1 minute
starts '2015-03-09 15:19:02'
do
BEGIN
call proc_test_log_pt();
END$$
delimiter ; 你可以重建前面所述的分区表，然后看event执行的效果。
上边自动分区方案是转载，参考一下，你也可以根据自己的想法来做。