wake-up-neo.com

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?

105
OrElse

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'))
124
RBarryYoung

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.

23
user151323

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 
16
Jerry Birchler

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);
}
8
gotqn

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.

7
gbn

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
6
M. Turnhout

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.

2
Daniel Elliott

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