需求描述
我们操作日志表随着时间越长,数据量越大;要求日志表按月分表,产生的日志根据当前时间确定要存入的表名中;为了保险起见,提前创建三年的表;
例如表名:sys_oper_log_202301,sys_oper_log_202302。
需求实现
1、创建存储过程:
## 根据表结构定义创建
CREATE DEFINER=`root`@`localhost` PROCEDURE `createOperLogTables`()
BEGIN
DECLARE
var_num INT DEFAULT 0;
WHILE
var_num < 36 DO
SET @sqlstr = CONCAT( "CREATE TABLE IF NOT EXISTS sys_oper_log_", date_format( date_add( curdate(), INTERVAL var_num MONTH ), '%Y%m' ),
"(`oper_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '日志主键',
`title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '模块标题',
`business_type` int DEFAULT '0' COMMENT '业务类型(0其它 1新增 2修改 3删除)',
`method` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '方法名称',
`request_method` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '请求方式',
`operator_type` int DEFAULT '0' COMMENT '操作类别(0其它 1后台用户 2手机端用户)',
`oper_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '操作人员',
`dept_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '部门名称',
`oper_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '请求URL',
`oper_ip` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '主机地址',
`oper_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '操作地点',
`oper_param` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '请求参数',
`json_result` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '返回参数',
`status` int DEFAULT '0' COMMENT '操作状态(0正常 1异常)',
`error_msg` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '错误消息',
`oper_time` datetime DEFAULT NULL COMMENT '操作时间',
`cost_time` bigint DEFAULT '0' COMMENT '消耗时间',
PRIMARY KEY (`oper_id`) USING BTREE,
KEY `idx_sys_oper_log_bt` (`business_type`) USING BTREE,
KEY `idx_sys_oper_log_s` (`status`) USING BTREE,
KEY `idx_sys_oper_log_ot` (`oper_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='操作日志记录';" );
PREPARE stmt
FROM
@sqlstr;
EXECUTE stmt;
SET var_num = var_num + 1;
END WHILE;
END;
## 根据模板表直接创建
CREATE DEFINER = `root` @`localhost` PROCEDURE `createOperLogTables2` () BEGIN
DECLARE
var_num INT DEFAULT 0;
WHILE
var_num < 36 DO
SET @sqlstr = CONCAT( "CREATE TABLE IF NOT EXISTS sys_oper_log_", date_format( date_add( curdate(), INTERVAL var_num MONTH ), '%Y%m' ), " like sys_oper_log" );
PREPARE stmt
FROM
@sqlstr;
EXECUTE stmt;
SET var_num = var_num + 1;
END WHILE;
END;
2、执行之后会在函数中出现 createOperLogTables createOperLogTables2 这个函数。
3、执行 createOperLogTables createOperLogTables2 :
CALL createOperLogTables();
CALL createOperLogTables2();