mysql 每日新增表分区

ycycoco

　　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;