存储IP地址的数据类型在SQL Server中的问题出现的原因是需要将IP地址从字符串格式转换为二进制格式，并且需要将二进制格式的IP地址转换回字符串格式。下面的代码提供了这两个功能的实现。解决方法是使用自定义函数，其中包含了将IP地址转换为二进制格式的函数和将二进制格式的IP地址转换回字符串格式的函数。

代码中的第一个函数是fn_ConvertIpAddressToBinary函数，它接受一个IP地址的字符串作为输入，并将其转换为二进制格式的IP地址。该函数首先检查输入字符串中的分隔符是“.”还是“:”，然后根据不同的情况进行处理。如果分隔符是“.”，则函数将IP地址字符串拆分为四个部分，并将每个部分转换为十进制数，然后将这些数转换为十六进制，并最终将它们组合成一个二进制格式的IP地址。如果分隔符是“:”，则函数将IP地址字符串拆分为八个部分，并将每个部分转换为十六进制，并最终将它们组合成一个二进制格式的IP地址。该函数返回一个二进制(16)的结果。

代码中的第二个函数是fn_ConvertBinaryToIpAddress函数，它接受一个二进制格式的IP地址作为输入，并将其转换为字符串格式的IP地址。该函数首先检查二进制格式的IP地址的前12个字节是否为零，如果是，则将其视为IPv4地址，否则将其视为IPv6地址。如果是IPv4地址，则函数将后四个字节转换为十进制数，并将其组合成一个字符串格式的IP地址。如果是IPv6地址，则函数将每两个字节转换为十六进制，并将其组合成一个字符串格式的IP地址。该函数返回一个字符串(39)的结果。

这两个函数可以用于在SQL Server中存储和处理IP地址，可以通过调用相应的函数来进行转换操作。这样可以方便地将IP地址存储在数据库中，并且可以使用这些转换函数进行过滤和查询操作。

需要注意的是，代码中的fn_ConvertIpAddressToBinary函数存在一个bug，可以参考相关的答案进行修复。

在SQL Server中存储IP地址的数据类型问题

在SQL Server中存储IP地址时，我们需要选择合适的数据类型。下面是关于这个问题的内容：

1. 我们可以使用varchar类型。IPv4的长度是固定的，但IPv6的长度可能会变化很大。

2. 除非有充分的理由将其存储为二进制，否则应使用字符串类型。

3. IPv6的长度非常固定-128位。

4. 除非涉及到人类无法阅读的数据或大量数据，否则这是最好的答案。

5. 使用二进制而不是字符串的一个简单原因是：二进制版本允许对IP地址进行数值范围检查！文本版本则不行。当然，这取决于所需的用途，但二进制数字更有用，因为它们具有实际意义。

6. varchar在数据库中占用的空间要多得多。一个32位的IPv4地址在数值存储上占用4个字节，而一个128位的IPv6地址在数值存储上占用16个字节。与此同时，作为字符串，这个IPv4地址需要15个字节来存储，而IPv6地址作为字符串的长度可能高达39个字节。

7. varbinary(16)是最佳选择。

8. IPv4-mapped IPv6地址（45个字符）：ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:192.168.158.190

我们可以选择使用varchar或varbinary(16)来存储IP地址。varchar类型在存储空间方面更占用，但更易于阅读和处理。而varbinary(16)类型则更节省存储空间，并且可以进行数值范围检查。具体选择哪种类型取决于具体的需求和使用场景。

在SQL Server中存储IP地址的数据类型

在SQL Server中，正确存储IPv4地址的方法是使用binary(4)数据类型，因为这就是它的实际形式（不是INT32/INT(4)，我们熟悉的数字文本形式255.255.255.255只是其二进制内容的显示转换）。

如果按照这种方式进行存储，你将需要一些函数来在文本显示格式和二进制格式之间进行转换：

以下是将文本显示格式转换为二进制格式的方法：

CREATE FUNCTION dbo.fnBinaryIPv4( AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE  AS BINARY(4)
    SELECT  = CAST( CAST( PARSENAME( , 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( , 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( , 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( , 1 ) AS INTEGER) AS BINARY(1))
    RETURN 
END
go

以下是将二进制格式转换回文本显示格式的方法：

CREATE FUNCTION dbo.fnDisplayIPv4( AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE  AS VARCHAR(15) 
    SELECT  = CAST( CAST( SUBSTRING( , 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( , 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( , 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( , 4, 1) AS INTEGER) AS VARCHAR(3) );
    RETURN 
END;
go

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

SELECT dbo.fnBinaryIPv4('192.65.68.201')
--应返回0xC04144C9
go
SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
--应返回'192.65.68.201'
go

最后，当进行查找和比较时，如果想要利用索引，始终使用二进制形式。

更新：

我想补充一点，解决SQL Server中标量UDF的性能问题，但仍然保留函数的代码重用性的方法是使用iTVF（内联表值函数）。以下是如何将上面的第一个函数（字符串转换为二进制）重写为iTVF的方法：

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

这是一个示例：

SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--应返回0xC04144C9
go

这是如何在INSERT中使用它的示例：

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

我认为这只在学术意义上是正确的。如果不知道帖子发布者尝试解决的目标和领域问题，我怀疑这样做会不必要地复杂化与数据的交互，并且可能会降低性能。

IPv4是一个有序的四个字节的序列。这是它的领域，以及存储格式是BIN(4)。存储格式不会影响性能，因为它是最佳格式。转换函数可能会影响性能（因为SQL Server上的UDF很差），可以通过内联或在客户端上进行转换来解决。最后，这种方法的重要优势是可以使用索引范围扫描在Class 1、2或3子网络中搜索地址（WHERE ip BETWEEN fnBinaryIPv4('132.31.55.00') AND fnBinaryIPv4('132.31.55.255')）。

我会将其存储为整数。你能解释存储为二进制的性能优势吗？

1）请参见前面的评论，以获取一个示例，2）我没有声称Binary会比Integer更快。我声称A）这是正确的格式（确实如此），B）它不会更慢。

我想知道你对这个页面的最后两个帖子有什么评论：social.msdn.microsoft.com/Forums/en-US/transactsql/thread/…

如果我有评论，我会在那里发表。虽然我真的看不出丹（Dan）说的和我上面说的有什么不同。

纠正我如果我错了，但我的意思是丹说对于SQL Server来说，处理int更容易，而你说int和binary在SQL Server处理方面没有区别。

是的，你错了，丹没有这么说。此外，这不是一个讨论论坛，也不适合这样。Stackoverflow是一个问答论坛，如果你有问题，请单独提问。

晚了一步，但我想补充一点。将其存储为BINARY(4)会使对存储的IP进行子网掩码处理变得困难。如果将数据编码为INTEGER，可以使用位掩码运算符进行位掩码处理。尽管这意味着从128.0.0.0到255.255.255.255的IP将存储为负整数，但这不会影响位AND运算。在上面列出的函数中添加一步将BINARY(4)转换为INTEGER是一个微不足道的过程。

（是你吗，布莱恩？）是的，确实如此，由于未知的原因，SQL Server只允许在位运算符的一侧使用二进制字符串，并且只返回数字作为结果。另一方面，字符串运算符和函数可以在它们上工作，所以如上所述，你可以更容易地进行网络类和子网搜索，甚至获得对其的索引支持。

我遇到了一个问题，可能很愚蠢，但想在这里提一下，因为我没有看到它被解决：INSERT INTO Table (IP, url) VALUES(SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'),'/TEST/')出现语法错误。但是当我执行INSERT INTO Table(IP) SELECT bin FROM...时，它就可以工作。当我尝试插入其他值时，我做错了什么？

这与本主题无关，但是简短的答案是，它要么是INSERT INTO.. VALUES..，要么是INSERT INTO.. SELECT..，不能同时使用两者。如果需要更多帮助，请将其作为单独的问题提问。

BINARY的另一个问题：Intel处理器是小端字节序，但在网络中，IP是大端字节序。这就是为什么PowerShell表达式（[ipaddress]'10.20.30.41'）。Address -（[ipaddress]'10.20.30.40'）。Address的结果是2 ^ 24。字节顺序颠倒。这意味着你可以将地址存储为操作系统将其表示的方式，或者将地址存储为网络将其表示的方式，但不能同时存储两者。

IPv4地址是32位无符号整数。四个字节只是32位数字的一种表示形式，方便人类阅读。所以我总是以此方式存储它们。如果客户端使用php，可以使用long2ip函数；C#有IPAddress类。没有必要重新发明轮子。

没有任何理由这样做，而不是将它们存储为整数。IP地址的值是D + C * 2^8 + B * 2^16 + A * 2^24 - 数值存储也有优势，因为可以对IP地址进行数学运算以计算子网等等。

dev.mysql.com/doc/refman/5.7/en/… dev.mysql.com/doc/refman/5.7/en/…