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执行的效果