这两天一直遇见mysql计划任务的案例,今天我就给大家分享一个真是的实例:
1、创建计划任务的语法:
create event 任务名称 on schedule at 时间周期 starts ‘年-月-日 时-分-秒‘ do sql语句
或者
create event 任务名称 on schedule every 时间周期 starts ‘年-月-日 时-分-秒‘ do sql语句
at 是在设置的时间中执行这条语句就不再执行了,而every 是一个循环的操作,比如我的时间设置的是’2015-05-08 17:55:00’,
at语句就是在这个2015-05-08 17:55:00时间点上执行,
而every 是在你设定的时间周期内重复执行这条语句。
2、查看计划任务执行状态:
SELECT * FROM information_schema.EVENTS; 我执行后的结果:
def test e_test root@localhost SYSTEM SQL TRUNCATE TABLE a RECURRING 1 DAY STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 2015-05-06 13:15:12 ENABLED NOT PRESERVE 2015-05-06 13:13:28 2015-05-06 13:13:28 2015-05-08 13:15:12 0 utf8 utf8_general_ci latin1_swedish_ci
defmysqle_testroot@localhostSYSTEMSQLUPDATE users SET name=1300001 WHERE id=529RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-06 13:53:00ENABLEDNOT PRESERVE2015-05-06 13:52:122015-05-06 13:52:122015-05-08 13:53:000utf8utf8_general_cilatin1_swedish_ci
defowne_testroot@localhostSYSTEMSQLUPDATE emp SET city=4444444 WHERE id=8888RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-06 15:26:00ENABLEDNOT PRESERVE2015-05-07 15:03:382015-05-07 15:03:382015-05-08 15:26:000utf8utf8_general_cilatin1_swedish_ci
defowne_updateroot@localhostSYSTEMSQLUPDATE user SET score=0 WHERE isvalid=1RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-06 15:00:00ENABLEDNOT PRESERVE2015-05-07 15:03:382015-05-07 15:03:382015-05-08 15:00:000utf8utf8_general_cilatin1_swedish_ci
defmysqlusers_score_updateroot@localhostSYSTEMSQLUPDATE users as a LEFT JOIN (select user_id,sum(score) as score from scorerecords
where isvalid=1 and convert(createtime,date) = CURDATE()
and score >0 group by user_id) as b on a.id = b.user_id
set a.dailyscore = case when b.score is null then 0 else b.score end
where a.isvalid =1RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-06 15:00:00ENABLEDNOT PRESERVE2015-05-07 12:01:432015-05-07 12:01:432015-05-08 15:00:000utf8utf8_general_cilatin1_swedish_ci
defceshiw_updateroot@%SYSTEMSQLUPDATE wuceshi SET name =’ddd’ WHERE id=1RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-08 14:03:00ENABLEDNOT PRESERVE2015-05-08 14:02:562015-05-08 14:02:562015-05-08 14:03:000utf8utf8_general_cilatin1_swedish_ci
defdamayiusers_score_updateroot@%SYSTEMSQLUPDATE users as a LEFT JOIN (select user_id,sum(score) as score from scorerecords
where isvalid=1 and convert(createtime,date) = CURDATE()
and score >0 group by user_id) as b on a.id = b.user_id
set a.dailyscore = case when b.score is null then 0 else b.score end
where a.isvalid =1RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-08 15:26:00ENABLEDNOT PRESERVE2015-05-08 15:24:522015-05-08 15:24:522015-05-08 15:26:000utf8utf8_general_cilatin1_swedish_ci
defdamayie_test_insertroot@%SYSTEMSQLINSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP)RECURRING1SECONDSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-08 15:04:17ENABLEDNOT PRESERVE2015-05-08 15:04:172015-05-08 15:04:172015-05-08 18:02:400utf8utf8_general_cilatin1_swedish_ci
defmayidaxiangcsusers_score_updateroot@%SYSTEMSQLUPDATE users as a LEFT JOIN (select user_id,sum(score) as score from scorerecords
where isvalid=1 and convert(createtime,date) = CURDATE()
and score >0 group by user_id) as b on a.id = b.user_id
set a.dailyscore = case when b.score is null then 0 else b.score end
where a.isvalid =1RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-06 00:00:00ENABLEDNOT PRESERVE2015-05-08 15:47:212015-05-08 15:47:210utf8utf8_general_cilatin1_swedish_ci
defceshieventusers_score_updateroot@%SYSTEMSQLUPDATE users as a LEFT JOIN (select user_id,sum(score) as score from scorerecords
where isvalid=1 and convert(createtime,date) = CURDATE()
and score >0 group by user_id) as b on a.id = b.user_id
set a.dailyscore = case when b.score is null then 0 else b.score end
where a.isvalid =1RECURRING1DAYSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-06 00:00:00ENABLEDNOT PRESERVE2015-05-08 16:57:362015-05-08 16:57:360utf8utf8_general_cilatin1_swedish_ci
defdamayidaxiangusers_score_updateroot@%SYSTEMSQLUPDATE users as a LEFT JOIN (select user_id,sum(score) as score from scorerecords
where isvalid=1 and convert(createtime,date) = CURDATE()
and score >0 group by user_id) as b on a.id = b.user_id
set a.dailyscore = case when b.score is null then 0 else b.score end
where a.isvalid =1RECURRING5MINUTESTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION2015-05-08 17:55:00ENABLEDNOT PRESERVE2015-05-08 17:56:212015-05-08 17:56:212015-05-08 18:00:000utf8utf8_general_cilatin1_swedish_ci