做一个 定时刷新用户登录记录的中间库
存储过程:
create procedure proc_userlogin()
BEGIN
declare _username varchar(64) default null;
declare _loginTime varchar(64) DEFAULT '1970-01-01 00:00:00';
-- 定义游标遍历时,作为判断是否遍历完全部记录的标记
declare _done interger default 0;
-- 定义游标名字_cur
declare _cur CURSOR FOR
select * from tmp_table; --临时表在下面定义
-- 声明当游标遍历完全部记录后将标志变量设置为某个值
declare CONTINUE HANDLER FOR NOT FOUND SET _done=1;
-- date为传入的参数(这里传入当天curdate())
SET @date = DATE_FORMAT(date,'%Y-%m-%d');
-- 定义是否存在当天记录
SET @count=0;
-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS tmp_table;
-- 定义sql
SET @sqlstr = concat('create TEMPORARY TABLE tmp_table(
select username,loginTime from user
where
date_format(loginTime,''%Y-%m-%d'')=''',@date,'''
)
');
-- 预处理需要执行的动态sql,stmt是变量
PREPARE stmt FROM @sqlstr;
-- 执行sql语句
EXECUTE stmt;
-- 释放预处理段
DEALLOCATE PREPARE stmt;
-- 查看中间库今天是否有记录
SET @sqlselect=concat('select count(1) into @count from test.user
where date_format(loginTime,''%Y-%m-%d'')=''',@date,'''
');
PREPARE stmt FROM @sqlselect;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 今天有记录的话,删除中间库今天的登录记录
IF @count>0 THEN
SET @sqldelete=concat('delete from test.user
where date_format(loginTime,''%Y-%m-%d'')=''',@date,'''
');
PREPARE stmt FROM @sqldelete;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
OPEN _cur; -- 打开游标
-- 循环语句,逐条把新记录插入到中间库
REPEAT
FETCH _cur
INTO _username,
_loginTime ;
INSERT INTO test.user(username,loginTime) values( _username,_loginTime);
-- 提交事务
commit;
UNTIL _done END REPEAT; -- 循环语句结束
-- 关闭游标
CLOSE _cur;
-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS tmp_table;
END
-----------------------------------------------------------------------------
定时事件:
create event user_event
on schedule
EVERY 1 hour
STARTS '2017-09-06 16:00:00'
ON COMPLETION PRESERVE ENABLE
do call proc_userlogin()