MySQL使用事务定时调用存储过程

最近工作临时接到需求要存储每一天某个特定时点的用户数据，但是代码里面没有这个功能，要得急而且又没有现成的代码。
要解决这个问题，当时想了两个方案

1. 通过代码的定时任务去执行，定时任务定好时间，做一个循环把数据查出来，然后写入数据库。
2. 使用MySQL的事件和存储过程。

考虑到代码实现还要重新部署发版比较麻烦，就使用第二种方法。
实现过程如下：

1. 查看是否可以用事件

# 查看是否可以用事件
	show variables like 'event_scheduler';

OFF 是未开启事务，需要打开事件

# 开启事件
	set global event_scheduler=1;

2. 创建定时任务事件

# 创建定时任务事件
    DROP EVENT IF EXISTS event_data;
    CREATE EVENT IF NOT EXISTS event_data 
    ON SCHEDULE EVERY 24 HOUR STARTS '2019-03-01 00:00:00' 
    ON COMPLETION PRESERVE 
    DO CALL pr_data_caiwu();

表示从2019-03-01 00:00:00开始，每24小时调用一次pr_data_caiwu这个存储过程。

3. 建表，用用于存储数据

    # 建表1
    CREATE TABLE `t_data_usableSum` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    	`reportdate` varchar(50) DEFAULT NULL COMMENT '日期',
      `addtime` datetime DEFAULT NULL COMMENT '记录时间',
      `KYusableSum` varchar(50) DEFAULT NULL COMMENT '用户可用余额',
      `DJfreezeSum` varchar(50) DEFAULT NULL COMMENT '冻结金额',
      `BZJusableSum` varchar(50) DEFAULT NULL COMMENT '保证金余额',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='财务—0点余额';
    
     # 建表2
    DROP TABLE IF EXISTS t_user_caiwu;
    CREATE TABLE `t_user_caiwu` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    		`userid` bigint(20) DEFAULT NULL COMMENT '用户id',
    		`username` varchar(45) DEFAULT NULL COMMENT '用户名',
    	`usableSum` decimal(18,2) DEFAULT NULL COMMENT '用户余额',
      `freezeSum` decimal(18,2) DEFAULT NULL COMMENT '冻结余额',
     `addtime` datetime DEFAULT NULL COMMENT '记录时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户—0点余额-明细';

4. 创建存储过程pr_data_caiwu

# 创建存储过程
DROP procedure if exists pr_data_caiwu;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `pr_data_caiwu`()
BEGIN
   DECLARE reportdate VARCHAR(50) DEFAULT NULL;# 日期
   DECLARE KYusableSum decimal(18,4)	default	0;# 用户可用余额
   DECLARE DJfreezeSum decimal(18,4)	default	0;# 冻结金额
   DECLARE BZJusableSum decimal(18,4)	default	0;# 保证金余额
   
   DECLARE MXuserid BIGINT default	0;# 明细用户id
   DECLARE MXusableSum decimal(18,2)	default	0;# 明细可用资金
   DECLARE MXfreezeSum decimal(18,2)	default	0;# 明细冻结金额
   DECLARE Mxusername VARCHAR(45)	default	NULL;# 明细用户名
   -- 声明游标
   DECLARE _done INT DEFAULT 0; 
   DECLARE f_cursor_name CURSOR FOR
   SELECT id,username,usableSum,freezeSum from t_user WHERE usableSum>0 OR freezeSum>0;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1; 
   -- 取数逻辑
   SELECT SUM(usableSum),SUM(freezeSum) INTO KYusableSum,DJfreezeSum FROM t_user  WHERE userRole!='GUARANTEECORP';
   SELECT SUM(usableSum) INTO BZJusableSum FROM t_user  WHERE userRole='GUARANTEECORP';
   SET reportdate =DATE_FORMAT(NOW(),'%Y-%m-%d');
   
   INSERT INTO t_data_usableSum (`reportdate`, `addtime`, `KYusableSum`, `DJfreezeSum`, `BZJusableSum`) 
   VALUES (reportdate, NOW(), KYusableSum, DJfreezeSum, BZJusableSum);
   -- 打开游标
   OPEN f_cursor_name;
   out_loop: LOOP
   	FETCH f_cursor_name INTO MXuserid,MXusername,MXusableSum,MXfreezeSum; 
   	IF _done = 1 THEN LEAVE out_loop; END IF;
   	IF _done = 0 THEN INSERT INTO t_user_caiwu(userid,username, usableSum,freezeSum,addtime) VALUES(MXuserid,MXusername,MXusableSum,MXfreezeSum,NOW());  END IF;
   END LOOP out_loop;
CLOSE f_cursor_name;
END;;
DELIMITER ;

完成。

注意点：

1. MySQL不能重启，如果MySQL重启了，事件需要重新打开。
2. 如果数据量比较大，存储过程执行insert的时候比较耗时。