-- 页面浏览记录表 CREATE TABLE BROWSING_RECORD ( ID INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, USER_ID VARCHAR(32) NOT NULL DEFAULT '' COMMENT '请求用户ID', USER_NAME VARCHAR(200) NOT NULL DEFAULT '' COMMENT '请求用户', IP VARCHAR(64) NOT NULL DEFAULT '' COMMENT '请求IP地址', OPERATION_LEVEL INT(1) NOT NULL DEFAULT 0 COMMENT '操作等级0-正常，1-告警，2-危险，默认0', BROWSE_MODULE VARCHAR(4096) NOT NULL DEFAULT '' COMMENT '浏览模块', OPERATION_ACTION VARCHAR(4096) NOT NULL DEFAULT '' COMMENT '操作动作', PAGE_URL VARCHAR(4096) NOT NULL DEFAULT '' COMMENT '请求url', OPERATION_RESULT VARCHAR(4096) NOT NULL DEFAULT '' COMMENT '操作结果', OPERATION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间', REQUEST_CODE VARCHAR(64) NOT NULL DEFAULT '' COMMENT '详情', REMARKS VARCHAR(4096) NOT NULL DEFAULT '' COMMENT '备注', PRIMARY KEY (ID) ) ENGINE=INNODB DEFAULT CHARSET=UTF8; -- 页面信息详细表 CREATE TABLE REQUEST_DETAILS ( REQUEST_CODE VARCHAR(64) NOT NULL DEFAULT '', REQ_DETAILS TEXT NOT NULL DEFAULT '' COMMENT '请求详情', OPERATION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间', RESP_DETAILS TEXT NOT NULL DEFAULT '' COMMENT '请求详情', RESP_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '响应时间', CREATED_TIME TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (REQUEST_CODE) ) ENGINE=INNODB DEFAULT CHARSET=UTF8; -- 唯一索引 ALTER TABLE REQUEST_DETAILS ADD UNIQUE (REQUEST_CODE);

 

-- 数据量很大,旧数据定期归档方案 -- 暂时定手动执行SQL备份时间维度历史数据，清空时间维度源表 -- 1 创建历史表，表结构和索引与旧表一模一样

CREATE TABLE BROWSING_RECORD_HISTORY LIKE BROWSING_RECORD; CREATE TABLE REQUEST_DETAILS_HISTORY LIKE REQUEST_DETAILS; -- 2 新建存储过程，查询30天的数据并归档进新数据库，然后把30天前的旧数据从旧表里删除 DELIMITER $ DROP PROCEDURE IF EXISTS SP_BRH_RDH$ CREATE PROCEDURE SP_BRH_RDH() BEGIN INSERT INTO BROWSING_RECORD_HISTORY SELECT * FROM BROWSING_RECORD WHERE OPERATION_TIME < NOW() - INTERVAL 30 DAY; DELETE FROM BROWSING_RECORD WHERE OPERATION_TIME < NOW() - INTERVAL 30 DAY; INSERT INTO REQUEST_DETAILS_HISTORY SELECT * FROM REQUEST_DETAILS WHERE OPERATION_TIME < NOW() - INTERVAL 30 DAY; DELETE FROM REQUEST_DETAILS WHERE OPERATION_TIME < NOW() - INTERVAL 30 DAY; END $ DELIMITER ; -- 3 创建EVENT，每30天晚上凌晨00：00定时执行上面的存储过程 CREATE EVENT IF NOT EXISTS EVENT_TEMP ON SCHEDULE EVERY 30 DAY ON COMPLETION PRESERVE DO CALL SP_BRH_RDH();