mysql自动分区

来源：互联网发布：制表格用什么软件编辑：程序博客网时间：2024/05/16 12:11

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_pton scheduleevery 1 minutestarts '2015-03-09 15:19:02'doBEGIN    call proc_test_log_pt();END$$delimiter ;

你可以重建前面所述的分区表，然后看event执行的效果

0 0