存储过程自动删除行MYSQL中7天以上 [英] Store Procedure that Automatically delete rows older than 7 days in MYSQL
问题描述
我想知道是否有可能创建一个存储过程，可以自动，每天00：00，每删除表是7天以上的每一行。
我已经看到一些解决方案，但如果我所期待的不知道，如果有人有什么很好的例子将是很好。我知道这可能与Python和PHP简单的脚本来完成，但我想用的东西更MYSQL自动化。
任何帮助将是非常美联社preciate。
谢谢！
MySQL有关系，以避免复杂的cron互动很多东西，你是调度SQL时，有关其事件的功能，和更少的文件。请参阅手册页 rel=\"nofollow\">。希望下面的内容中的重要步骤，需要考虑的事情的简要概述，和可核查的测试了。
显示变量，其中变量名='event_scheduler';
+ ----------------- + ------- +
| VARIABLE_NAME |值|
+ ----------------- + ------- +
| event_scheduler | OFF |
+ ----------------- + ------- +
哎呀，事件调度器未开启。没有什么会触发。
SET GLOBAL event_scheduler = ON; - 把她并确认以下
显示变量，其中变量名='event_scheduler';
+ ----------------- + ------- +
| VARIABLE_NAME |值|
+ ----------------- + ------- +
| event_scheduler | ON |
+ ----------------- + ------- +
模式测试
创建表theMessages
（ID INT AUTO_INCREMENT主键，
用户id int不空，
消息VARCHAR（255）NOT NULL，
updateDt日期时间不为空，
键（updateDt）
- FK不是显示
）;
- 它是目前2015年9月10日13时12分00秒
- 将truncate table theMessages;
插入theMessages（用户ID信息，updateDt）值（1，我现在需要去，没有后续问题，2015年8月24日11时10分09秒'）;
插入theMessages（用户ID信息，updateDt）值（7，'你总是这么说...只是隐藏，2015年8月29日'）;
插入theMessages（用户ID信息，updateDt）VALUES（1，'7天测试1，2015年9月3日12:00:00'）;
插入theMessages（用户ID信息，updateDt）VALUES（1，'7天test2的，2015年9月3日14:00:00'）;
创建2个事件，1日每天运行，2号每10分钟开
忽略他们实际上做什么（角色和另外一个）。问题的关键是在时差办法和计划
DELIMITER $$
CREATE EVENT`delete7DayOldMessages`
ON SCHEDULE每1天STARTS2015年9月1日00:00:00
在完成preSERVE
DO BEGIN
从theMessages删除
其中，DATEDIFF（NOW（），updateDt）→6; - 并不十分确切，但昨天与LT; 24小时仍1天
- 等等等等所有的东西在这里
END; $$
DELIMITER;
...
DELIMITER $$
CREATE EVENT`Every_10_Minutes_Cleanup`
ON SCHEDULE EVERY 10 MINUTE STARTS2015年9月1日00:00:00
在完成preSERVE
DO BEGIN
从theMessages删除
其中，TIMESTAMPDIFF（小时，updateDt，现在（））＆GT; 168; - 在1周龄（168小时）的消息
- 等等等等所有的东西在这里
END; $$
DELIMITER;
显示事件状态（不同的方法）
从so_gibberish展示活动; - 列出所有模式名事件（数据库名）
演出活动; - ＆LT; ---------从工作台/ SQLyog的
展会活动\\ G组;` - ＆LT; ---------我喜欢这个从MySQL＆GT;提示*************************** 1.排******************** *******
DB：so_gibberish
名称：delete7DayOldMessages
定义者：根@本地
时区：SYSTEM
类型：定期
在执行：NULL
间隔值：1
间隔字段：DAY
开始时间：2015年9月1日00:00:00
结束时间：NULL
状态：已启用
发起人：1
character_set_client字符：UTF8
collation_connection是：utf8_general_ci
数据库排序规则：utf8_general_ci
*************************** 2.排******************** *******
DB：so_gibberish
名称：Every_10_Minutes_Cleanup
定义者：根@本地
时区：SYSTEM
类型：定期
在执行：NULL
间隔值：10
间隔字段：MINUTE
开始时间：2015年9月1日00:00:00
结束时间：NULL
状态：已启用
发起人：1
character_set_client字符：UTF8
collation_connection是：utf8_general_ci
数据库排序规则：utf8_general_ci
2行中集（0.06秒）
随机的东西要考虑
下降事件someEventName; - ＆LT; -----一件好事了解
不能别名DATEDIFF并使用WHERE子句中1线，让
从theMessages选择ID，DATEDIFF（NOW（），updateDt）其中DATEDIFF（NOW（），updateDt）→6;
得到更多确切168小时服1周龄
选择ID，TIMESTAMPDIFF（HOUR，updateDt，现在（））为'difference` FROM theMessages;
+ ---- + ------------ +
| ID |差|
+ ---- + ------------ +
| 1 | 410 |
| 2 | 301 |
| 3 | 169 |
| 4 | 167 |
+ ---- + ------------ +
该手册的链接显示相当多的与间隔的选择，如下图所示的flexibilty的：
时间：
{数量年|季度|本月| DAY | HOUR | MINUTE |
WEEK |第二| YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
并发
嵌入必要的多个事件（或相同的事件的多个点火）不会导致数据横行任何并发的措施。
设置和忘记
记住，现在，因为你会忘记它，这些事件只是不停地射击。因此，建立在坚实的code，这将只是保持运行状态，即使当你忘记。你极有可能会。
您的特殊要求
您需要确定哪些行需要由表先删除，这样它供奉主键约束。只需通过CREATE EVENT语句，这可能是巨大的疙瘩他们都在正确的顺序明显的区域内。
I would like to know if is possible to create a Store Procedure that automatically, every day at 00:00,delete every row of every table that is over 7 days. I have seen few solutions but not sure if its what I am looking for, and would be nice if someone has any good example. I know this could be done with simple scripts in python and php, but I would like something more automated by MYSQL.
Any help would be really appreciate.
Thanks!
解决方案Mysql has its EVENT functionality for avoiding complicated cron interactions when much of what you are scheduling is sql related, and less file related. See the Manual page here. Hopefully the below reads as a quick overview of the important steps and things to consider, and verifiable testing too.
show variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ooops, the event scheduler is not turned on. Nothing will trigger.
SET GLOBAL event_scheduler = ON; -- turn her on and confirm belowshow variables where variable_name='event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+
Schema for testing
create table theMessages ( id int auto_increment primary key, userId int not null, message varchar(255) not null, updateDt datetime not null, key(updateDt) -- FK's not shown ); -- it is currently 2015-09-10 13:12:00 -- truncate table theMessages; insert theMessages(userId,message,updateDt) values (1,'I need to go now, no followup questions','2015-08-24 11:10:09'); insert theMessages(userId,message,updateDt) values (7,'You always say that ... just hiding','2015-08-29'); insert theMessages(userId,message,updateDt) values (1,'7 day test1','2015-09-03 12:00:00'); insert theMessages(userId,message,updateDt) values (1,'7 day test2','2015-09-03 14:00:00');
Create 2 events, 1st runs daily, 2nd runs every 10 minutes
Ignore what they are actually doing (playing against one another). The point is on
time differenceapproaches and scheduling.DELIMITER $$ CREATE EVENT `delete7DayOldMessages` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-01 00:00:00' ON COMPLETION PRESERVE DO BEGIN delete from theMessages where datediff(now(),updateDt)>6; -- not terribly exact, yesterday but <24hrs is still 1 day -- etc etc all your stuff in here END;$$ DELIMITER ;...
DELIMITER $$ CREATE EVENT `Every_10_Minutes_Cleanup` ON SCHEDULE EVERY 10 MINUTE STARTS '2015-09-01 00:00:00' ON COMPLETION PRESERVE DO BEGIN delete from theMessages where TIMESTAMPDIFF(HOUR, updateDt, now())>168; -- messages over 1 week old (168 hours) -- etc etc all your stuff in here END;$$ DELIMITER ;
Show event statuses (different approaches)
show events from so_gibberish; -- list all events by schema name (db name) show events; -- <--------- from workbench / sqlyog show events\G;` -- <--------- I like this one from mysql> prompt *************************** 1. row *************************** Db: so_gibberish Name: delete7DayOldMessages Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 1 Interval field: DAY Starts: 2015-09-01 00:00:00 Ends: NULL Status: ENABLED Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 2. row *************************** Db: so_gibberish Name: Every_10_Minutes_Cleanup Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 10 Interval field: MINUTE Starts: 2015-09-01 00:00:00 Ends: NULL Status: ENABLED Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 2 rows in set (0.06 sec)
Random stuff to consider
drop event someEventName;-- <----- a good thing to know aboutcan't alias datediff and use in where clause in 1 line, so
select id,DATEDIFF(now(),updateDt) from theMessages where datediff(now(),updateDt)>6;get more exact, 168 hours for 1 week old
select id,TIMESTAMPDIFF(HOUR, updateDt, now()) as `difference` FROM theMessages; +----+------------+ | id | difference | +----+------------+ | 1 | 410 | | 2 | 301 | | 3 | 169 | | 4 | 167 | +----+------------+The link to the Manual Page shows quite a bit of flexibilty with interval choices, shown below:
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Concurrency
Embed any concurrency measures necessary that multiple events (or multiple firings of the same event) don't cause data to run amok.
Set and Forget
Remember, for now, because you are going to forget it, that these events just keep firing. So build in solid code that will just keep running, even when you forget. Which you most likely will.
Your particular requirements
You need to determine which rows need to be deleted first by table, such that it honors Primary Key constraints. Just lump them all in proper order inside of the obvious area via the CREATE EVENT statement, which can be massive.
这篇关于存储过程自动删除行MYSQL中7天以上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
