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;
