mysql自动分区

1、建立分区表

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

2、增加分区的命令

alter table test_log add partition (partition p20150302 values less than('2015-03-03'));

3、删除分区的命令

alter table test_log drop partition p20150301;

4、查询分区信息

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维护分区

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

执行前

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)

执行后

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)

也可以这样执行

mysql -uroot -p3306 test -e 'call proc_test_log_pt'

7、可以在mysql的event或os的crontab中调用上面的proc

以event为例

需要先开启event

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，为了测试方便，间隔设置为分钟

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