因数据库中历史登录信息过多,造成数据库负载过大,需只保留6小时内的登录信息,
我在这里是根据登录历史表中的一个创建时间字段和数据库当前时间进行比较,得出登录存在的时间。使用timstampdiff函数可以解决该问题。
具体sql如下:
-- 开启event_scheduler sql指令
SHOW VARIABLES LIKE '%event_scheduler%';
SET GLOBAL event_scheduler = ON;
-- 定义存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS delete_login_token $$
CREATE PROCEDURE delete_login_token()
BEGIN
IF EXISTS(SELECT id FROM sys_token_his WHERE TIMESTAMPDIFF(hour , EFF_TIME, NOW()) > 6) THEN
-- DELETE
-- from sys_token_his
-- where id in
-- (select id from (SELECT id FROM sys_token_his WHERE TIMESTAMPDIFF(hour, EFF_TIME, NOW()) >= 10) a);
delete from sys_token_his where WHERE TIMESTAMPDIFF(hour , EFF_TIME, NOW()) > 6;
END IF;
END
$$
DELIMITER ;
-- 创建定时器,每间隔10分钟调用一次存储过程
DROP EVENT IF EXISTS event_remind_status;
CREATE EVENT event_remind_status
ON SCHEDULE EVERY 10 minute DO
BEGIN
CALL delete_login_token();
END;
-- 启动定时器
ALTER EVENT event_remind_status ON COMPLETION PRESERVE ENABLE;
# 关闭定时器
ALTER EVENT event_remind_status ON COMPLETION PRESERVE DISABLE ;
# 查看事件
show events ;
# 查看事件创建语句
show create event event_remind_status;