分析使用者的 IP 是來自哪個國家/城市, 在觀察網路行為上是很重要的一項數據.
有興趣的人可以到這裡找尋一些 IP 與地理資訊的相關資源.
至於 IP 轉換成數字的公式, 可以在 IP address 的 wiki 找到.
以 wiki 中所舉的 172.16.254.1 來說:
- 將 172 * 16777216 得到 一個 w 值 ( 2^24 = 16777216 )
- 將 16 * 65536 得到 一個 x 值 ( 2^16 = 65536 )
- 將 254 * 256 得到 一個 y 值 ( 2^8 = 256 )
- 將 1 * 1 得到一個 z 值 ( 2^0 = 1 )
- 最後透過 w + x + y + z 可得到此 IP 所代表的數字 2886794753
反之, 將 2886794753 轉換為 IP 字串的方式如下:
- 將 2886794753 / 16777216 得到 172
- 將 (2886794753 % 16777216) / 65536 得到 16
- 將 ((2886794753 % 16777216) % 65536) / 256 得到 254
- 將 (((2886794753 % 16777216) % 65536)) % 256 得到 1
- 最後結果就是 172.16.254.1
因為欲比對的資料是放在資料庫中的,所以將以上的運算轉為兩個資料庫的 function, 以方便後續查詢.
- 將 IP 轉為 Number 的 dbo.getIPNumber():
|
1
2
3
4
5
6
7
8
9
10
11
12 |
CREATE FUNCTION [dbo].[getIPNumber] ( @ip varchar(15) )
RETURNS bigint
AS
BEGIN
Declare @part1 bigint, @part2 int, @part3 int, @part4 int
SELECT @part1=CAST(LEFT(@ip, CHARINDEX ('.' , @ip)-1) as bigint)*16777216, @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
SELECT @part2=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*65536, @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
SELECT @part3=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*256, @part4=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
Return @part1+@part2+@part3+@part4
END
|
- 將 Number 轉為 IP 的 dbo.getIPString():
|
1
2
3
4
5
6
7
8
9
10
11
12 |
CREATE FUNCTION [dbo].[getIPString] ( @num bigint )
RETURNS varchar(15)
AS
BEGIN
DECLARE @ip varchar(15)
SELECT @ip = @num / 16777216, @num=@num % 16777216
SELECT @ip = @ip + '.' + cast(@num / 65536 as varchar), @num=@num % 65536
SELECT @ip = @ip + '.' + cast(@num / 256 as varchar), @ip = @ip + '.' + cast(@num % 256 as varchar)
Return @ip
END
| 由於 SQL 的 SELECT 給值是從左至右依序進行的, 所以以上兩個寫法都可再精簡成一個 SELECT 將資料都算完.
精簡版如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 |
CREATE FUNCTION [dbo].[getIPNumber] ( @ip varchar(15) )
RETURNS bigint
AS
BEGIN
Declare @part1 bigint, @part2 int, @part3 int, @part4 int
SELECT @part1=CAST(LEFT(@ip, CHARINDEX ('.' , @ip)-1) as bigint)*16777216
, @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
, @part2=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*65536
, @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
, @part3=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*256
, @part4=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
Return @part1+@part2+@part3+@part4
END
CREATE FUNCTION [dbo].[getIPString] ( @num bigint )
RETURNS varchar(15)
AS
BEGIN
DECLARE @ip varchar(15)
SELECT @ip = @num / 16777216
, @num=@num % 16777216
, @ip = @ip + '.' + cast(@num / 65536 as varchar)
, @num=@num % 65536
, @ip = @ip + '.' + cast(@num / 256 as varchar)
, @ip = @ip + '.' + cast(@num % 256 as varchar)
Return @ip
END
| 補充: 在 SQL Server 2005 後, 有一個 ParseName 的函式 (說明)
據 MSDN 的說明, 此函式是用在傳回物件名稱的指定部份. 物件的可擷取部份有物件名稱、擁有者名稱、資料庫名稱和伺服器名稱.
由於在資料庫中, 物件結構是 [伺服器名稱].[資料庫名稱].[結構描述名稱].[物件名稱], 剛好跟 IP 的結構一樣, 所以我們可以改寫 IP 轉換至 Number 的函式如下.
|
1
2
3
4
5
6
7
8
9
10
11 |
CREATE FUNCTION [dbo].[getIPNumber] ( @ip varchar(15) )
RETURNS bigint
AS
BEGIN
Return CAST(PARSENAME(@ip,4) as bigint)*16777216
+ PARSENAME(@ip,3)*65536
+ PARSENAME(@ip,2)*256
+ PARSENAME(@ip,1)
END
|
|
