mysql页面浏览记录表设计

最新推荐文章于 2024-02-29 13:35:19 发布

原创最新推荐文章于 2024-02-29 13:35:19 发布 · 2.9k 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

数据库mysql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种数据库操作记录及请求详情表的设计，并提出了针对大量数据的定期归档方案，包括创建归档表、存储过程及定时任务，确保系统的高效运行。

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