-- 页面浏览记录表
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();