MicroCommunity/java110-db/db/LogService/create_table.db

-- 日志表

create table l_transaction_log(
    log_id varchar(30) not null  COMMENT 'id',
    transaction_id VARCHAR(36) NOT NULL COMMENT '外部交易流水',
    contract_id varchar(64) not null comment '上下文ID',
    ip varchar(20) not null comment '日志产生主机IP',
    port varchar(10) not null comment '日志产生端口',
    src_ip varchar(20) comment '调用方IP',
    src_port varchar(10) comment '调用方端口',
    app_id varchar(30) not null comment '调用方应用ID',
    user_id varchar(30) comment '用户ID',
    service_code varchar(50) comment '服务编码',
    service_name varchar(50) comment '服务名称',
    timestamp TIMESTAMP NOT NULL comment '日志交互时间，时间戳',
    cost_time int not null default 0 comment '耗时',
    status_cd varchar(2) not null comment '交互状态 S 成功 F 失败',
    month INT NOT NULL comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    unique KEY (log_id,month)
)
partition BY RANGE (month) (
    partition transaction_log_1 VALUES LESS THAN (2),
    partition transaction_log_2 VALUES LESS THAN (3),
    partition transaction_log_3 VALUES LESS THAN (4),
    partition transaction_log_4 VALUES LESS THAN (5),
    partition transaction_log_5 VALUES LESS THAN (6),
    partition transaction_log_6 VALUES LESS THAN (7),
    partition transaction_log_7 VALUES LESS THAN (8),
    partition transaction_log_8 VALUES LESS THAN (9),
    partition transaction_log_9 VALUES LESS THAN (10),
    partition transaction_log_10 VALUES LESS THAN (11),
    partition transaction_log_11 VALUES LESS THAN (12),
    partition transaction_log_12 VALUES LESS THAN (13)
);



-- 日志原始内容表

create table l_transaction_log_message(
    log_id varchar(30) not null  COMMENT 'id',
    request_header LONGTEXT COMMENT '请求头信息',
    response_header LONGTEXT COMMENT '返回头信息',
    request_message LONGTEXT comment '请求报文',
    response_message LONGTEXT comment '返回报文',
    remark varchar(200) comment '备注',
    month INT NOT NULL  comment '月份',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     UNIQUE KEY (log_id,`month`)
)
partition BY RANGE (month) (
    partition transaction_log_message_1 VALUES LESS THAN (2),
    partition transaction_log_message_2 VALUES LESS THAN (3),
    partition transaction_log_message_3 VALUES LESS THAN (4),
    partition transaction_log_message_4 VALUES LESS THAN (5),
    partition transaction_log_message_5 VALUES LESS THAN (6),
    partition transaction_log_message_6 VALUES LESS THAN (7),
    partition transaction_log_message_7 VALUES LESS THAN (8),
    partition transaction_log_message_8 VALUES LESS THAN (9),
    partition transaction_log_message_9 VALUES LESS THAN (10),
    partition transaction_log_message_10 VALUES LESS THAN (11),
    partition transaction_log_message_11 VALUES LESS THAN (12),
    partition transaction_log_message_12 VALUES LESS THAN (13)
);