在 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

示例查詢如下：

歡迎來到：碼農很忙。