Datentyp zum Speichern der IP-Adresse in SQL Server
Welchen Datentyp soll ich zum Speichern einer IP-Adresse in einem SQL Server auswählen?
Wäre es dann einfach genug, durch Auswahl des richtigen Datentyps nach IP-Adresse zu filtern?
Die technisch korrekte Art, IPv4 zu speichern, ist binär (4), da dies tatsächlich der Fall ist (nein, nicht einmal ein INT32/INT (4)). Die numerische Textform, die wir alle kennen und lieben (255.255.255.255), ist gerecht die Anzeigeumwandlung seines binären Inhalts).
Wenn Sie dies auf diese Weise tun, möchten Sie, dass Funktionen in das und aus dem Textanzeigeformat konvertiert werden:
So konvertieren Sie die Textanzeige in eine Binärdatei:
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
Und so konvertieren Sie die Binärdatei zurück in die Textanzeigeform:
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
Hier ist eine Demo, wie man sie benutzt:
SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go
Verwenden Sie beim Nachschlagen und Vergleichen immer die Binärform, wenn Sie Ihre Indizes nutzen möchten.
AKTUALISIEREN:
Ich wollte hinzufügen, dass eine Möglichkeit, um die inhärenten Leistungsprobleme von skalaren UDFs in SQL Server zu beheben, die Wiederverwendung von Code für eine Funktion jedoch weiterhin darin besteht, stattdessen eine iTVF-Funktion (Inline Table Valued Function) zu verwenden. So kann die erste Funktion oben (String in Binär) als iTVF umgeschrieben werden:
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
Hier ist es im Beispiel:
SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
Und so würden Sie es in einem INSERT verwenden
INSERT INTo myIpTable
SELECT {other_column_values,...},
(SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))
Sie können varchar verwenden. Die Länge von IPv4 ist statisch, aber die von IPv6 kann stark variieren.
Wenn Sie keinen guten Grund haben, es als Binärdatei zu speichern, bleiben Sie bei einem (textuellen) String-Typ.
Hier ist ein Code zum Konvertieren von IPV4 oder IPv6 im Varchar-Format in Binär (16) und zurück. Dies ist die kleinste Form, die ich mir vorstellen kann. Es sollte sich gut indizieren lassen und eine relativ einfache Möglichkeit zum Filtern nach Subnetzen bieten. Benötigt SQL Server 2005 oder höher. Ich bin mir nicht sicher, ob es absolut kugelsicher ist. Hoffe das hilft.
-- 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
Da ich sowohl IPv4 Als auch IPv6 Verarbeiten möchte, verwende ich VARBINARY(16) und die folgenden SQL CLR - Funktionen, um die text IP zu konvertieren Adressdarstellung in Bytes und umgekehrt:
[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 Verwendet varchar (48) nach SQL Server 2005 SP1. Klingt gut genug für mich, vor allem, wenn Sie es verwenden möchten, um Ihren Wert zu vergleichen.
Realistisch gesehen wird IPv6 noch eine Weile nicht als Mainstream angesehen, daher würde ich die Route 4 tinyint vorziehen. Wenn ich das sage, verwende ich varchar (48), weil ich sys.dm_exec_connections Verwenden muss ...
Andernfalls. Mark Redmans Antwort erwähnt eine vorherige SO debatte Frage.
Benutzer, die .NET verwenden, können die IPAddress-Klasse verwenden, um IPv4/IPv6-Zeichenfolgen zu analysieren und als VARBINARY(16) zu speichern. Kann dieselbe Klasse verwenden, um byte[] In einen String umzuwandeln. Wenn Sie das VARBINARY in SQL konvertieren möchten:
--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
Normalerweise verwende ich eine einfache alte VARCHAR-Filterung, damit eine IPAddress einwandfrei funktioniert.
Wenn Sie nach IP-Adressbereichen filtern möchten, würde ich diese in vier ganze Zahlen aufteilen.
Danke RBarry. Ich baue ein IP-Blockzuweisungssystem zusammen und binäres Speichern ist der einzige Weg.
Ich speichere die CIDR-Darstellung (z. B. 192.168.1.0/24) des IP-Blocks in einem varchar-Feld und verwende 2 berechnete Felder, um die Binärform von Anfang und Ende des Blocks zu speichern. Von dort aus kann ich schnell nachfragen, ob ein bestimmter Block bereits zugewiesen wurde oder frei zugewiesen werden kann.
Ich habe Ihre Funktion geändert, um die End-IP-Adresse wie folgt zu berechnen:
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, [email protected]) AS BINARY(4))
RETURN @bin
END;
go