Geben Sie einen Subnetzbereich und eine Liste von IPs an und wählen Sie alle Zeilen aus, zwischen denen die IPs liegen
Ich arbeite daran, verschiedene Aspekte eines Netzwerks in einer Datenbank zu modellieren. Eines der nervigeren Probleme, die wir behandeln, besteht darin, Subnetzbereiche zu erstellen und dann zu bestimmen, ob ein bestimmter Satz von IPs innerhalb dieser Bereiche liegt. Unser aktuelles Modell berücksichtigt die Unterschiede zwischen IPv4 und IPv6 mit den folgenden Spalten:
%Vor% Das obige Schema macht einige Annahmen, die wichtig sind, um darauf hinzuweisen. Wir verwenden vollständig erweiterte IPs ( 192.168.001.001 vs. 192.168.1.1 ) für Speicherung und Vergleich. Wir haben diese Entscheidung aufgrund der Probleme getroffen, die mit dem Speichern IPv6-Adressen im SQL-Server zusammenhängen (Bigints sind unsigniert, was bedeutet, dass wir sie machen müssten) Verwendung von sechs Spalten zur Darstellung von IPv6).
In Anbetracht dieses Tabellenschemas ist es ziemlich einfach, one select-Anweisungen zu schreiben, um zu bestimmen, ob eine IP eines beliebigen Typs zwischen den Bereichen in der Tabelle liegt:
%Vor%Was ist schwieriger ist eine Liste von IPs gegeben, bestimmen Sie, welche von denen zwischen den Subnetzbereichen sind. Die Liste der IPs wird durch Benutzereingaben bereitgestellt und in der Datenbank nicht gespeichert. Offensichtlich kann ich für Daten, die in der Datenbank gespeichert sind, einen ähnlichen Join wie im folgenden Beispiel durchführen.
Zum Beispiel könnte ein Benutzer 1234:0000:0000:0000:fc12:00ab:0042:1050 , 192.168.001.001 und 192.168.1.1 angeben. Die einzige Lösung, die ich mir ausgedacht habe, ist die Verwendung einer Tabelle. bewertete Funktion, um eine Liste von IPs zu teilen und einen Join mit einem between:
Bei der Verwendung einer Split-Funktion fühlt es sich hacky an. Ich verbrachte den besseren Teil des Morgens damit, allgemeine Tabellenausdrücke zu schnüffeln >, konnte aber nicht an eine Implementierung denken, die funktionieren würde. Idealerweise würde eine einzelne Auswahl bestimmen, ob eine gegebene Zeichenfolge von den IPv4- oder IPv6-Spalten zurückgeworfen werden soll, aber wenn dies nicht möglich ist, kann ich die Liste trennen, bevor die Sammlung von IPs an die Datenbank übergeben wird.
Um die Antwort zu vereinfachen, habe ich eine SQL Fiddle erstellt. Gibt es einen Mechanismus in SQL (ich würde lieber nicht T-SQL verwenden) angesichts einer Liste von IPs zu bestimmen, welche bestehenden Subnetzbereiche diese IPs zwischen fallen? Ist das obige Schema sogar der richtige Ansatz für das Problem, würde ein anderes Datenmodell zu einer einfacheren Lösung führen?
4 Antworten
Dies ist keine vollständige Lösung, sondern eher eine Idee zu einem anderen Design, Ich dachte, anstatt einen typischen SQL-Vergleich zu machen, warum nicht versuchen, einen logischen Vergleich zu verwenden. Da ich sehr wenig von der SQL-Implementierung kenne, habe ich versucht, mit bitweisem Vergleich herumzualbern (mit bigint)
Es gibt viel Optimierung, aber ich denke, es gibt eine Möglichkeit, dass es helfen könnte,
eine kleine Demonstration, wo ich 4 IPs (192.168.1.1 und 3 mehr) vergleiche, ich benutze sie als bigints, weil ein int zu klein ist, und ich muss den logischen bitweisen Vergleich verwenden, (mehr Infos hier Ссылка )
%Vor%wie Sie sehen können (UND / & Amp;) die IP und die Netzwerkadresse dann vergleiche ich das mit der Netzwerkadresse, wenn es eine Übereinstimmung ist, fällt es in diesen Bereich
korrigiere mich, wenn ich falsch liege, ich muss mehr darüber nachdenken, sehr interessante Sachen in der Tat
Bearbeiten: Wie unten erwähnt, ist Bigint zu klein für IPv6, das funktioniert leider nicht, die bitweise (UND) Operation kann nicht mit binärem Datentyp durchgeführt werden, es werden nur ganzzahlige Typen akzeptiert ...
Ich habe Ihre SQL Fiddle betrachtet, Herumspielen mit der fraglichen Abfrage,
Um 100% klar zu sein, benötigen Sie eine Abfrage, um alle Bereiche zu finden, in die eine Liste von Host-Adressen fällt.
so können Sie so tun, als wären Ihre Hosts eine Liste / Tabelle von Daten, und dann innere Subnetze verbinden (oder Links beitreten, wenn sie auch ohne Subnetz angezeigt werden soll)
%Vor%Ich habe 4 Ergebnisse (es gab zwei Subnetze, die jedem Datensatz entsprachen)
Haben Sie darüber nachgedacht, sowohl das ipv6- als auch das ipv4-Format in einer Spalte zu speichern?
Speichern von IP-Adressen in Microsoft SQL Server
Es würde eine Konvertierung der willkürlichen Quelldaten für Ihren Vergleich erfordern (oder andersherum), aber Sie könnten zumindest vermeiden, dass Sie zwei separate Abfragen benötigen, um zu überprüfen.
Ich würde dann geneigt sein, ein CTE aus Ihren Quelldaten zu bilden (FOR XML?) und dann mit Ihrer Datenbanktabelle (Subnetz) zu verbinden.
Ich würde dieses Problem meist mit [xml] oder [heirarchyid] ( Ссылка ) und behandle die Daten als Baum. Es wird relativ einfach, einen Baum zu erstellen, der auf vorhandenen Subnetzen mit einer [tree]. [Run] @subnet-Methode basiert, die die Baumstruktur ausführt und den Knoten findet, der @subnet entspricht. Durch die Behandlung der Daten als Baum (was tatsächlich der Fall ist) und den Aufbau rekursiver Baumbaumethoden, sollten Sie in der Lage sein, leicht zu einem Punkt zu gelangen, an dem Sie einen Knoten finden, wenn er existiert oder ihn einfügen und den nächsten erhalten vorherige Knoten.
Ich kann detailliertere Beispiele nennen, wenn das von Interesse ist, aber es ist keine triviale Lösung, deshalb werde ich die Zeit nicht damit verbringen. Was ich hier zeige, ist ein einfacher Prototyp, der den Knoten findet, der der Elternteil (als einfache Übereinstimmung) der Eingabemaske ist. Ich gebe dies nur als Beispiel an, aber wenn die Lösung Sie interessiert, kann ich Ihnen mehr Details liefern oder Sie können leicht sehen, wie Sie mit diesen Techniken eine Lösung aufbauen können.
Frieden, Katherine
%Vor%Tags und Links sql sql-server ipv6 ipv4