用于在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)ASBEGIN    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 @binENDgo以下是如何将二进制文件转换回文本显示形式：CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)ASBEGIN    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 @strEND;go这是一个如何使用它们的演示：SELECT dbo.fnBinaryIPv4('192.65.68.201')--should return 0xC04144C9goSELECT dbo.fnDisplayIPv4( 0xC04144C9 )-- should return '192.65.68.201'go最后，在进行查找和比较时，如果希望能够利用索引，请始终使用二进制形式。更新：我想补充一种方法来解决SQL Server中标量UDF的固有性能问题，但仍然保留函数的代码重用是使用iTVF（内联表值函数）。以下是如何将上面的第一个函数（字符串到二进制）重写为iTVF：CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLEAS 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 0xC04144C9go以下是如何在INSERT中使用它INSERT INTo myIpTableSELECT {other_column_values,...},       (SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))

Answer 2

以下是将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(&nbsp; &nbsp; &nbsp;@ipAddress VARCHAR(39))RETURNS BINARY(16) ASBEGINDECLARE&nbsp; &nbsp; &nbsp;@bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2)&nbsp; &nbsp; &nbsp;, @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT&nbsp; &nbsp; &nbsp;, @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4)SELECT&nbsp; &nbsp; &nbsp;@delim = '.'&nbsp; &nbsp; &nbsp;, @prevColIndex = 0&nbsp; &nbsp; &nbsp;, @limit = 4&nbsp; &nbsp; &nbsp;, @vbytes = 0x&nbsp; &nbsp; &nbsp;, @parts = 0&nbsp; &nbsp; &nbsp;, @colIndex = CHARINDEX(@delim, @ipAddress)IF @colIndex = 0&nbsp; &nbsp; &nbsp;BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @delim = ':'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , @limit = 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , @colIndex = CHARINDEX(@delim, @ipAddress)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHILE @colIndex > 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @parts = @parts + 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @colIndex = CHARINDEX(@delim, @ipAddress)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF @colIndex = 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RETURN NULL&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;ENDSET @ipAddress = @ipAddress + @delimWHILE @colIndex > 0&nbsp; &nbsp; &nbsp;BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF @delim = ':'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET&nbsp; @zone = RIGHT('0000' + @token, 4)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, @vbytes = @vbytes + @vbzone&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF @token = ''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHILE @parts + 1 < @limit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @vbytes = @vbytes + @vbzone&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , @parts = @parts + 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ELSE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, @vbytes = @vbytes + @vbzone&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @prevColIndex = @colIndex&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)&nbsp;&nbsp; &nbsp; &nbsp;END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SET @bytes =&nbsp; &nbsp; &nbsp;CASE @delim&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHEN ':' THEN @vbytes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ELSE 0x000000000000000000000000 + @vbytes&nbsp; &nbsp; &nbsp;END&nbsp;RETURN @bytesEND-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress](&nbsp; &nbsp; &nbsp;@bytes BINARY(16))RETURNS VARCHAR(39) ASBEGINDECLARE&nbsp; &nbsp; &nbsp;@part VARBINARY(2)&nbsp; &nbsp; &nbsp;, @colIndex TINYINT&nbsp; &nbsp; &nbsp;, @ipAddress VARCHAR(39)SET @ipAddress = ''IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000&nbsp; &nbsp; &nbsp;BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @colIndex = 13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHILE @colIndex <= 16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @part = SUBSTRING(@bytes, @colIndex, 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , @ipAddress = @ipAddress&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;+ CAST(CAST(@part AS TINYINT) AS VARCHAR(3))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;+ CASE @colIndex WHEN 16 THEN '' ELSE '.' END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , @colIndex = @colIndex + 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF @ipAddress = '0.0.0.1'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @ipAddress = '::1'&nbsp; &nbsp; &nbsp;ENDELSE&nbsp; &nbsp; &nbsp;BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @colIndex = 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHILE @colIndex <= 16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @part = SUBSTRING(@bytes, @colIndex, 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@ipAddress = @ipAddress&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;+ CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;+ CASE @colIndex WHEN 15 THEN '' ELSE ':' END&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, @colIndex = @colIndex + 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END&nbsp; &nbsp; &nbsp;ENDRETURN @ipAddress&nbsp; &nbsp;END&nbsp;