项目编号~/project/PAF5 之 数据库设计
项目数据库 PostgreSQL 13.3
核心数据表设计
CREATE TABLE mdata ( idx bigserial not null primary key, // 自增的大范围整数 大序列数字 1 到 9223372036854775807 server_ip inet not null, // 重要★ 服务器IP地址 网络地址inet类型 xx.xx.xx.xx/32 如果其他类型的数据库 该字段修改为text server_port integer not null, // 重要★ 服务器端口 数字 -2147483648 到 +2147483647 vtype varchar(6) not null, // 重要★ 限制长度为6个字符 字符串 枚举 http https socks4 socks5 auth varchar(6) not null default 'NoAuth', // 是否需要认证 长度为6个字符 枚举 NoAuth Auth 暂时不考虑需要认证的数据 auth_name text, // 认证需要的用户名 可为空 类型文本 auth_pass text, // 认证需要的密码 可为空 类型文本 status varchar(1) not null default 'N', // 重要★ 限制长度为1个字符 表示可用状态 字符串 枚举 Y N 默认值为N cnt integer not null default 0, // 重要★ 检测总次数 数字 1 到 +2147483647 y_cnt integer not null default 0, // 重要★ 检测成功次数 数字 1 到 +2147483647 n_cnt integer not null default 0, // 重要★ 检测失败次数 数字 1 到 +2147483647 constncnt integer not null default 0, // 重要★ 连续检测到失败的次数 如果当次检测status是Y 该值置0 如果是N 该值减1 rsp_time real default 0.0, // 响应时间 单位ms 默认值 0.0 download_speed real default 0.0, // 下载速度 单位kB/s 参考值 86.32KB/s 默认值 0.0 upload_speed real default 0.0, // 下载速度 单位kB/s 默认值 0.0 location varchar(6), // 重要★ 国家地区 参考ISO-3166-1 两位字母代码 https://zh.wikipedia.org/wiki/%E5%9C%8B%E5%AE%B6%E5%9C%B0%E5%8D%80%E4%BB%A3%E7%A2%BC location_more text, // 国家地区 更多信息的说明 比如标记城市的信息 isp text, // IP所属的运营商信息 anonymity varchar(1) default 'N', // 是否为高匿名 字符串 枚举 Y N 默认值为N insert_time timestamp without time zone default (now()), // 重要★ 记录数据记录入库的时间 格式样式 YYYY-MM-DD HH-MI-SS update_time text, // 重要★ 记录数据记录更新的时间 格式样式 YYYY-MM-DD HH-MI-SS remark text // 其他必要的备注信息 );
增加表的归属，数据唯一索引等关键权限步骤
CREATE UNIQUE INDEX idx_mdata_main on mdata (server_ip, server_port, vtype); // 创建唯一的关键字段索引 ALTER TABLE public.mdata OWNER TO apguser; // 安全需要 更改数据到普通用户
实际落地的操作
postgres@pve-debian-server-02:~$ psql -d apgdb
psql (13.3 (Debian 13.3-1.pgdg100+1))
apgdb=# \d
Did not find any relations.
apgdb=# CREATE TABLE mdata (
apgdb(# idx bigserial not null primary key,
apgdb(# server_ip inet not null,
apgdb(# server_port integer not null,
apgdb(# vtype varchar(6) not null,
apgdb(# auth varchar(6) not null default 'NoAuth',
apgdb(# auth_name text,
apgdb(# auth_pass text,
apgdb(# status varchar(1) not null default 'N',
apgdb(# cnt integer not null default 0,
apgdb(# y_cnt integer not null default 0,
apgdb(# n_cnt integer not null default 0,
apgdb(# constncnt integer not null default 0,
apgdb(# rsp_time real default 0.0,
apgdb(# download_speed real default 0.0,
apgdb(# upload_speed real default 0.0,
apgdb(# location varchar(6),
apgdb(# location_more text,
apgdb(# isp text,
apgdb(# anonymity varchar(1) default 'N',
apgdb(# insert_time timestamp without time zone default (now()),
apgdb(# update_time text,
apgdb(# remark text
apgdb(# );
CREATE TABLE
apgdb=# \d
public | mdata | table | postgres
public | mdata_idx_seq | sequence | postgres
apgdb=# CREATE UNIQUE INDEX idx_mdata_main on mdata (server_ip, server_port, vtype);
CREATE INDEX
apgdb=# ALTER TABLE public.mdata OWNER TO apguser;
ALTER TABLE
apgdb=#
apgdb=#
apgdb=# \d
public | mdata | table | apguser
public | mdata_idx_seq | sequence | apguser
apgdb=#
apgdb=# insert into mdata(server_ip, server_port, vtype) values('37.49.127.234',1080,'socks5');
INSERT 0 1
apgdb=# select server_ip, server_port, vtype, status, cnt, y_cnt, n_cnt, constncnt, location, insert_time, update_time from mdata limit 3;
server_ip | server_port | vtype | status | cnt | y_cnt | n_cnt | constncnt | location | insert_time | update_time
---------------+-------------+--------+--------+-----+-------+-------+-----------+----------+----------------------------+-------------
172.21.22.3 | 8080 | socks5 | N | 0 | 0 | 0 | 0 | | 2021-06-22 12:12:59.439876 |
175.1.22.3 | 8080 | http | N | 0 | 0 | 0 | 0 | | 2021-06-22 12:12:59.447857 |
24.249.199.12 | 4145 | socks5 | N | 0 | 0 | 0 | 0 | | 2021-06-22 12:12:59.451406 |
(3 rows)
apgdb=#
注释1 如果需要学习PostgreSQL的安装，则请参考”在Debian10系统上,安装和使用PostgreSQL数据库 2021-06-15 https://dasmz.com/?p=168”
