Type de données pour stocker l'adresse ip dans le serveur SQL
quel type de données dois-je choisir pour stocker une adresse IP dans un serveur SQL?
en sélectionnant le bon type de données serait-il assez facile de filtrer par adresse IP alors?
10 réponses
la façon techniquement correcte de stocker IPv4 est binaire(4), puisque c'est ce qu'il est réellement (non, pas même un INT32/INT(4)), la forme textuelle numérique que nous connaissons tous et aimons (255.255.255.255) étant juste la conversion d'affichage de son contenu binaire.
si vous le faites de cette façon, vous voudrez des fonctions pour convertir vers et à partir du format d'affichage textuel:
Voici comment convertir la forme d'affichage textuel en binaire:
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
et voici comment convertir le binaire retour à la forme d'affichage textuel:
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
voici une démonstration de comment les utiliser:
SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go
enfin, lorsque vous faites des recherches et des comparaisons, utilisez toujours la forme binaire si vous voulez être en mesure de tirer parti de vos index.
mise à JOUR:
je voulais ajouter qu'une façon de résoudre les problèmes de performance inhérents de l'UDF scalaire dans le serveur SQL, mais encore conserver le code-la réutilisation d'une fonction est d'utiliser un iTVF (inline table-valued function) à la place. Voici comment la première fonction ci-dessus (chaîne en binaire) peut être réécrite en 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
Voici l'exemple:
SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
et voici comment vous l'utiliseriez dans un encart
INSERT INTo myIpTable
SELECT {other_column_values,...},
(SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))
vous pouvez utiliser varchar. La longueur de L'IPv4 est statique, mais celle de L'IPv6 peut être très variable.
sauf si vous avez une bonne raison de le stocker en binaire, coller avec une chaîne de caractères (textuel) type.
voici du code pour convertir IPV4 ou IPv6 au format varchar en binaire(16) et retour. C'est la plus petite forme à laquelle je puisse penser. Il devrait bien indexer et fournir un moyen relativement facile de filtrer sur les sous-réseaux. Besoin de SQL Server 2005 ou plus tard. Je ne suis pas sûr que ce soit totalement pare-balles. Espérons que cette aide.
-- 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
comme je veux gérer à la fois IPv4 et IPv6 , j'utilise VARBINARY(16) et les fonctions suivantes SQL CLR pour convertir la text présentation de l'adresse IP en octets et l'inverse:
[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 utilise varchar(48) après SQL Server 2005 SP1. Semble assez bon pour moi surtout si vous voulez l'utiliser comparer à votre valeur.
de façon réaliste, vous ne verrez pas IPv6 comme mainstream pendant un certain temps encore, donc je préférerais la route 4 tinyint. En disant cela, j'utilise varchar (48) parce que je dois utiliser sys.dm_exec_connections ...
dans le cas Contraire. La réponse de Mark Redman mentionne une question antérieure de la série débat .
j'utilise habituellement un vieux filtre VARCHAR simple pour un IPAddress fonctionne très bien.
si vous voulez filtrer sur les plages D'adresses IP, je le diviserais en quatre entiers.
Merci RBarry. Je mets en place un système D'allocation de blocs IP et stocker en binaire est la seule façon de partir.
je stocke la représentation CIDR (ex: 192.168.1.0/24) du bloc IP dans un champ varchar, et en utilisant 2 champs calculés pour contenir la forme binaire du début et de la fin du bloc. A partir de là, je peux lancer des requêtes rapides pour voir si un bloc donné comme déjà attribué ou est libre d'attribuer.
j'ai modifié votre fonction pour calculer l'adresse IP finale comme suit:
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
pour les utilisateurs de .NET peut utiliser la classe IPAddress pour analyser la chaîne IPv4/IPv6 et la stocker comme une VARBINARY(16) . Peut utiliser la même classe pour convertir byte[] en chaîne. Si vous voulez convertir le VARBINARY en 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
j'aime les fonctions de SandRock. Mais j'ai trouvé une erreur dans le code de dbo.fn_ConvertIpAddressToBinary . Le paramètre entrant de @ipAddress VARCHAR (39) est trop petit quand vous concattez le @delim.
SET @ipAddress = @ipAddress + @delim
Vous pouvez l'augmenter à 40. Ou encore mieux, utilisez une nouvelle variable qui est plus grande et utilisez-la en interne. De cette façon, vous ne perdez pas la dernière paire sur les grands nombres.
SELECT dbo.fn_ConvertIpAddressToBinary('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff')
j'utilise varchar(15) jusqu'à présent, tout fonctionne pour moi. Insert, Update, Select. Je viens de lancer une application qui a des adresses IP, bien que je n'ai pas encore fait beaucoup de travail dev.
Voici la déclaration select:
select * From dbo.Server
where [IP] = ('132.46.151.181')
Go