{"id":770,"date":"2018-04-13T14:18:07","date_gmt":"2018-04-13T06:18:07","guid":{"rendered":"https:\/\/blog.jsjs.org\/?p=770"},"modified":"2018-04-13T14:18:07","modified_gmt":"2018-04-13T06:18:07","slug":"mysql%e6%95%b0%e6%8d%ae%e5%ba%93-event-%e5%ae%9a%e6%97%b6%e6%89%a7%e8%a1%8c%e4%bb%bb%e5%8a%a1","status":"publish","type":"post","link":"https:\/\/blog.jsjs.org\/?p=770","title":{"rendered":"MySQL\u6570\u636e\u5e93 Event \u5b9a\u65f6\u6267\u884c\u4efb\u52a1."},"content":{"rendered":"<h3>\u4e00\u3001\u80cc\u666f<\/h3>\n<p>\u7531\u4e8e\u9879\u76ee\u7684\u4e1a\u52a1\u662f\u4e0d\u65ad\u5f80\u524d\u8dd1\u7684\uff0c\u6240\u4ee5\u96be\u514d\u6570\u636e\u5e93\u7684\u8868\u7684\u91cf\u4f1a\u8d8a\u6765\u8d8a\u5e9e\u5927\uff0c\u4e0d\u65ad\u7684\u6324\u5360\u786c\u76d8\u7a7a\u95f4\u3002\u5373\u4f7f\u518d\u5927\u7684\u7a7a\u95f4\u4e5f\u652f\u6491\u4e0d\u8d77\u4e1a\u52a1\u7684\u589e\u957f\uff0c\u6240\u4ee5\u5b9a\u671f\u5220\u9664\u4e0d\u5fc5\u8981\u7684\u6570\u636e\u662f\u5f88\u6709\u5fc5\u8981\u7684\u3002\u5728\u6211\u4eec\u9879\u76ee\u4e2d\u7531\u4e8e\u4e0d\u6e05\u7406\u6570\u636e\uff0c\u4e00\u4e2a\u8868\u5360\u7684\u7a7a\u95f4\u7adf\u7136\u8fbe\u5230\u4e864G\u4e4b\u591a\u3002\u60f3\u60f3\u6709\u591a\u53ef\u6015&#8230;<\/p>\n<p>\u8fd9\u91cc\u4ecb\u7ecd\u7684\u662f\u7528MySQL \u5efa\u7acb\u4e00\u4e2a\u5b9a\u65f6\u5668Event\uff0c\u5b9a\u671f\u6e05\u9664\u6389\u4e4b\u524d\u7684\u4e0d\u5fc5\u8981\u4e8b\u4ef6\u3002<\/p>\n<h3>\u4e8c\u3001\u5185\u5bb9<\/h3>\n<div class=\"cnblogs_code\">\n<div class=\"cnblogs_code_toolbar\"><span class=\"cnblogs_code_copy\"><a title=\"\u590d\u5236\u4ee3\u7801\"><img decoding=\"async\" src=\"http:\/\/common.cnblogs.com\/images\/copycode.gif\" alt=\"\u590d\u5236\u4ee3\u7801\" \/><\/a><\/span><\/div>\n<pre>#1\u3001\u5efa\u7acb\u5b58\u50a8\u8fc7\u7a0b\u4f9b\u4e8b\u4ef6\u8c03\u7528\ndelimiter\/\/\ndrop procedure if exists middle_proce\/\/\ncreate procedure middle_proce()\nbegin\nDELETE FROM jg_bj_comit_log WHERE comit_time &lt; SUBDATE(NOW(),INTERVAL 2 MONTH);\noptimize table jg_bj_comit_log;\nDELETE FROM jg_bj_order_create WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_order_create;\nDELETE FROM jg_bj_order_match WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_order_match;\nDELETE FROM jg_bj_order_cancel WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_order_cancel;\nDELETE FROM jg_bj_operate_arrive WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_operate_arrive;\nDELETE FROM jg_bj_operate_depart WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_operate_depart;\nDELETE FROM jg_bj_operate_login WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_operate_login;\nDELETE FROM jg_bj_operate_logout WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_operate_logout;\nDELETE FROM jg_bj_operate_pay WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_operate_pay;\nDELETE FROM jg_bj_position_driver WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_position_driver;\nDELETE FROM jg_bj_position_vehicle WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_position_vehicle;\nDELETE FROM jg_bj_rated_passenger WHERE created_on &lt; SUBDATE(NOW(),INTERVAL 3 MONTH);\noptimize table jg_bj_rated_passenger;\nend\/\/\ndelimiter;\n\n#2\u3001\u5f00\u542fevent(\u8981\u4f7f\u5b9a\u65f6\u8d77\u4f5c\u7528\uff0cMySQL\u7684\u5e38\u91cfGlOBAL event_schduleer \u5fc5\u987b\u4e3aon \u6216\u80051)\nshow variables like 'event_scheduler'\nset global event_scheduler='on'\n\n#3\u3001\u521b\u5efaEvnet\u4e8b\u4ef6\ndrop event if exists middle_event;\ncreate event middle_event\non schedule every 1 DAY STARTS '2017-12-05 00:00:01'\non completion preserve ENABLE\ndo call middle_proce();\n\n#4\u3001\u5f00\u542fEvent \u4e8b\u4ef6\nalter event middle_event on completion preserve enable;\n\n#5\u3001\u5173\u95edEvent \u4e8b\u4ef6\nalter event middle_event on completion preserve disable;<\/pre>\n<div class=\"cnblogs_code_toolbar\"><span class=\"cnblogs_code_copy\"><a title=\"\u590d\u5236\u4ee3\u7801\"><img decoding=\"async\" src=\"http:\/\/common.cnblogs.com\/images\/copycode.gif\" alt=\"\u590d\u5236\u4ee3\u7801\" \/><\/a><\/span><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u80cc\u666f \u7531\u4e8e\u9879\u76ee\u7684\u4e1a\u52a1\u662f\u4e0d\u65ad\u5f80\u524d\u8dd1\u7684\uff0c\u6240\u4ee5\u96be\u514d\u6570\u636e\u5e93\u7684\u8868\u7684\u91cf\u4f1a\u8d8a\u6765\u8d8a\u5e9e\u5927\uff0c\u4e0d\u65ad\u7684\u6324\u5360\u786c\u76d8\u7a7a\u95f4\u3002\u5373\u4f7f\u518d\u5927\u7684\u7a7a\u95f4\u4e5f\u652f\u6491\u4e0d\u8d77\u4e1a\u52a1\u7684\u589e\u957f\uff0c\u6240\u4ee5\u5b9a\u671f\u5220\u9664\u4e0d\u5fc5\u8981\u7684\u6570\u636e\u662f\u5f88\u6709\u5fc5\u8981\u7684\u3002\u5728\u6211\u4eec\u9879\u76ee\u4e2d\u7531\u4e8e\u4e0d\u6e05\u7406\u6570\u636e\uff0c\u4e00\u4e2a\u8868\u5360\u7684\u7a7a\u95f4\u7adf\u7136\u8fbe\u5230\u4e864G\u4e4b\u591a\u3002\u60f3\u60f3\u6709\u591a\u53ef\u6015&#8230; \u8fd9\u91cc\u4ecb\u7ecd\u7684\u662f\u7528MySQL \u5efa\u7acb\u4e00\u4e2a\u5b9a\u65f6\u5668Event\uff0c\u5b9a\u671f\u6e05\u9664\u6389\u4e4b\u524d\u7684\u4e0d\u5fc5\u8981\u4e8b\u4ef6\u3002 \u4e8c\u3001\u5185\u5bb9 #1\u3001\u5efa\u7acb\u5b58\u50a8\u8fc7\u7a0b\u4f9b\u4e8b\u4ef6\u8c03\u7528 delimiter\/\/ drop procedure if exists middle_proce\/\/ create procedure middle_proce() begin DELETE FROM jg_bj_comit_log WHERE [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-770","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/posts\/770","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=770"}],"version-history":[{"count":0,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/posts\/770\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}