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

2021-12-02 00:00:00 types ip-address sql-server-2005 tsql sql-server

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

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

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

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

推荐答案

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

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 its binary content).

如果你这样做，你会希望函数与文本显示格式相互转换:

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.

更新:

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

I wanted to add that one way to address the inherent performance problems of scalar UDFs 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:

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 中使用它的方法

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'))

相关文章