mysql中定时删除N天前以时间命名的数据表

74 阅读 0 评论 49 点赞

我是靠谱客的博主拉长溪流，最近开发中收集的这篇文章主要介绍mysql中定时删除N天前以时间命名的数据表，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近在做一个log管理。具体情形是这样的，在mysql中每天产生一个log数据表，要求自动删除N天前的数据表。之前对这方面没什么了解，查了很多资料，都是很抽象的从一个高度上讲了讲。在此，记录一下实现过程，供有需要的童鞋参考。写的可能有点笨拙，欢迎讨论，共同提高~

实现过程中用到了mysql中的存储过程（procedure）和定时器（event）。需要注意的是，mysql从5.1版本才开始支持定时器。

1、查看mysql版本

mysql> select version();

2、查看定时器开关

如果value值为OFF，可以这样打开：

set GLOBAL event_scheduler=1;

想关闭，只需设置为0即可。

3、创建测试用数据表

mysql> create table 2013_04_15
-> (
-> id int(11) not null auto_increment primary key,
-> time datetime not null
-> );

我想删除一年之前的数据表。

4、创建存储过程

mysql> delimiter //

mysql> drop procedure if exists del//

mysql> create procedure del()
-> BEGIN
-> select date_format(DATE_SUB(now(),INTERVAL 1 YEAR),'%Y_%m_%d') into @tf;
-> set @sql = concat('drop table if exists ',@tf);
-> PREPARE stml FROM @sql;
-> EXECUTE stml;
-> END//
mysql> delimiter ;

注释：

第一行delimiter //，设置mysql的定界符，默认是分号，即当以分号结束时，执行输入的mysql命令。但有时不希望这样，故需要重新定义界定符，本例中使用双斜杠，注意再设置回来，上述最后一行sql语句即是。
第二行drop procedure if exists del//，判断是否已经存在名为del的procedure。如果存在就删除。
创建名为del的procedure。BEGIN起始，END结束。
select date_format(DATE_SUB(now(),INTERVAL 1 YEAR),'%Y_%m_%d') into @tf;根据今天的日期，生成一年前的日期串，格式形如2013_04_15，存放在变量tf中。
set @sql = concat('drop table if exists ',@tf);构造删除表的sql语句。
PREPARE stml FROM @sql;EXECUTE stml; 执行sql语句。

5、创建定时器

mysql> drop event if exists del_event;
mysql> create event del_event
-> on schedule every 3 minute（定时器执行的时间间隔，测试中，我使用的3分钟）
-> on completion preserve disable
-> do call del();