利用SQL區分網站域名IP地址歸屬的方法

羅望東+梁艷花+王佳

摘 要

在IP網絡的日常維護中，常常需要對網站域名以及IP地址的歸屬地進行區分，本文介紹了如何利用SQL數據庫對網站域名以及IP地址的歸屬進行查詢區分的方法。

【關鍵詞】SQL 網站域名 IP地址 歸屬 查詢

1 前言

IP網維護人員在日常網絡維護工作中，經常會遇到大量網站域名或IP地址需要按市分公司區分歸屬地，然后由對應市分公司對所屬域名或IP進行相關處理。在數量少的時候，可以人工通過查詢資料進行區分，可當查詢量達到成千上萬時，人工查詢幾乎是個不可能完成的工作。建立一個IP地址歸屬查詢數據庫，通過SQL（結構化查詢語言）對需要查詢的域名或IP地址按市分公司區分，可以大大提高工作效率和查詢的準確性。

2 查詢原理及準備工作

IP網絡維護人員一般都有自己維護管理的IP地址歸屬的詳細資料，但是把要查詢的IP地址與IP地址歸屬資料進行查詢關聯是個難點，這個難點難在IP地址的記錄方式是點分十進制的，四段數字被三個點分隔開，每段的十進制數是0至255之間的整數，每段數字前面的0可寫也可不寫，難以進行查詢關聯。

為了使IP地址便于查詢，可將點分十進制的IP地址換算成十進制的整數，這樣就可以比較大小進行查詢。一個IP地址段的開始和結束IP地址分別換算成十進制整數，將一個待查詢的IP地址也換算成十進制地址數，利用SQL的查詢語句，待查的IP地址跟SQL數據庫中的IP地址段的開始和結束IP的十進制數比較，如果大于等于開始IP數，并且小于等于結束IP數，那么說明這個IP地址是在這個IP地址段內的IP，否則這個IP地址不屬于這個IP段。如果IP網絡已經按地域進行過分域，某一個IP地址段在骨干路由器中可以查尋到路由對應的AS號，AS號對應的地域，就是IP所屬的地域。

2.1 點分十進制IP換算成十進制整數的方法

點分十進制IP換算成十進制整數可以利用EXCEL進行，EXCEL可以雙擊表格右下角的點，使公式自動下拉計算。假如EXCEL表的A1單元格是一個點分十進制的IP，將A1單元格IP的十進制數以點為分界分別拆分到B1至E1四個格內，F1是拆分了最左邊數的IP，G1是拆分了F1最左邊數的IP，H1是最終該IP換算后的十進制數，可以復制H列，在EXCEL文件中選擇性粘貼，粘貼數值，得到每個IP對應的十進制數值。A1之后每個單元格對應的計算公式如下：

B1= LEFT（A1，SEARCH（"."，A1，1）-1）

C1= LEFT（F1，SEARCH（"."，F1，1）-1）

D1= LEFT（G1，SEARCH（"."，G1，1）-1）

E1= RIGHT（G1，LEN（G1）-SEARCH（"."，G1，1））

F1= RIGHT（A1，LEN（A1）-SEARCH（"."，A1，1））

G1= RIGHT（F1，LEN（F1）-SEARCH（"."，F1，1））

H1= （（B1*256+C1）*256+D1）*256+E1

2.2 SQL查詢使用前的準備工作

（1）建立一個Microsoft SQL Servers服務器。

（2）在SQL Servers上建立一個數據庫表。

（3）上傳到數據庫中的表都用EXCEL保存為CSV（逗號分隔）格式，在SQL企業管理器中建立一個自己的數據庫表，在數據庫表里導入上傳IP歸屬的相關數據表，導入上傳表時數據源選“文本文件”，第一行含列名稱打鉤，后面提到的上傳導入數據庫表方法都一樣。

（4）在數據庫的表中上傳屬于本省的IP，表名“ip_henan”，列名可以包含net（IP段的網絡IP即起始IP）、mask（網絡的掩碼）、start_ip（IP段的起始IP的十進制數）、end_ip（IP段的結束IP的十進制數）、area（IP所屬省的名字）等。

（5）上傳全省的IP路由表，表名“ip_route_table”，這個表是一個比較關鍵的表，該表可以從核心骨干路由器上得到，包含的列名可以有net（某段網絡IP地址的起始IP）、mask（掩碼）、from_router（從那臺路由器學習來的）、as_no （AS號）、start_ip（IP段的起始IP的十進制數）、end_ip（IP段的結束IP的十進制數）。

（6）上傳全省AS號對應的市分公司名，表名“ip_as”，列名包括city（城市名）、as_no（城市對應的AS號）。

3 查詢域名對應IP的方法

如果待查域名沒有IP地址，需要先查詢域名對應的IP地址，將域名對應的IP在UNIX主機上用到dig命令查出來，將含A或CNAME記錄的IP地址記錄到一個臨時文本文件中，然后將域名與IP地址用SQL查詢聯系到一起，再根據IP地址查詢歸屬地域。如果待查域名已有IP地址，可以直接查詢IP地址對應的地市歸屬。

3.1 步驟一：將原始查詢資料整理成標準格式

（1）將需查詢的域名在excel中整理成統一的格式，另存成CSV（逗號分隔）格式，文本名“name_ip_search_excel”；列名按id（序號），web（網站名），name（域名）起，如果原始列表里列名有其他項，可以根據情況自定義，但域名一列一定要輸入name；

（2）在SQL查詢分析器中，先清除以前的表記錄TRUNCATE table name_ip_search_excel；

（3）導入到SQL server 數據庫的表中。

3.2 步驟二：將需查詢的域名單獨整理出來

（1）將所查域名name這列單獨保存，存成文本文件“name_to_ip_search”；endprint

（2）先清除以前的表記錄TRUNCATE table name_to_ip_search；

（3）導入到SQL server數據庫的表中。

3.3 步驟三：利用dig命令查詢所有域名對應的IP

（1）用excel 編輯name_to_ip_search表，前后各加一列；前一列均填充 “dig “ ，后一列填充：“ | egrep "-------A-------|-------CNAME-------" >>/tmp/get_ip_result.txt”（引號中內容）；

（2）將3列拷貝至UltraEdit文本編輯軟件中，首先將“ ”（tab鍵）替換為空格，然后，將“-------”替換為“ ”（tab鍵），tab鍵無法輸入，可以先在記事本里輸入，然后粘貼過去；

（3）另存為getip.sh文件；

（4）上傳getip.sh到可以使用dig命令的UNIX主機中，設置允許執行權限：chmod 755 getip.sh；

（5）刪除以前的文件，執行rm –f /tmp/get_ip_result.txt；

（6）用 ./getip.sh &執行查詢命令；

（7）根據內容多少，查詢時間長短不一定，可以用ls -l 查看get_ip_result.txt文件大小是否不斷增長，來判斷是否查詢完畢。

3.4 步驟四：將域名查詢出來的IP整理成標準格式

（1）將執行后的/tmp/get_ip_result.txt文件導出；

（2）用excel打開修改，列名包含name（域名）、num（dig查出來的num數）、Inhao（dig查出來的IN號）、type（域名記錄類型）、ip（域名對應的IP），另存成CSV格式文件“get_ip_result”；

（3）先清除以前的表記錄 TRUNCATE table get_ip_result；

（4）導入到SQL server 數據庫的表中。

3.5 步驟五：將需查詢的域名與查詢出來的IP對應起來

（1）將每個需要查詢的域名name_to_ip_search，從結果get_ip_result中提取出來；有些域名采用CNAME方式，要查詢到域名最終對應的IP，需要進行嵌套查詢。本次案例進行了5次嵌套，部分域名可能需要增加更多級嵌套。

（2）在SQL查詢分析器中執行如下腳本：

TRUNCATE table name_ip_result_tmp；

insert into name_ip_result_tmp select distinct a.name，b.ip --第一次直接查詢A記錄

from name_to_ip_search a ，get_ip_result b

where a.name+'.'=b.name

and b.type='A' ；

insert into name_ip_result_tmp select distinct a.name，c.ip --第二次嵌套查詢

from name_to_ip_search a ，get_ip_result b，get_ip_result c

where a.name+'.'=b.name

and b.type='CNAME'

and b.ip=c.name

and c.type='A' ；

insert into name_ip_result_tmp select distinct a.name，d.ip --第三次嵌套查詢

from name_to_ip_search a ，get_ip_result b，get_ip_result c，get_ip_result d

where a.name+'.'=b.name

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='A'；

insert into name_ip_result_tmp select distinct a.name，e.ip --第四次嵌套查詢

from name_to_ip_search a ，get_ip_result b，get_ip_result c，get_ip_result d，get_ip_result e

where a.name+'.'=b.name

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='CNAME'

and d.ip=e.name

and e.type='A'；

insert into name_ip_result_tmp select distinct a.name，f.ip --第五次嵌套查詢

from name_to_ip_search a ，get_ip_result b，get_ip_result c，get_ip_result d，get_ip_result e，get_ip_result f

where a.name+'.'=b.nameendprint

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='CNAME'

and d.ip=e.name

and e.type='CNAME'

and e.ip=f.name；

3.6 步驟六：將原始查詢資料與IP對應起來

（1）將name_ip_search_excel 和name_ip_result_tmp進行關聯處理，取最終結果；

（2）腳本：

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.name=bb.name

union all

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.name<>bb.name and aa.name like '%'+bb.name+'%'

執行完后數分鐘會得到原始查詢資料后面加查詢的域名及IP地址的列表結果。以上六步將域名對應的IP地址查詢了出來，下面可以根據IP地址定位所屬市分公司。

4 根據IP地址定位其所屬市分公司的方法

如果有其他已知IP地址需要定位歸屬地的工作，可以直接按此方法進行查詢。

4.1 步驟一：將域名與IP地址資料整理成標準格式

（1）將帶IP地址的結果拷貝到excel中，將IP地址按前面介紹的方法換算成十進制數放在后面一列，列名稱為ip_num，此列名很重要，不要輸錯，存成CSV（逗號分隔）格式，文件名“ip_search_table”。

（2）菜單“工具”中選擇“SQl查詢分析器”，在分析器打開后，清除以前的記錄，執行：Delete from ip_search_table；

（3）將ip_search_table表導入到SQL server 數據庫的表中

4.2 步驟二：將IP與市分公司對應起來

（1）在SQL查詢分析器中：執行

select distinct bb.*，aa.net，aa.mask，aa.city from

（select distinct a.*，b.area

from ip_search_table a left join ip_henan b

on a.ip_num >=b.start_ip and a.ip_num<=b.end_ip

） bb

left join

（select a.*，b.city

from ip_route_table a，ip_as b

where a.as_no=b.as_no

） aa

on bb.ip_num >= aa.start_ip_num

and bb.ip_num <= aa.end_ip_num

（2）一般數分鐘后查詢可以結束，將查詢結果拷貝到Excel中，刪除一些域名重復的行，和不用的參考列，在IP地址后面可以得到所屬省、市分公司的區分結果。如果所屬省結果內容為NULL，可能該IP不屬于自己的省，如果是屬于自己省的IP，但是市分公司結果內容為NULL，可能全省的IP路由表不準確，需要在資料和設備上核對后更新數據庫中的全省IP路由表ip_route_table。

5 小結

以往人工查詢IP地址歸屬時，非常費時費力，利用SQL查詢，數千條的IP地址定位只要幾分鐘就可以完成。而且利用SQL查詢比較靈活，對需查詢的IP地址列表的原始格式沒有嚴格要求，如果能進一步完善IP歸屬資料數據庫的話，利用SQL查詢可以查詢到更多的信息。利用SQL還可以對全省各市分公司的IP地址使用情況進行分析，分析各市分公司的撥號地址池和專線地址池使用情況，找到未使用的IP地址，提高市分公司的IP地址利用率。建立一個IP地址歸屬查詢數據庫后，通過SQL可以開發出更多的功能。

參考文獻

[1][美]斯蒂芬森，[美]晉勞，[美]瓊斯著，井中月，郝記生譯. SQL入門經典（第5版）[M].北京：人民郵電出版社，2011-11-1.

[2][美]Ben Forta著，鐘鳴，劉曉霞譯. SQL必知必會（第4版）[M].北京：人民郵電出版社，2013-5-1.

作者單位

中國聯合網絡通信有限公司河南省分公司網絡管理中心 河南省鄭州市 450000endprint

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='CNAME'

and d.ip=e.name

and e.type='CNAME'

and e.ip=f.name；

3.6 步驟六：將原始查詢資料與IP對應起來

（1）將name_ip_search_excel 和name_ip_result_tmp進行關聯處理，取最終結果；

（2）腳本：

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.name=bb.name

union all

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.name<>bb.name and aa.name like '%'+bb.name+'%'

執行完后數分鐘會得到原始查詢資料后面加查詢的域名及IP地址的列表結果。以上六步將域名對應的IP地址查詢了出來，下面可以根據IP地址定位所屬市分公司。

4 根據IP地址定位其所屬市分公司的方法

如果有其他已知IP地址需要定位歸屬地的工作，可以直接按此方法進行查詢。

4.1 步驟一：將域名與IP地址資料整理成標準格式

（1）將帶IP地址的結果拷貝到excel中，將IP地址按前面介紹的方法換算成十進制數放在后面一列，列名稱為ip_num，此列名很重要，不要輸錯，存成CSV（逗號分隔）格式，文件名“ip_search_table”。

（2）菜單“工具”中選擇“SQl查詢分析器”，在分析器打開后，清除以前的記錄，執行：Delete from ip_search_table；

（3）將ip_search_table表導入到SQL server 數據庫的表中

4.2 步驟二：將IP與市分公司對應起來

（1）在SQL查詢分析器中：執行

select distinct bb.*，aa.net，aa.mask，aa.city from

（select distinct a.*，b.area

from ip_search_table a left join ip_henan b

on a.ip_num >=b.start_ip and a.ip_num<=b.end_ip

） bb

left join

（select a.*，b.city

from ip_route_table a，ip_as b

where a.as_no=b.as_no

） aa

on bb.ip_num >= aa.start_ip_num

and bb.ip_num <= aa.end_ip_num

（2）一般數分鐘后查詢可以結束，將查詢結果拷貝到Excel中，刪除一些域名重復的行，和不用的參考列，在IP地址后面可以得到所屬省、市分公司的區分結果。如果所屬省結果內容為NULL，可能該IP不屬于自己的省，如果是屬于自己省的IP，但是市分公司結果內容為NULL，可能全省的IP路由表不準確，需要在資料和設備上核對后更新數據庫中的全省IP路由表ip_route_table。

5 小結

以往人工查詢IP地址歸屬時，非常費時費力，利用SQL查詢，數千條的IP地址定位只要幾分鐘就可以完成。而且利用SQL查詢比較靈活，對需查詢的IP地址列表的原始格式沒有嚴格要求，如果能進一步完善IP歸屬資料數據庫的話，利用SQL查詢可以查詢到更多的信息。利用SQL還可以對全省各市分公司的IP地址使用情況進行分析，分析各市分公司的撥號地址池和專線地址池使用情況，找到未使用的IP地址，提高市分公司的IP地址利用率。建立一個IP地址歸屬查詢數據庫后，通過SQL可以開發出更多的功能。

參考文獻

[1][美]斯蒂芬森，[美]晉勞，[美]瓊斯著，井中月，郝記生譯. SQL入門經典（第5版）[M].北京：人民郵電出版社，2011-11-1.

[2][美]Ben Forta著，鐘鳴，劉曉霞譯. SQL必知必會（第4版）[M].北京：人民郵電出版社，2013-5-1.

作者單位

中國聯合網絡通信有限公司河南省分公司網絡管理中心 河南省鄭州市 450000endprint

and b.type='CNAME'

and b.ip=c.name

and c.type='CNAME'

and c.ip=d.name

and d.type='CNAME'

and d.ip=e.name

and e.type='CNAME'

and e.ip=f.name；

3.6 步驟六：將原始查詢資料與IP對應起來

（1）將name_ip_search_excel 和name_ip_result_tmp進行關聯處理，取最終結果；

（2）腳本：

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.name=bb.name

union all

select * from name_ip_search_excel aa， name_ip_result_tmp bb

where aa.name<>bb.name and aa.name like '%'+bb.name+'%'

執行完后數分鐘會得到原始查詢資料后面加查詢的域名及IP地址的列表結果。以上六步將域名對應的IP地址查詢了出來，下面可以根據IP地址定位所屬市分公司。

4 根據IP地址定位其所屬市分公司的方法

如果有其他已知IP地址需要定位歸屬地的工作，可以直接按此方法進行查詢。

4.1 步驟一：將域名與IP地址資料整理成標準格式

（1）將帶IP地址的結果拷貝到excel中，將IP地址按前面介紹的方法換算成十進制數放在后面一列，列名稱為ip_num，此列名很重要，不要輸錯，存成CSV（逗號分隔）格式，文件名“ip_search_table”。

（2）菜單“工具”中選擇“SQl查詢分析器”，在分析器打開后，清除以前的記錄，執行：Delete from ip_search_table；

（3）將ip_search_table表導入到SQL server 數據庫的表中

4.2 步驟二：將IP與市分公司對應起來

（1）在SQL查詢分析器中：執行

select distinct bb.*，aa.net，aa.mask，aa.city from

（select distinct a.*，b.area

from ip_search_table a left join ip_henan b

on a.ip_num >=b.start_ip and a.ip_num<=b.end_ip

） bb

left join

（select a.*，b.city

from ip_route_table a，ip_as b

where a.as_no=b.as_no

） aa

on bb.ip_num >= aa.start_ip_num

and bb.ip_num <= aa.end_ip_num

（2）一般數分鐘后查詢可以結束，將查詢結果拷貝到Excel中，刪除一些域名重復的行，和不用的參考列，在IP地址后面可以得到所屬省、市分公司的區分結果。如果所屬省結果內容為NULL，可能該IP不屬于自己的省，如果是屬于自己省的IP，但是市分公司結果內容為NULL，可能全省的IP路由表不準確，需要在資料和設備上核對后更新數據庫中的全省IP路由表ip_route_table。

5 小結

以往人工查詢IP地址歸屬時，非常費時費力，利用SQL查詢，數千條的IP地址定位只要幾分鐘就可以完成。而且利用SQL查詢比較靈活，對需查詢的IP地址列表的原始格式沒有嚴格要求，如果能進一步完善IP歸屬資料數據庫的話，利用SQL查詢可以查詢到更多的信息。利用SQL還可以對全省各市分公司的IP地址使用情況進行分析，分析各市分公司的撥號地址池和專線地址池使用情況，找到未使用的IP地址，提高市分公司的IP地址利用率。建立一個IP地址歸屬查詢數據庫后，通過SQL可以開發出更多的功能。

參考文獻

[1][美]斯蒂芬森，[美]晉勞，[美]瓊斯著，井中月，郝記生譯. SQL入門經典（第5版）[M].北京：人民郵電出版社，2011-11-1.

[2][美]Ben Forta著，鐘鳴，劉曉霞譯. SQL必知必會（第4版）[M].北京：人民郵電出版社，2013-5-1.

作者單位

中國聯合網絡通信有限公司河南省分公司網絡管理中心 河南省鄭州市 450000endprint