MySQL事件event周期执行调用存储过程

事件(event)简介

    事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

    事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

    事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

事件的优缺点
    优点

    一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。

    可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

    缺点
    定时触发，不可以调用。

 业务需求

    每天特定时间将需要入账的账单修改状态为入账并写入入账时间。

 实践实例

    账单表结构如下：

    CREATE TABLE `dz_order_bill` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `bill_no` varchar(20) DEFAULT '' COMMENT '账单编号',
      `bill_title` varchar(30) DEFAULT '' COMMENT '账单标题',
      `bill_type` tinyint(1) DEFAULT '0' COMMENT '账单类目',
      `bill_date` date DEFAULT '0000-00-00' COMMENT '账单日期',
      `bill_amount` decimal(10,2) DEFAULT '0.00' COMMENT '账单金额',
      `bill_status` tinyint(1) DEFAULT '0' COMMENT '账单状态 1 正常 2 已退款',
      `bill_pay_amount` decimal(10,2) DEFAULT '0.00' COMMENT '账单支付金额',
      `bill_discount_amount` decimal(10,2) DEFAULT '0.00' COMMENT '账单优惠金额',
      `bill_pay_status` tinyint(1) DEFAULT '0' COMMENT '账单支付状态 0 未支付 1 已支付',
      `bill_pay_time` int(11) DEFAULT '0' COMMENT '账单支付时间',
      `bill_refund_time` int(11) DEFAULT '0' COMMENT '账单退款时间',
      `relate_table` varchar(30) DEFAULT '' COMMENT '关联数据表',
      `remark` varchar(255) DEFAULT '' COMMENT '账单备注',
      `record_time` int(11) DEFAULT '0' COMMENT '入账时间 0 未入账',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=utf8 COMMENT='租客账单表';

    测试表结构如下：(用作调用测试，比较直观的看到调用结果)

    CREATE TABLE `dz_event_test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `exec_time` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    创建存储过程(procedure)：

    DROP PROCEDURE IF EXISTS p_auto_bill_record;
    CREATE PROCEDURE `p_auto_bill_record`()
    BEGIN
      update dz_order_bill set record_time=UNIX_TIMESTAMP()
      where bill_date = CURDATE() AND bill_status=1 AND bill_pay_status=1 AND record_time=0;
      insert into dz_event_test(exec_time) values(concat(CURDATE(),' ',CURTIME()));
    END;

    创建执行事件(event):

    DROP EVENT IF EXISTS e_call_auto_bill_record;
    CREATE EVENT `e_call_auto_bill_record`
    ON SCHEDULE EVERY 1 MINUTE STARTS '2019-10-10 15:30:30'
    ON COMPLETION PRESERVE ENABLE DO call auto_bill_record();
    // 每天 13:00 执行
    DROP EVENT IF EXISTS e_call_auto_bill_record;
    CREATE EVENT `e_call_auto_bill_record`
    ON SCHEDULE EVERY 1 DAY STARTS date_add(date(ADDDATE(curdate(),1)),interval 13 hour)  
    ON COMPLETION PRESERVE ENABLE DO call p_auto_bill_record();

  注意

  1、查看是否事件已开启

    SHOW VARIABLES LIKE 'event_scheduler';

  2、开启mysql事件

SET GLOBAL event_scheduler = ON; 
（*全局开启，此方法为动态设置数据库重启后将失效，可从配置文件my.cnf设置event_scheduler = 1 #或者ON）