Tipo de dados para armazenar o endereço IP no SQL Server

Que tipo de dados devo escolher para armazenar um endereço IP em um SQL Server?

Ao selecionar o tipo de dados correto, seria fácil filtrar por endereço IP?

A maneira tecnicamente correta de armazenar o IPv4 é Binário (4), pois é o que realmente é (não, nem mesmo um INT32 / INT (4)), a forma textual numérica que todos conhecemos e amamos (255.255.255.255) apenas a conversão de exibição do seu conteúdo binário.

Se você fizer dessa maneira, você desejará que as funções sejam convertidas para e do formato de exibição textual:

Veja como converter o formulário de exibição textual em binário:

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 

E aqui está como converter o binário de volta para o formulário de exibição textual:

 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 

Aqui está uma demonstração de como usá-los:

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

Finalmente, ao fazer pesquisas e comparações, sempre use o formulário binário se quiser aproveitar seus índices.


ATUALIZAR:

Eu queria acrescentar que uma maneira de resolver os problemas de desempenho inerentes das UDFs escalares no SQL Server, mas ainda manter a reutilização de código de uma function, é usar uma iTVF (function de valor de tabela embutida). Veja como a primeira function acima (string para binário) pode ser reescrita como um 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 

Aqui está no exemplo:

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

E aqui está como você iria usá-lo em um INSERT

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

Você pode usar varchar. O comprimento do IPv4 é estático, mas o do IPv6 pode ser altamente variável.

A menos que você tenha um bom motivo para armazená-lo como binário, use um tipo de string (textual).

Aqui está algum código para converter IPV4 ou IPv6 no formato varchar para binário (16) e vice-versa. Esta é a menor forma que eu poderia pensar. Deve indexar bem e fornecer uma maneira relativamente fácil de filtrar sub-redes. Requer o SQL Server 2005 ou posterior. Não tenho certeza se é totalmente à prova de balas. Espero que isto ajude.

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

Como eu quero lidar com IPv4 e IPv6 , estou usando VARBINARY(16) e as seguintes funções SQL CLR para converter a apresentação do endereço IP de text em bytes e o inverso:

 [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); } 

sys.dm_exec_connections usa varchar (48) após o SQL Server 2005 SP1. Parece bom o suficiente para mim, especialmente se você quiser usá-lo em comparação com o seu valor.

Realisticamente, você não verá IPv6 como mainstream por um tempo ainda, então eu prefiro a rota 4 tinyint. Dizendo isso, estou usando varchar (48) porque eu tenho que usar sys.dm_exec_connections

De outra forma. A resposta de Mark Redman menciona uma pergunta prévia sobre o debate da SO.

Eu costumo usar uma filtragem antiga VARCHAR para um IPAddress funciona bem.

Se você quiser filtrar os intervalos de endereços IP, dividi-los em quatro inteiros.

Obrigado RBarry. Estou montando um sistema de alocação de blocos IP e armazenando como binário é o único caminho a percorrer.

Estou armazenando a representação CIDR (ex: 192.168.1.0/24) do bloco IP em um campo varchar e usando 2 campos calculados para manter a forma binária do início e fim do bloco. De lá, posso executar consultas rápidas para ver se um determinado bloco já foi alocado ou está livre para atribuir.

Eu modifiquei sua function para calcular o endereço IP final assim:

 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 

Para pessoas que usam o .NET, é possível usar a class IPAddress para analisar a cadeia IPv4 / IPv6 e armazená-la como um VARBINARY(16) . Pode usar a mesma class para converter byte[] em string. Se quiser converter o VARBINARY em SQL:

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

Eu gosto das funções do SandRock. Mas eu encontrei um erro no código do dbo.fn_ConvertIpAddressToBinary . O parâmetro de input de @ipAddress VARCHAR (39) é muito pequeno quando você concatina @delim com ele.

 SET @ipAddress = @ipAddress + @delim 

Você pode aumentá-lo para 40. Ou melhor ainda, usar uma nova variável maior e usá-la internamente. Dessa forma você não perde o último par em grandes números.

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

Estou usando Varchar (15) até agora tudo está funcionando para mim. Inserir, Atualizar, Selecionar. Acabei de iniciar um aplicativo que possui endereços IP, embora ainda não tenha feito muito trabalho de desenvolvimento.

Aqui está a declaração select:

Selecione * De dbo.Server onde [IP] = (‘132.46.151.181’) Ir