mysql定时执行存储过程demo

将事件计划开启

SETGLOBAL event_scheduler = 1;

开启事件任务

ALTEREVENT eventName ONCOMPLETION PRESERVE ENABLE;




 

delimiter //   设定语句终结符为 //，因存储过程语句用;结束

 

==========创建数据库表========

  DROPTABLE IF EXISTS test;
  CREATETABLE test (

	id bigint(11) unsigned NOTNULL AUTO_INCREMENT,

	name varchar(100)NOTNULL DEFAULT '',

	create_time  datetime,
  PRIMARYKEY (ID)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULTCHARSET=utf8;  

===========定义存储过程 将 stored_procedure==========

  DELIMITER //
  DROPP ROCEDURE IF EXISTS stored_procedure//
  CREATEPROCEDURE stored_procedure()
  BEGIN
  INSERTINTO test(name, create_time) values('testName', now());
  END//




==============设置定时器 timer================

  DROPEVENT IF EXISTS timer//
  CREATEEVENT timer
  ONSCHEDULE EVERY 10 secondSTARTS TIMESTAMP'2017-04-12 17:40:00' #每隔10秒将执行存储过程p_test
  ONCOMPLETION PRESERVE
  DO
  BEGIN
  CALL stored_procedure();
  END//







======================================================================

相关的命令


        查看event是否开启 : SHOW VARIABLES LIKE'%event_sche%';

        将事件计划开启 : SETGLOBAL event_scheduler = 1;

        将事件计划关闭 : SETGLOBAL event_scheduler = 0;

        关闭事件任务 : ALTEREVENT eventName ONCOMPLETION PRESERVE DISABLE; 

        开启事件任务 : ALTEREVENT eventName ONCOMPLETION PRESERVE ENABLE;

        查看事件任务 : SHOW EVENTS ;







 



将事件计划开启