在 PostgreSQL 中使用码农很忙 IP 地址数据库
在下载到码农很忙 IP 地址数据库后,我们可以将其存储在 PostgreSQL 数据库中,并在需要查询某个 IP 对应的位置数据时,通过 SQL 语句获取正确的结果。这是一种很便捷的使用方式,并且在增加了恰当的索引后,可以取得不错的搜索效果。
PostgreSQL 数据库内置了 inet
和 range
两个数据类型,用来表示一个网络地址和数值范围。为了达到更高的查询速度,我们可以自定义一个名为 inet_rang
的数据类型,用于表示一个网络地址的范围:
CREATE TYPE inet_range AS RANGE (subtype=inet);
之后,需要在 PostgreSQL 中新建数据表:t_ip_data
,并为 ip_rang
搜索列增加 GiST 索引:
-- ----------------------------
-- Table structure for t_ip_data
-- ----------------------------
DROP TABLE IF EXISTS "public"."t_ip_data";
CREATE TABLE "public"."t_ip_data" (
"id" serial4,
"ip_begin" inet NOT NULL,
"ip_end" inet NOT NULL,
"ip_range" "public"."inet_range",
"cc" varchar(255) COLLATE "pg_catalog"."default",
"continent" varchar(255) COLLATE "pg_catalog"."default",
"flag" varchar(255) COLLATE "pg_catalog"."default",
"country" varchar(255) COLLATE "pg_catalog"."default",
"province" varchar(255) COLLATE "pg_catalog"."default",
"city" varchar(255) COLLATE "pg_catalog"."default",
"tag" varchar(255) COLLATE "pg_catalog"."default",
"isp" varchar(255) COLLATE "pg_catalog"."default"
)
;
-- ----------------------------
-- Indexes structure for table t_ip_data
-- ----------------------------
CREATE INDEX "t_ip_data_IPRange_idx" ON "public"."t_ip_data" USING gist (
"ip_range" "pg_catalog"."range_ops"
);
-- ----------------------------
-- Primary Key structure for table t_ip_data
-- ----------------------------
ALTER TABLE "public"."t_ip_data" ADD CONSTRAINT "t_ip_data_pkey" PRIMARY KEY ("id");
之后,使用 Navicat 等工具,将码农很忙 IP 地址数据库中的数据导入到数据表 t_ip_data 中,注意字段对应:
如果不是第一次导入数据,注意将【导入模式】设置为【复制】:
数据导入成功后,需要使用以下 SQL 语句对 ip_range 进行修正:
UPDATE t_ip_data SET ip_range = ('[' ||ip_begin || ',' || ip_end || ']') :: inet_range
之后,就可以使用以下 SQL 语句进行数据查询:
SELECT * FROM t_ip_data WHERE ip_range @> '要查询的 IPv4 地址' :: inet
示例查询如下:
欢迎来到:码农很忙 。