您好,登录后才能下订单哦!
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;
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。