sql-server - SQL Server 中存储 IP 地址的数据类型

Question

我应该选择什么数据类型来在 SQL Server 中存储 IP 地址？

通过选择正确的数据类型，按 IP 地址过滤是否足够容易？

Answer 1

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

如果您这样做，您将需要函数在文本显示格式之间进行转换:

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

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

以下是将二进制文件转换回文本显示形式的方法:

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

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

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

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

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

<小时/>

更新:

我想补充一点，解决 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

示例如下:

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

以下是如何在 INSERT 中使用它

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