2021-04-13 mysql 定时任务每天 统计数据输出到指定表

# 设置启动event
set GLOBAL event_scheduler = 1;
# 查询是否启用
SHOW VARIABLES LIKE 'event_scheduler';
#删除存储过程
DROP PROCEDURE IF EXISTS daily_DianTiAnZhuang;
#创建新的存储过程
DELIMITER $$
CREATE PROCEDURE daily_DianTiAnZhuang()
BEGIN
insert INTO yxt_statistics (action_name,`key`,`value`)  
select '电梯安装日活' action_name, last_login_date `key`,count(*) `value` from (SELECT user_name,last_login_time,DATE_FORMAT(last_login_time,'%Y-%m-%d') last_login_date FROM yxt_user WHERE last_login_time like concat(curdate() - INTERVAL 1 DAY,"%") and u_work LIKE "%电梯安装%" ) a GROUP by last_login_date;
SELECT * FROM yxt_statistics ORDER BY id desc LIMIT 1 ;
END $$

#删除定时任务
DROP EVENT IF EXISTS test_event;
#创建定时任务
DELIMITER //
CREATE EVENT test_event 
# 执行时间时间 每天 
ON SCHEDULE EVERY 1 DAY
#启动时间 当前时间
STARTS CURDATE() 
# DO  后边需要执行的sql
DO call daily_DianTiAnZhuang();
//