db · Wiki · 互联网应用开发 / project-credit

db

接口相关表

CREATE TABLE `api` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '接口名称',
  `cn_name` varchar(20) DEFAULT '' COMMENT '接口中文名称',
  `desc` varchar(50) DEFAULT '' COMMENT '接口描述',
  `url` varchar(100) NOT NULL DEFAULT '' COMMENT '接口地址',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '接口状态',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_upadte` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='接口表';

INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('overdueClassify', '逾期', '逾期信息', 'https://i.trustutn.org/b/overdueClassify', 1, 0, '2016-08-15 09:35:23');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('loanClassify', '共债', '共债信息', 'https://i.trustutn.org/b/loanClassify', 1, 0, '2016-08-15 09:35:46');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('blacklist', '黑名单', '黑名单信息', 'https://i.trustutn.org/b/blacklist', 1, 0, '2016-08-15 09:36:05');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('logStatistics', '其它机构查询', '其它机构查询情况', 'https://i.trustutn.org/b/logStatistics', 1, 0, '2016-08-15 09:36:55');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('idCheck', '身份证验证', '身份证信息验证', 'https://i.trustutn.org/idCheck', 1, 0, '2016-08-15 09:37:31');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('bankcardauth', '银行卡验证', '银行卡实名验证
', 'https://i.trustutn.org/bankcardauth', 1, 0, '2016-08-15 09:38:51');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('phoneinfo', '电话号码信息', '电话号码相关查询', 'https://i.trustutn.org/b/phoneinfo', 1, 0, '2016-09-01 06:25:41');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('phonetag', '电话号码标记', '电话号码标记查询', 'https://i.trustutn.org/phonetag', 1, 0, '2016-09-01 06:25:42');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('phoneMatch', '设备号匹配', '手机号设备号匹配查询', 'https://i.trustutn.org/b/phoneMatch', 1, 0, '2016-09-01 06:25:45');
 
CREATE TABLE `api_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `api_id` int(11) NOT NULL DEFAULT '0' COMMENT '接口id',
  `time_index` int(11) DEFAULT '0',
  `id_card` bigint(20) DEFAULT '0' COMMENT '身份证号',
  `name` varchar(50) DEFAULT '' COMMENT '姓名',
  `phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '手机号',
  `imsi` bigint(20) NOT NULL DEFAULT '0' COMMENT '国际移动用户识别码',
  `imei` bigint(20) NOT NULL DEFAULT '0',
  `input_params` varchar(2000) NOT NULL DEFAULT '' COMMENT '传入参数',
  `output_params` varchar(2000) NOT NULL DEFAULT '' COMMENT '返回结果',
  `response_time` bigint(20) DEFAULT '0' COMMENT '接口响应时间',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '返回状态 0 调用失败 2 调用成功,但未命中 1调用成功且命中',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_uast` (`user_id`,`api_id`,`status`,`time_index`),
  KEY `idx_tuas` (`time_index`,`user_id`,`api_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=280043 DEFAULT CHARSET=utf8 COMMENT='接口调用日志表';
 
CREATE TABLE `api_log_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `api_id` int(11) DEFAULT '0' COMMENT '接口id,0则为汇总数据',
  `data_key` varchar(100) NOT NULL DEFAULT '' COMMENT '键',
  `time_type` enum('DAY','WEEK','MONTH','YEAR','TOTAL') DEFAULT 'DAY' COMMENT '时间维度的类别',
  `time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间维度,0则为汇总数据',
  `data_value` int(11) NOT NULL DEFAULT '0' COMMENT '值',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_utd` (`user_id`,`api_id`,`data_key`,`time_type`,`time_index`)
) ENGINE=InnoDB AUTO_INCREMENT=13257 DEFAULT CHARSET=utf8;

基础信息表

CREATE TABLE `base_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `info` varchar(50) NOT NULL DEFAULT '',
  `code` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (0, '查询类型', 0);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (0, '证件类型', 0);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '基本查询', 0);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '基本查询加地址', 1);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '基本查询加照片', 2);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '全部信息', 3);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '居民身份证', 111);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '军官证', 114);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外国人临时居留证', 555);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '其他证件', 990);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '法人证件', 992);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '组织机构代码', 991);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '营业执照', 889);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '临时居民身份证', 112);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '暂住证', 116);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '学生证', 133);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '机动车驾驶证', 335);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外交护照', 411);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '公务护照', 412);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '因公普通护照', 413);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '普通护照', 414);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '旅行证', 415);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '入出境通行证', 416);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外国人出入境证', 417);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外国人旅行证', 418);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '香港特别行政区护照', 420);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '澳门特别行政区护照', 421);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '台湾居民来往大陆通行证', 511);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '往来港澳通行证', 513);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '来往港澳通行证', 515);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '港澳同胞回乡证(通行卡)', 516);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '大陆居民往来台湾通行证', 517);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '因公往来香港澳门特别行政区通行证', 518);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '边境管理区通行证', 711);

通知表

CREATE TABLE `notice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '被通知人,0则为全体通知',
  `type` varchar(10) NOT NULL DEFAULT '' COMMENT '通知类型',
  `title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
  `content` varchar(500) NOT NULL DEFAULT '' COMMENT '正文',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_u` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8;
 
CREATE TABLE `notice_template` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `type` varchar(10) NOT NULL DEFAULT '' COMMENT '通知类型',
  `title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
  `content` varchar(500) NOT NULL DEFAULT '' COMMENT '内容, ? 为占位符,可动态替换',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='通知模板';

帮助信息表

CREATE TABLE `support` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
  `content` text NOT NULL COMMENT '正文',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
  `helpful` mediumint(9) NOT NULL DEFAULT '0' COMMENT '是否有帮助',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8;

用户表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码',
  `salt` varchar(10) NOT NULL DEFAULT '',
  `company` varchar(255) DEFAULT '' COMMENT '公司名',
  `fchar` char(1) NOT NULL DEFAULT '' COMMENT '公司首字母',
  `pname` varchar(20) NOT NULL DEFAULT '' COMMENT 'pname用于接口安全校验',
  `pkey` char(32) NOT NULL DEFAULT '' COMMENT 'pkey用于接口安全校验',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '账号状态',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_u` (`username`),
  KEY `idx_up` (`username`,`password`)
) ENGINE=InnoDB AUTO_INCREMENT=210 DEFAULT CHARSET=utf8;

CREATE TABLE `user_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time_type` enum('week','month','day') DEFAULT 'week' COMMENT '时间纬度的类别',
  `time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间纬度,0则为汇总数据',
  `data_key` varchar(50) NOT NULL DEFAULT '' COMMENT '键',
  `data_value` int(11) NOT NULL DEFAULT '0' COMMENT '值',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tkv` (`time_index`,`time_type`,`data_key`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;

账户表

CREATE TABLE `user_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `account_balance` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '账户余额',
  `accumulated_amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '累计消费金额',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ut` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=utf8;

CREATE TABLE `user_account_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `type` varchar(20) NOT NULL DEFAULT 'consumption' COMMENT '消费consumption 赠送free 充值recharge',
  `money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '金额',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间维度',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ut` (`user_id`),
  KEY `idx_tut` (`user_id`,`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6712 DEFAULT CHARSET=utf8;

CREATE TABLE `user_account_log_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间维度',
  `data_key` varchar(100) NOT NULL DEFAULT '' COMMENT '键',
  `data_value` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '值',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_utd` (`user_id`,`data_key`,`time_index`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;

用户接口关系表

CREATE TABLE `user_api` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `api_id` int(11) NOT NULL DEFAULT '0' COMMENT '接口id',
  `price` decimal(6,2) NOT NULL DEFAULT '0.00',
  `rule` enum('hit','call') NOT NULL DEFAULT 'call' COMMENT 'hit命中,call调用',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ua` (`user_id`,`api_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COMMENT='用户接口服务';

CREATE TABLE `user_api_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `api_id` int(11) NOT NULL DEFAULT '0' COMMENT '接口id',
  `price` decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '价格',
  `rule` enum('hit','call') NOT NULL DEFAULT 'call' COMMENT '计费规则',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

白名单表

CREATE TABLE `user_ip_whitelist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `ip` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ip地址,用 INET_ATON() 和 INET_NTOA() 函数互转',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ui` (`user_id`,`ip`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COMMENT='用户ip白名单';