SQL IP to BigInt IP地址转换数字

来源：互联网发布：手机照片移花接木软件编辑：程序博客网时间：2024/06/10 08:45

http://blog.joycode.com/ghj/tags/287.aspx

前一篇博客 IP to Integer 提供的 SQL 函数是 IP 转换成 Integer 的方法。 Integer 的最大值为： 2147483647（2^31 - 1）。按照 IP 转换成整数的算法，会算出来大于这个值的，所以之前提供的 IP 转换成 Integer 的方法会算出负值。

下面是 IP to BigInt 的转换算法，这时候运算就不会小于零。

CREATE FUNCTION dbo.ipStringToBigInt (     @ip CHAR(15) ) RETURNS bigint AS BEGIN     DECLARE @rv bigint,         @o1 bigint,         @o2 INT,         @o3 INT,         @o4 INT     SELECT         @o1 = CONVERT(INT, PARSENAME(@ip, 4)),         @o2 = CONVERT(INT, PARSENAME(@ip, 3)),         @o3 = CONVERT(INT, PARSENAME(@ip, 2)),         @o4 = CONVERT(INT, PARSENAME(@ip, 1))      IF (@o1 BETWEEN 0 AND 255)         AND (@o2 BETWEEN 0 AND 255)         AND (@o3 BETWEEN 0 AND 255)         AND (@o4 BETWEEN 0 AND 255)     BEGIN               SET @rv = (@o1 * 16777216)  +              (@o2 * 65536) +              (@o3 * 256) +             (@o4)     END     ELSE         SET @rv = -1     RETURN @rv ENDgo CREATE FUNCTION dbo.ipBigIntToString (     @ip bigint ) RETURNS CHAR(15) AS BEGIN     DECLARE @o1 INT,         @o2 INT,         @o3 INT,         @o4 INT      IF @ip > 4294967295 RETURN '255.255.255.255'     IF @ip <= 0 RETURN '0.0.0.0'      SET @o1 = @ip / 16777216     SET @ip = @ip % 16777216     SET @o2 = @ip / 65536     SET @ip = @ip % 65536     SET @o3 = @ip / 256     SET @ip = @ip % 256     SET @o4 = @ip      RETURN         CONVERT(VARCHAR(4), @o1) + '.' +         CONVERT(VARCHAR(4), @o2) + '.' +         CONVERT(VARCHAR(4), @o3) + '.' +         CONVERT(VARCHAR(4), @o4) ENDgo -- 调用例子select dbo.ipBigIntToString(3708279131)select dbo.ipStringToBigInt('221.7.217.91')

IP to Integer
有时候我们要判断某个IP是否在某个段内，如何实现呢？ 一个简单办法就是把IP转换成Integer，然后判断整数是否在某个范围内就可以实现这个判断。
转换的算法如下：
比如我们要转换的IP为： 058.062.042.000
First Octet: 058 
Second Octet: 062 
Third Octet: 042 
Fourth Octet: 000 
计算公式如下： 
(first octet * 256³) + (second octet * 256²) + (third octet * 256) + (fourth octet) 
=  (first octet * 16777216) + (second octet * 65536) + (third octet * 256) + (fourth octet) 
=  (058 * 16777216) + (062 * 65536) + (042 * 256) + (000) 
=  977152512 
网上有现成的服务，比如下面这个地址：
http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp
SQL Server 的一个例子如下：
CREATE FUNCTION dbo.ipStringToInt (     @ip CHAR(15) ) RETURNS INT AS BEGIN     DECLARE @rv INT,         @o1 INT,         @o2 INT,         @o3 INT,         @o4 INT,         @base INT      SELECT         @o1 = CONVERT(INT, PARSENAME(@ip, 4)),         @o2 = CONVERT(INT, PARSENAME(@ip, 3)),         @o3 = CONVERT(INT, PARSENAME(@ip, 2)),         @o4 = CONVERT(INT, PARSENAME(@ip, 1))      IF (@o1 BETWEEN 0 AND 255)         AND (@o2 BETWEEN 0 AND 255)         AND (@o3 BETWEEN 0 AND 255)         AND (@o4 BETWEEN 0 AND 255)     BEGIN              SELECT @base = CASE             WHEN @o1 < 128 THEN                 (@o1 * 16777216)             ELSE                 -(256 - @o1) * 16777216             END              SET @rv = @base +              (@o2 * 65536) +              (@o3 * 256) +             (@o4)     END     ELSE         SET @rv = -1     RETURN @rv ENDgo
调用范例：
select dbo.ipStringToInt('058.062.042.000')
整数转换如下：
CREATE FUNCTION dbo.ipIntToString (     @ip INT ) RETURNS CHAR(15) AS BEGIN     DECLARE @o1 INT,         @o2 INT,         @o3 INT,         @o4 INT      IF ABS(@ip) > 2147483647         RETURN '255.255.255.255'      SET @o1 = @ip / 16777216      IF @o1 = 0         SELECT @o1 = 255, @ip = @ip + 16777216      ELSE IF @o1 < 0     BEGIN         IF @ip % 16777216 = 0             SET @o1 = @o1 + 256         ELSE         BEGIN             SET @o1 = @o1 + 255             IF @o1 = 128                 SET @ip = @ip + 2147483648             ELSE                 SET @ip = @ip + (16777216 * (256 - @o1))         END     END     ELSE     BEGIN         SET @ip = @ip - (16777216 * @o1)     END      SET @ip = @ip % 16777216     SET @o2 = @ip / 65536     SET @ip = @ip % 65536     SET @o3 = @ip / 256     SET @ip = @ip % 256     SET @o4 = @ip      RETURN         CONVERT(VARCHAR(4), @o1) + '.' +         CONVERT(VARCHAR(4), @o2) + '.' +         CONVERT(VARCHAR(4), @o3) + '.' +         CONVERT(VARCHAR(4), @o4) ENDgo
调用范例：
select dbo.ipIntToString(977152512)
C# 的例子如下：
using System;using System.Net;namespace ConsoleApplication1{    class Program    {        static long ToInt(string addr)        {            return BitConverter.ToInt32(IPAddress.Parse(addr).GetAddressBytes(), 0);         }        static string ToAddr(long address)        {            return IPAddress.Parse(address.ToString()).ToString();            // This also works:             // return new IPAddress((uint) IPAddress.HostToNetworkOrder(             //    (int) address)).ToString();         }        static void Main(string[] args)        {            Console.WriteLine(ToInt("64.233.187.99"));            Console.WriteLine(ToAddr(1089059683));             Console.ReadLine();        }    }}
参考资料：
How should I store an IP address in SQL Server? 
http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html
How to convert an IPv4 address into a integer in C#? 
http://stackoverflow.com/questions/461742/how-to-convert-an-ipv4-address-into-a-integer-in-c