数据库脚本

MySql

需要为表创建分区

CREATE TABLE `operation_log` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `request_date` datetime DEFAULT NULL COMMENT '请求时间',
  `request_id` varchar(64) DEFAULT NULL COMMENT '请求唯一标识',
  `user_id` varchar(40) DEFAULT NULL COMMENT '用户id',
  `username` varchar(128) DEFAULT NULL COMMENT '用户姓名',
  `request_url` varchar(128) DEFAULT NULL COMMENT 'url地址',
  `request_method` varchar(20) DEFAULT NULL COMMENT '请求类型',
  `request_execute_time` decimal(14,4) DEFAULT NULL COMMENT '执行时间，单位毫秒',
  `request_ip` varchar(64) DEFAULT NULL COMMENT '请求ip',
  `error_no` int(11) DEFAULT NULL COMMENT '错误码',
  `error_info` varchar(255) DEFAULT NULL COMMENT '错误消息',
  `server_name` varchar(64) DEFAULT NULL COMMENT '服务名称',
  `server_ip` varchar(64) DEFAULT NULL COMMENT '服务ip',
  `server_prot` int(11) DEFAULT NULL COMMENT '服务端口',
  `error_status` tinyint(2) DEFAULT '0' COMMENT '系统异常状态，1 是，0 否',
  `alarm_send_status` tinyint(2) DEFAULT '0' COMMENT '报警发送状态，1 已发送，0 未发送',
  `author` varchar(128) DEFAULT NULL COMMENT '作者',
  `request_name` varchar(255) DEFAULT NULL COMMENT '接口名称',
  PRIMARY KEY (`log_id`) USING BTREE,
  KEY `user_id` (`user_id`) USING BTREE,
  KEY `request_execute_time` (`request_execute_time`) USING BTREE,
  KEY `error_no` (`error_no`) USING BTREE,
  KEY `request_id` (`request_id`) USING BTREE,
  KEY `request_date` (`request_date`) USING BTREE,
  KEY `server_name` (`server_name`) USING BTREE,
  KEY `request_name` (`request_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ;


CREATE TABLE `operation_log_detail` (
  `log_id` bigint(20) NOT NULL,
  `request_date` datetime NOT NULL COMMENT '请求时间',
  `request_param` longtext COMMENT '请求参数',
  `error_stack_trace` longtext COMMENT '异常堆栈信息',
  `content` longtext,
  PRIMARY KEY (`log_id`) USING BTREE,
  KEY `request_date` (`request_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

TimescaleDB

CREATE TABLE operation_log (
  log_id BIGSERIAL ,
	request_date TIMESTAMPTZ NOT NULL,
	request_id VARCHAR ( 64 )  NULL,
	user_id VARCHAR ( 40 )  NULL,
	username VARCHAR ( 128 )  NULL,
	request_url VARCHAR ( 128 )  NULL,
	request_method VARCHAR (20 )  NULL,
	request_execute_time decimal(10,4)  NULL,
	request_ip VARCHAR (64 )  NULL,
	error_no int4  NULL,
	error_info VARCHAR (255)  NULL,
	server_name VARCHAR (64 )  NULL,
	server_ip VARCHAR (64 )  NULL,
	server_prot int4  NULL,
	error_status int2 DEFAULT 0 ,
	alarm_send_status int2 DEFAULT 0 ,
	author VARCHAR ( 128 )  NULL,
	request_name VARCHAR ( 255 )  NULL
);

SELECT
	create_hypertable ( 'operation_log', by_range ( 'request_date', INTERVAL '1 day' ) );
CREATE UNIQUE INDEX idx_log_id ON operation_log ( log_id, request_date);
CREATE INDEX idx_request_id ON operation_log ( request_id );
CREATE INDEX idx_user_id ON operation_log ( user_id);
CREATE INDEX idx_request_url ON operation_log ( request_url);

CREATE INDEX idx_ip ON operation_log ( request_ip);

CREATE TABLE operation_log_detail (
  log_id int8 ,
	request_date TIMESTAMPTZ NOT NULL,
	request_param text  NULL,
	error_stack_trace text  NULL,
	content text  NULL
);

SELECT
	create_hypertable ( 'operation_log_detail', by_range ( 'request_date', INTERVAL '1 day' ) );
CREATE UNIQUE INDEX idx_log_id ON operation_log ( log_id, request_date);