llpt_migration-kafkaproducer/sql/流量余量.sql

drop table tb_interface_user;
create table tb_interface_user (
	username varchar(50) primary key comment '接口调用账号，分配给使用者的账号，在接口文档中对应于cpid, channel等',
    password varchar(50) comment '接口调用密码',
    realname varchar(500) comment '调用者信息，某某公司、某某渠道',
	systemid varchar(50) comment '系统ID，账号所属的系统，如 orderchannel',
    systemname varchar(100) comment '账号所属系统的名称，系统名称，如前向系统，后向系统',
    interfacename varchar(200) not null comment '账号所属系统的名称，系统名称，如前向系统，后向系统',
    allowip varchar(500) comment '可以调用接口的IP，多个IP用逗号分隔，支持星号（*）',
    duration int comment 'timestamp的有效期限，单位分钟，防止恶意刷接口',
    status varchar(1) default '0' comment '0有效1失效',
    inserttime datetime default now(),
    updatetime datetime default now()
);

insert into tb_interface_user(username, password, realname, systemid, systemname, interfacename, duration)
values('Floor8Kafka', 'dy#@24fL', '楼视频部门将kafka的流量数据透传到9楼','orderchannel', '定向前向前端', 'acceptFlowstatus','10')

select * from tl_traffic_sync where insert_time >= '2020-06-06';
select * from tb_interface_user;
update tb_interface_user set allowip = '*';
update tb_interface_user set allowip = '100.0.3.1,114.255.201.224,114.253.45.19,222.129.192.11,100.0.3.94';
update tb_interface_user set allowip = '127.0.3.1,100.0.3.1,127.0.3.2';

-- 目前同步量分析
-- 差不多是每隔5分钟同步一次
-- 6月8日每分钟最多入库7842条，平均到每秒是120
select date_format(insert_time,'%Y-%m-%d %h %i'),count(1) from tl_traffic_sync 
where insert_time>'2020-06-08'
and insert_time<'2020-06-09'
group by date_format(insert_time,'%Y-%m-%d %h %i')
order by count(1) desc;

select * from tl_traffic_sync where insert_time >= '2020-06-09' and serial_number='18674842761';
alter table tl_traffic_sync add column recieve_time datetime;
select * from tb_interface_user;
-- 222.129.192.11

-- 旧的配置表
select * from tb_traffic_sync_ip;
-- 新的配置表
select * from tb_interface_user;

select * from tl_traffic_sync where insert_time is null;
select to_days(insert_time),unix_timestamp(insert_time) from tl_traffic_sync;


ALTER TABLE tl_traffic_sync PARTITION BY RANGE(to_days(insert_time)) (
PARTITION tl_traffic_sync_202005 VALUES LESS THAN (to_days('2020-06-01')),
PARTITION tl_traffic_sync_202006 VALUES LESS THAN (to_days('2020-07-01')),
PARTITION tl_traffic_sync_202007 VALUES LESS THAN (to_days('2020-08-01')),
PARTITION tl_traffic_sync_202008 VALUES LESS THAN (to_days('2020-09-01')),
PARTITION tl_traffic_sync_202009 VALUES LESS THAN (to_days('2020-10-01')),
PARTITION tl_traffic_sync_202010 VALUES LESS THAN (to_days('2020-11-01')),
PARTITION tl_traffic_sync_202011 VALUES LESS THAN (to_days('2020-12-01')),
PARTITION tl_traffic_sync_202012 VALUES LESS THAN (to_days('2021-01-01')),
PARTITION tl_traffic_sync_202101 VALUES LESS THAN (to_days('2021-02-01')),
PARTITION tl_traffic_sync_202102 VALUES LESS THAN (to_days('2021-03-01')),
PARTITION tl_traffic_sync_202103 VALUES LESS THAN (to_days('2021-04-01')),
PARTITION tl_traffic_sync_202104 VALUES LESS THAN (to_days('2021-05-01')),
PARTITION tl_traffic_sync_202105 VALUES LESS THAN (to_days('2021-06-01')),
PARTITION tl_traffic_sync_202106 VALUES LESS THAN (to_days('2021-07-01')),
PARTITION tl_traffic_sync_202107 VALUES LESS THAN (to_days('2021-08-01')), 
PARTITION tl_traffic_sync_202108 VALUES LESS THAN (to_days('2021-09-01')),
PARTITION tl_traffic_sync_202109 VALUES LESS THAN (to_days('2021-10-01')),
PARTITION tl_traffic_sync_202110 VALUES LESS THAN (to_days('2021-11-01')),
PARTITION tl_traffic_sync_202111 VALUES LESS THAN (to_days('2021-12-01')),
PARTITION tl_traffic_sync_202112 VALUES LESS THAN (to_days('2022-01-01')),
PARTITION tl_traffic_sync_202201 VALUES LESS THAN (to_days('2022-02-01')),
PARTITION tl_traffic_sync_202202 VALUES LESS THAN (to_days('2022-03-01')),
PARTITION tl_traffic_sync_202203 VALUES LESS THAN (to_days('2022-04-01')),
PARTITION tl_traffic_sync_202204 VALUES LESS THAN (to_days('2022-05-01')),
PARTITION tl_traffic_sync_202205 VALUES LESS THAN (to_days('2022-06-01'))
);