需求:今天遇到mysql中的日志表需要设置最大存储量、最多存储天数,多余的删除
存储过程代码如下:
DELIMITER //
DROP PROCEDURE if exists log_system;
CREATE PROCEDURE log_system (in u_n INT,u_d INT,s_n INT,s_d INT)//u_d用户日志天数,u_n用户日志数,s_n系统日志数,s_d系统日志天数
BEGIN
SET u_d = (SELECT userloggingDate FROM logging_prameter); //通过设置表得到用户日志天数
DELETE FROM user_logging
WHERE time <= (select date_add(sysdate(),interval - u_d day) as yestoday);//通过算法算出系统时间前去用户日志天数,得到应该删除的时间点
IF (SELECT COUNT(*) FROM user_logging) > (SELECT userLoggingNumber FROM logging_prameter) THEN
SET u_n = (select COUNT(*) FROM user_logging)-(SELECT userLoggingNumber FROM logging_prameter);
DELETE FROM user_logging ORDER BY time LIMIT u_n;
END IF;
SET s_d = (SELECT sysLoggingDate FROM logging_prameter);
DELETE FROM sys_logging WHERE time <= (select date_add(sysdate(),interval - s_d day) as yestoday);
IF (SELECT COUNT(*) FROM sys_logging) > (SELECT sysLoggingNumber FROM logging_prameter) THEN
SET s_n = (select COUNT(*) FROM sys_logging)-(SELECT sysLoggingNumber FROM logging_prameter);
DELETE FROM sys_logging ORDER BY time LIMIT s_n;
END IF;
END;
//DELIMITER
定时器代码如下:
DELIMITER //
SET GLOBAL event_scheduler = ON;
DROP EVENT IF EXISTS `eventSysUserLogging`;
CREATE EVENT IF NOT EXISTS `eventSysUserLogging`
ON SCHEDULE
EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE,'17:30:00')
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL log_system(@u_n,@u_n,@s_n,@s_d);//调用储存过程
END;
// DELIMITER