用于在SQL Server中存储ip地址的数据类型

What datatype should I choose for storing an IP Address in a SQL Server?

在SQL服务器中存储IP地址应该选择什么数据类型?

By selecting the right datatype would it be easy enough to filter by IP address then?

通过选择正确的数据类型，可以很容易地通过IP地址进行筛选吗?

10 个解决方案

#1

117

The technically correct way to store IPv4 is Binary(4), since that is what it actually is (no, not even an INT32/INT(4)), the numeric textual form that we all know and love (255.255.255.255) being just the display conversion of it's binary content.

从技术上来说，正确的IPv4存储方式是二进制(4)，因为它实际上就是二进制(不，甚至不是INT32/INT(4)))，我们都知道和喜欢的数字文本形式(255.255.255.255.255.255 .255)只是二进制内容的显示转换。

If you do it this way, you will want functions to convert to and from the textual-display format:

如果你这样做，你会想要函数转换到和从文本显示格式:

Here's how to convert the textual display form to binary:

下面介绍如何将文本显示形式转换为二进制形式:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
go

And here's how to convert the binary back to the textual display form:

下面是如何将二进制转换回文本显示形式:

CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @str AS VARCHAR(15) 

    SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );

    RETURN @str
END;
go

Here's a demo of how to use them:

下面是如何使用它们的演示:

SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go

SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go

Finally, when doing lookups and compares, always use the binary form if you want to be able to leverage your indexes.

最后，在进行查找和比较时，如果您希望能够利用索引，请始终使用二进制形式。

UPDATE:

更新:

I wanted to add that one way to address the inherent performance problems of scalar UDF's in SQL Server, but still retain the code-reuse of a function is to use an iTVF (inline table-valued function) instead. Here's how the first function above (string to binary) can be re-written as an iTVF:

我想添加一种方法来解决SQL Server中标量UDF固有的性能问题，但仍然保留函数的代码重用，即使用iTVF(内联表值函数)。下面是如何将上面的第一个函数(字符串到二进制)重写为iTVF:

CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE
AS RETURN (
    SELECT CAST(
               CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
                AS BINARY(4)) As bin
        )
go

Here's it in the example:

这个例子是这样的:

SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go

And here's how you would use it in an INSERT

这是如何在插入中使用它的方法。

INSERT INTo myIpTable
SELECT {other_column_values,...},
       (SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))

#2

You can use varchar. The length of IPv4 is static, but that of IPv6 may be highly variable.

您可以使用varchar。IPv4的长度是静态的，但是IPv6的长度是高度可变的。

Unless you have a good reason to store it as binary, stick with a string (textual) type.

除非您有很好的理由将它存储为二进制，否则请使用字符串(文本)类型。

#3

Here is some code to convert either IPV4 or IPv6 in varchar format to binary(16) and back. This is the smallest form I could think of. It should index well and provide a relatively easy way to filter on subnets. Requires SQL Server 2005 or later. Not sure it's totally bulletproof. Hope this helps.

这里有一些代码可以将varchar格式的IPV4或IPv6转换为二进制(16)并返回。这是我能想到的最小的形式。它应该能够很好地索引，并且提供了一种相对容易的在子网上进行过滤的方法。需要SQL Server 2005或更高版本。不确定它是否完全防弹。希望这个有帮助。

-- SELECT dbo.fn_ConvertIpAddressToBinary('2002:1ff:6c2::1ff:6c2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('10.4.46.2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('bogus')

ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary
(
     @ipAddress VARCHAR(39)
)
RETURNS BINARY(16) AS
BEGIN
DECLARE
     @bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2)
     , @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT
     , @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4)

SELECT
     @delim = '.'
     , @prevColIndex = 0
     , @limit = 4
     , @vbytes = 0x
     , @parts = 0
     , @colIndex = CHARINDEX(@delim, @ipAddress)

IF @colIndex = 0
     BEGIN
           SELECT
                @delim = ':'
                , @limit = 8
                , @colIndex = CHARINDEX(@delim, @ipAddress)
           WHILE @colIndex > 0
                SELECT
                      @parts = @parts + 1
                      , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
           SET @colIndex = CHARINDEX(@delim, @ipAddress)

           IF @colIndex = 0
                RETURN NULL     
     END

SET @ipAddress = @ipAddress + @delim

WHILE @colIndex > 0
     BEGIN
           SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1)

           IF @delim = ':'
                BEGIN
                      SET  @zone = RIGHT('0000' + @token, 4)

                      SELECT
                           @vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)')
                           , @vbytes = @vbytes + @vbzone

                      IF @token = ''
                           WHILE @parts + 1 < @limit
                                 SELECT
                                      @vbytes = @vbytes + @vbzone
                                      , @parts = @parts + 1
                END
           ELSE
                BEGIN
                      SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2)

                      SELECT
                           @vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)')
                           , @vbytes = @vbytes + @vbzone
                END

           SELECT
                @prevColIndex = @colIndex
                , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) 
     END            

SET @bytes =
     CASE @delim
           WHEN ':' THEN @vbytes
           ELSE 0x000000000000000000000000 + @vbytes
     END 

RETURN @bytes

END

-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)

ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress]
(
     @bytes BINARY(16)
)
RETURNS VARCHAR(39) AS
BEGIN
DECLARE
     @part VARBINARY(2)
     , @colIndex TINYINT
     , @ipAddress VARCHAR(39)

SET @ipAddress = ''

IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000
     BEGIN
           SET @colIndex = 13
           WHILE @colIndex <= 16
                SELECT
                      @part = SUBSTRING(@bytes, @colIndex, 1)
                      , @ipAddress = @ipAddress
                           + CAST(CAST(@part AS TINYINT) AS VARCHAR(3))
                           + CASE @colIndex WHEN 16 THEN '' ELSE '.' END
                      , @colIndex = @colIndex + 1

           IF @ipAddress = '0.0.0.1'
                SET @ipAddress = '::1'
     END
ELSE
     BEGIN
           SET @colIndex = 1
           WHILE @colIndex <= 16
                BEGIN
                      SET @part = SUBSTRING(@bytes, @colIndex, 2)
                      SELECT
                           @ipAddress = @ipAddress
                                 + CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)')
                                 + CASE @colIndex WHEN 15 THEN '' ELSE ':' END
                           , @colIndex = @colIndex + 2
                END
     END

RETURN @ipAddress   

END

#4

As I want to handle both IPv4 and IPv6, I am using VARBINARY(16) and the following SQL CLR functions to convert the text IP address presentation to bytes and the reverse:

由于我想同时处理IPv4和IPv6，我使用VARBINARY(16)和下面的SQL CLR函数将文本IP地址表示转换为bytes，反之亦然:

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBytes GetIPAddressBytesFromString (SqlString value)
{
    IPAddress IP;

    if (IPAddress.TryParse(value.Value, out IP))
    {
        return new SqlBytes(IP.GetAddressBytes());
    }
    else
    {
        return new SqlBytes();
    }
}


[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetIPAddressStringFromBytes(SqlBytes value)
{
    string output;

    if (value.IsNull)
    {
        output = "";
    }
    else
    {
        IPAddress IP = new IPAddress(value.Value);
        output = IP.ToString();
    }

    return new SqlString(output);
}

#5

sys.dm_exec_connections uses varchar(48) after SQL Server 2005 SP1. Sounds good enough for me especially if you want to use it compare to your value.

sys。dm_exec_connections在SQL Server 2005 SP1之后使用varchar(48)。听起来很不错，尤其是当你想用它来和你的价值作比较时。

Realistically, you won't see IPv6 as mainstream for a while yet, so I'd prefer the 4 tinyint route. Saying that, I'm using varchar(48) because I have to use sys.dm_exec_connections...

实际上，您暂时还不会将IPv6视为主流，所以我更喜欢4 tinyint路由。说到这里，我使用varchar(48)是因为我必须使用sys.dm_exec_connections…

Otherwise. Mark Redman's answer mentions a previous SO ~~debate~~ question.

否则。Mark Redman的回答提到了之前的SO辩论问题。

#6

I usually use a plain old VARCHAR filtering for an IPAddress works fine.

我通常使用一个普通的旧VARCHAR过滤IPAddress工作良好。

If you want to filter on ranges of IP address I'd break it into four integers.

如果你想要过滤IP地址的范围，我会把它分成四个整数。

#7

Thanks RBarry. I'm putting together an IP block allocation system and storing as binary is the only way to go.

谢谢RBarry。我正在构建一个IP块分配系统，将其存储为二进制是唯一的方法。

I'm storing the CIDR representation (ex: 192.168.1.0/24) of the IP block in a varchar field, and using 2 calculated fields to hold the binary form of the start and end of the block. From there, I can run fast queries to see if a given block as already been allocated or is free to assign.

我正在varchar字段中存储IP块的CIDR表示(例如:192.168.1.0/24)，并使用两个计算字段来保存块的开始和结束的二进制形式。从那里，我可以运行快速查询，查看给定的块是否已经被分配或是否可以*分配。

I modified your function to calculate the ending IP Address like so:

我修改了你的函数，这样计算结束IP地址:

CREATE FUNCTION dbo.fnDisplayIPv4End(@block AS VARCHAR(18)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)
    DECLARE @ip AS VARCHAR(15)
    DECLARE @size AS INT

    SELECT @ip = Left(@block, Len(@block)-3)
    SELECT @size = Right(@block, 2)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    SELECT @bin = CAST(@bin + POWER(2, 32-@size) AS BINARY(4))
    RETURN @bin
END;
go

#8

For people using .NET can use IPAddress class to parse IPv4/IPv6 string and store it as a VARBINARY(16). Can use the same class to convert byte[] to string. If want to convert the VARBINARY in SQL:

对于使用。net的人，可以使用IPAddress类解析IPv4/IPv6字符串并将其存储为VARBINARY(16)。可以使用相同的类将字节[]转换为字符串。如果要转换SQL中的VARBINARY:

--SELECT 
--  dbo.varbinaryToIpString(CAST(0x7F000001 AS VARBINARY(4))) IPv4,
--  dbo.varbinaryToIpString(CAST(0x20010DB885A3000000008A2E03707334 AS VARBINARY(16))) IPv6

--ALTER 
CREATE
FUNCTION dbo.varbinaryToIpString
(
    @varbinaryValue VARBINARY(16)
)
RETURNS VARCHAR(39)
AS
BEGIN
    IF @varbinaryValue IS NULL
        RETURN NULL
    IF DATALENGTH(@varbinaryValue) = 4
    BEGIN
        RETURN 
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 1, 1))) + '.' +
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 2, 1))) + '.' +
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 3, 1))) + '.' +
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 4, 1)))
    END
    IF DATALENGTH(@varbinaryValue) = 16
    BEGIN
        RETURN 
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  1, 2) + ':' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  3, 2) + ':' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  5, 2) + ':' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  7, 2) + ':' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  9, 2) + ':' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue, 11, 2) + ':' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue, 13, 2) + ':' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue, 15, 2)
    END

    RETURN 'Invalid'
END

#9

-1

I like the functions of SandRock. But I found an error in the code of dbo.fn_ConvertIpAddressToBinary. The incoming parameter of @ipAddress VARCHAR(39) is too small when you concat the @delim to it.

我喜欢沙岩的功能。但是我在dbo.fn_ConvertIpAddressToBinary代码中发现了一个错误。@ipAddress VARCHAR(39)的传入参数太小，当您将@delim合并到它时。

SET @ipAddress = @ipAddress + @delim

You can increase it to 40. Or better yet use a new variable that is bigger and use that internally. That way you don't lose the last pair on large numbers.

你可以把它增加到40。或者更好地使用一个更大的新变量，并在内部使用它。这样你就不会失去最后一对。

SELECT dbo.fn_ConvertIpAddressToBinary('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff')

#10

-1

I'm using Varchar(15) so far everything is working for me. Insert, Update, Select. I have just started an app that has IP Addresses, though I have not done much dev work yet.

我用的是Varchar(15)，到目前为止，一切都是为我工作。插入、更新选择。我刚刚启动了一个有IP地址的应用程序，尽管我还没有做太多的开发工作。

Here is the select statement:

下面是select语句:

Select * From dbo.Server where [IP] = ('132.46.151.181') Go

从dbo选择*。[IP] =('132.46.151.181')去的服务器

#1

117