MySQL数据库怎么按每月分区_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执行的效果。

上边自动分区方案是转载，参考一下，你也可以根据自己的想法来做。收起