mysql event procedure_Mysql 新建 Event 和 Stored Procedure

Event

MySql的事件调度是在5.1引入的，从而可以将数据的定时操作放在数据库级别，而不是通过其它外部的程序定时执行。

Mysql event 的执行依赖于事件调度器 event_scheduler，查看其是否已开启：

mysql> SHOW VARIABLES LIKE '%event_scheduler%';

-- 若值为 off, 则开启

mysql> SET GLOBAL event_scheduler = "ON";

下面新建 event：

-- create event

DELIMITER $$

DROP EVENT IF EXISTS `event_name`$$

SET character_set_client = utf8;

SET character_set_results = utf8;

SET collation_connection = utf8_general_ci;

-- 从 2017-05 起，每月的第一天执行该 event

CREATE EVENT event_name ON SCHEDULE EVERY '1' MONTH STARTS '2017-05-01 00:00:00'

DO

BEGIN

DECLARE CONTINUE handler FOR SQLEXCEPTION

BEGIN

ROLLBACK;

SET autocommit = 1;

END;

SET autocommit = 0;

START TRANSACTION;

-- do something, for example:

call procedure_name();

COMMIT;

SET autocommit = 1;

END;

$$

DELIMITER ;

Stored Procedure

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

下面新建 stored procedure：

-- create procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_attendance_relevant_data`$$

SET character_set_client = utf8;

SET character_set_results = utf8;

SET collation_connection = utf8_general_ci;

CREATE PROCEDURE `insert_attendance_relevant_data`()

BEGIN

-- sql statement set

END;

$$

DELIMITER ;