MySQL:根据时间规则定时清除匹配数据

MySQL:根据时间规则定时清除匹配数据


一 . 场景

有时,我们可能需要定时清除数据库一些废弃的数据,可以使用mysql的存储过程和事件来完成。

提示:本文不对MySQL的事件Event进行详细介绍,如果想了解详细内容,请查看文章《MySQL:简述MySQL中的事件Event》。

二 . 实现

首先创建Table,添加数据,执行语句如下:

drop table if exists table_timestamp;
create table table_timestamp
(
   message_pk          bigint not null auto_increment,

   message_content     varchar(99),

   /* 创建时间不可为空*/
   CreateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,

   primary key (message_pk)
);

/* 初始化数据 */
insert into table_timestamp(message_content,CreateTime) values('a','2019-01-08 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('b','2019-01-09 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('c','2019-01-10 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('d','2019-01-13 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('e','2019-01-14 12:37:44');

1 . 简单实现:事件
要求:删除三天以前的数据,实现如下:

select * from table_timestamp;

drop event if exists event_timestamp;
create event event_timestamp
    on schedule every 1 minute starts '2019-01-15 10:38:00' 
    on completion not preserve
    enable
    do delete from table_timestamp where (to_days(now()) - to_days(createtime))>=3;  

select * from table_timestamp;

2 . 复杂实现:事件+存储过程
要求:根据传入参数,删除相应的数据,实现如下:

select * from table_timestamp;

drop procedure if exists procedure_timestamp;
delimiter $$
create procedure procedure_timestamp(in date_inter int)
begin
   delete from table_timestamp where (to_days(now()) - to_days(createtime))>=date_inter;  
end$$
delimiter ;

drop event if exists event_procedure_timestamp;
create event event_procedure_timestamp
    on schedule every 1 minute starts '2019-01-15 10:38:00'
    on completion not preserve
    enable
    do call procedure_timestamp(3);

select * from table_timestamp;

三 . 实现样例

drop table if exists table_timestamp;
create table table_timestamp
(
   message_pk          bigint not null auto_increment,

   message_content     varchar(99),

   /* 创建时间不可为空*/
   CreateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,

   primary key (message_pk)
);

/* 初始化数据 */
insert into table_timestamp(message_content,CreateTime) values('a','2019-01-08 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('b','2019-01-09 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('c','2019-01-10 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('d','2019-01-13 12:37:44');
insert into table_timestamp(message_content,CreateTime) values('e','2019-01-14 12:37:44');

/* 事件 */
drop event if exists event_timestamp;
create event event_timestamp
    on schedule every 1 minute starts '2019-01-15 10:38:00' 
    on completion not preserve
    enable
    do delete from table_timestamp where (to_days(now()) - to_days(createtime))>=3;  


/* 事件+存储过程 */
drop procedure if exists procedure_timestamp;
delimiter $$
create procedure procedure_timestamp(in date_inter int)
begin
   delete from table_timestamp where (to_days(now()) - to_days(createtime))>=date_inter;  
end$$
delimiter ;

drop event if exists event_procedure_timestamp;
create event event_procedure_timestamp
    on schedule every 1 minute starts '2019-01-15 10:38:00'
    on completion not preserve
    enable
    do call procedure_timestamp(3);

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页