mysql 每日新增表分区

1.创建表和表分区

DROP TABLE zy.time_partition;

CREATE TABLE zy.time_partition

(TIME DATETIME NOT NULL )ENGINE=INNODB DEFAULT CHARSET=utf8

PARTITION BY RANGE(TO_DAYS(TIME))

(PARTITION p20171031 VALUES LESS THAN (TO_DAYS('2017-11-01')),

PARTITION p20171101 VALUES LESS THAN (TO_DAYS('2017-11-02'))

#DATA DIRECTORY '/data/2010-07-16'

#INDEX DIRECTORY '/data/2010-07-16'

);

2.创建每日新增表分区的存储过程

DROP PROCEDURE IF EXISTS zy.time_partition_procedure;

DELIMITER $$

CREATE PROCEDURE zy.time_partition_procedure()

BEGIN

select replace(b.partition_name,'p','') into @in_date from information_schema.PARTITIONS b where b.table_name ='time_partition' order by b.partition_ordinal_position desc limit 1;

set @max_date= DATE_ADD(@in_date,INTERVAL 1 DAY)+0 ;

set @date= DATE_ADD(@in_date,INTERVAL 1 DAY)+0 ;

SET @sql=CONCAT('ALTER TABLE zy.time_partition add PARTITION (PARTITION p',@date,' VALUES LESS THAN (TO_DAYS(''',to_days(@max_date1),''')));');

SELECT @sql;

PREPARE strsql FROM @sql;    #预执行sql

EXECUTE strsql;           #执行sql  

DEALLOCATE PREPARE strsql;   #释放sql

COMMIT;

END;

3.创建每天执行存储的事件

delimiter $$

create event zy.time_partition_event

on schedule every 1 day start date_add(curent()+1,interval 3 hour)

on completion preserve

enable

do

begin

call zy.time_partition_procedure();

end;

4.

#查看是否支持表分区

SHOW VARIABLES LIKE '%partition%'

#查询表的所有分区

SELECT * FROM information_schema.PARTITIONS a WHERE a.table_name IN ('time_partition')ORDER BY partition_ordinal_position DESC;

#新增表分区

ALTER TABLE zy.time_partition ADD PARTITION (PARTITION p20171102 VALUES LESS THAN (TO_DAYS('2017-11-02')));

#删除表的分区

ALTER TABLE zy.time_partition  DROP PARTITION p20171101;