结合前2天的内容,今天会实作资料分区删除&建立event呼叫我们写好的procedure达到我们的需求。
有一个尚未切分区的资料表act,且本身已经有资料在内了,想用该表栏位created_at(建立时间)当做partition key切表,除了切分区还要能达到自动新增分区与删除旧资料分区的动作,方便资料上的维护。
删除x天前历史分区。
概念: 取表分区最小值与今天日期相比,看需要保留几天。
实作流程
DELIMITER ||
DROP PROCEDURE IF EXISTS del_part ||
CREATE PROCEDURE del_part(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64),IN_DELETE INT)
BEGIN
DECLARE n_sysdate date;
DECLARE min_partition_day date;
DECLARE del_partition_name VARCHAR(255) ;
--取最小分区值
select from_unixtime(partition_description) into min_partition_day from information_schema.partitions where table_schema = IN_SCHEMANAME and table_name = IN_TABLENAME AND partition_description < 'MAXVALUE' order by partition_description asc limit 1;
set n_sysdate = sysdate();
--判断要删除天数的参数执行删除分区作业
WHILE min_partition_day <= (n_sysdate - INTERVAL IN_DELETE DAY) DO
SET del_partition_name = date_format(min_partition_day - INTERVAL 1 DAY,'p%Y%m%d');
SET @sql = concat('alter table ', IN_SCHEMANAME, '.', IN_TABLENAME, ' drop partition ', del_partition_name);
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET min_partition_day = min_partition_day + INTERVAL 1 DAY;
END WHILE;
SET @STEP = "STEP: drop partition [ PASS ]";
SELECT @STEP;
END ||
DELIMITER ;
CALL del_part('t7','act',7);
假设我们要删除前7天之前的分区 (这边等於会删除掉p20210924分区)
最後结合全部内容成一个procedure方便呼叫~
DELIMITER ||
DROP PROCEDURE IF EXISTS auto_part ||
CREATE PROCEDURE auto_part(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64),IN_CREATE INT,IN_DELETE INT)
BEGIN
DECLARE isexist_partition varchar(255) default '';
DECLARE old_date VARCHAR(255);
DECLARE new_p_description VARCHAR(255);
DECLARE new_p_name VARCHAR(255);
DECLARE n_sysdate date;
DECLARE max_partition_day date;
DECLARE p_name VARCHAR(255) ;
DECLARE p_description VARCHAR(255) ;
DECLARE min_partition_day date;
DECLARE del_partition_name VARCHAR(255) ;
select partition_name into isexist_partition from information_schema.partitions where table_schema = IN_SCHEMANAME and table_name = IN_TABLENAME limit 1;
IF isexist_partition <=> NULL THEN
SET @p_check = "The table has no partition。 STEP: Add table partition";
SELECT @p_check;
select date(from_unixtime(`created_at`)) into old_date from `act` order by `created_at` asc limit 1 ;
SET old_date = REPLACE(old_date, '-', '');
SET new_p_name = DATE_FORMAT(old_date, 'p%Y%m%d');
SET new_p_description = unix_timestamp(old_date + INTERVAL 1 DAY);
SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`', ' PARTITION BY RANGE (`created_at`) (PARTITION ', new_p_name, ' VALUES LESS THAN (', new_p_description, '),PARTITION pmax VALUES LESS THAN MAXVALUE );');
SELECT @SQL;
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
SET @STEP = "STEP: check partition [ PASS ]";
SELECT @STEP;
set n_sysdate = sysdate();
select from_unixtime(partition_description) into max_partition_day from information_schema.partitions where table_schema = IN_SCHEMANAME and table_name = IN_TABLENAME AND partition_description < 'MAXVALUE' order by partition_description desc limit 1;
WHILE max_partition_day <= (n_sysdate + INTERVAL IN_CREATE DAY) DO
SET p_name = date_format(max_partition_day ,'p%Y%m%d');
SET p_description = unix_timestamp(max_partition_day + INTERVAL 1 DAY);
SET @sql = concat('ALTER TABLE ', IN_SCHEMANAME, '.', IN_TABLENAME, ' REORGANIZE PARTITION `pmax` INTO (partition ', p_name, ' values less than (', p_description, '),PARTITION `pmax` VALUES LESS THAN MAXVALUE )');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET max_partition_day = max_partition_day + INTERVAL 1 DAY;
END WHILE;
SET @STEP = "STEP: add historical date to today [ PASS ]";
SELECT @STEP;
select from_unixtime(partition_description) into min_partition_day from information_schema.partitions where table_schema = IN_SCHEMANAME and table_name = IN_TABLENAME AND partition_description < 'MAXVALUE' order by partition_description asc limit 1;
WHILE min_partition_day <= (n_sysdate - INTERVAL IN_DELETE DAY) DO
SET del_partition_name = date_format(min_partition_day - INTERVAL 1 DAY,'p%Y%m%d');
SET @sql = concat('alter table ', IN_SCHEMANAME, '.', IN_TABLENAME, ' drop partition ', del_partition_name);
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET min_partition_day = min_partition_day + INTERVAL 1 DAY;
END WHILE;
SET @STEP = "STEP: drop partition [ PASS ]";
SELECT @STEP;
END ||
DELIMITER ;
CALL auto_part('t7','act',5,7);
内容:
使用test.act资料表created_at 整数型态栏位当partition key进行分区作业。
预先建立後5天分区。
资料维护上只保留到7天前的资料。
最後有了写好的程序码,要让程序在特定时间固定执行就可以使用mysql Event排成的功能来实现。
如何设定event内容!? MYSQL EVENT用法
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)
--重启後失效
set global event_scheduler = on;
--修改设定档 my.cnf
event_scheduler = 1
DELIMITER ||
CREATE EVENT IF NOT EXISTS `auto_act_event` --建立事件&确认event是否存在,存在则不建立
ON SCHEDULE EVERY 1 day --指定为循环事件(每天执行)
STARTS '2021-10-02 20:00:00' --START / END 用来指定事件有效开始结束时间。ps.这边因为要持续跑所以只设定事件开始执行时间。
ON COMPLETION PRESERVE --避免事件在到期後自动删除
ENABLE
COMMENT 'act_event_partition'
DO BEGIN --执行内容区块
call auto_part('t7','act',5,7);
END||
DELIMITER ;
--查询资料库存在哪些event
SHOW EVENTS FROM db_name;
--查询event建立结构
SHOW CREATE EVENT event_name;
--删除现有的事件
DROP EVENT IF EXISTS event_name;
到这边我们就完整的实现自动切表的维护作业了
>>: [Day28] swift & kotlin 上架篇!(2) 小鸡BB-游戏上架流程-kotlin
电风扇 ( 控制强度 ) 教学原文参考:电风扇 ( 控制强度 ) 这篇文章会介绍,如何在 Scrat...
全文同步於个人 Docusaurus Blog 在本章中,要达成两个目标: 使用 JS 来动态 r...
今天来介绍使用API会用到NSURLSession 其实NSURLSession 与 URLSess...
此篇会延续上一篇客制化 Sass 原始码架构 ,介绍透过 7+1 Pattern,这个设计模式来管...
#odoo #开源系统 #数位赋能 #E化自主 章节介绍 在21世纪的当下,不管是购买商品、找工作,...