SEARCH_IP¶
Sucht nach gültigen IPv4- und IPv6-Adressen in angegebenen Zeichenfolgen aus einer oder mehreren Tabellen, einschließlich Feldern in den Spalten VARIANT, OBJECT und ARRAY. Die Suche basiert auf einer einzelnen IP-Adresse oder einem Bereich von IP-Adressen, die Sie angeben. Wenn eine IP-Adresse in der Spalte oder im Feld stimmt mit einer angegebenen IP-Adresse übereinstimmt oder in einem bestimmten Bereich liegt, dann gibt die Funktion TRUE zurück.
Weitere Informationen zur Verwendung dieser Funktion finden Sie unter Verwenden der Volltextsuche.
Syntax¶
SEARCH_IP( <search_data>, '<search_string>' )
Argumente¶
search_data
Die Daten, die Sie suchen möchten, ausgedrückt als durch Kommas getrennte Listen von Zeichenfolgen, Spaltennamen oder Pfaden zu Feldern in VARIANT-Spalten. Die Suchdaten können auch aus einer einzelnen literalen Zeichenfolge bestehen, was nützlich für das Testen der Funktion sein kann.
Sie können das Platzhalterzeichen (
*
) angeben, wobei sich*
auf alle qualifizierten Spalten in allen Tabellen ausdehnt, die sich im Bereich der Funktion befinden. Qualifizierte Spalten sind diejenigen, die die Datentypen VARCHAR (Text), VARIANT, ARRAY und OBJECT haben. VARIANT-, ARRAY- und OBJECT-Daten werden für die Suche in Text umgewandelt. Sie können auch die Schlüsselwörter ILIKE und EXCLUDE zum Filtern verwenden.Weitere Informationen zu diesem Argument finden Sie in der Beschreibung
search_data
der SEARCH Funktion.'search_string'
Eine VARCHAR-Zeichenfolge, die eine der folgenden Adressen enthält:
Eine vollständige und gültige IP-Adresse im IPv4- oder IPv6-Standardformat , z. B.
192.0.2.1
oder2001:0db8:85a3:0000:0000:8a2e:0370:7334
.Eine gültige IP-Adresse im IPv4- oder IPv6-Standardformat mit einem Classless Inter-Domain Routing (CIDR)-Bereich, z. B.
192.0.2.1/24
oder2001:db8:85a3::/64
.Eine gültige IP-Adresse im IPv4- oder IPv6-Standardformat mit führenden Nullen, z. B.
192.000.002.001
anstelle von192.0.2.1
oder2001:0db8:85a3:0333:4444:8a2e:0370:7334
anstelle von2001:db8:85a3:333:4444:8a2e:370:7334
. Die Funktion akzeptiert bis zu drei Ziffern für jeden Teil eines IPv4-Adresse und bis zu vier Ziffern für jeden Teil einer IPv6-Adresse.Eine gültige komprimierte IPv6-Adresse, z. B.
2001:db8:85a3:0:0:0:0:0
oder2001:db8:85a3::
anstelle von2001:db8:85a3:0000:0000:0000:0000:0000
.Eine doppelte IPv6-Adresse, die eine IPv6-Adresse und eine IPv4-Adresse miteinander kombiniert, z. B.
2001:db8:85a3::192.0.2.1
.
Dieses Argument muss eine literale Zeichenfolge sein. Geben Sie ein Paar einfache Anführungszeichen um die Zeichenfolge an.
Die folgenden Arten von Argumenten werden nicht unterstützt:
Spaltennamen
Leere Zeichenfolgen
Mehr als eine IP-Adresse
Teilweise IPv4- und IPv6-Adressen
Rückgabewerte¶
Gibt einen BOOLEAN-Wert zurück:
Gibt TRUE zurück, wenn eine gültige IP-Adresse in
search_string
angegeben ist und eine passende IP-Adresse insearch_data
gefunden wird.Gibt TRUE zurück, wenn in
search_string
eine gültige IP-Adresse mit einem CIDR-Bereich angegeben ist und insearch_data
eine IP-Adresse in dem angegebenen Bereich gefunden wird.Gibt NULL zurück, wenn eines dieser Argumente NULL ist.
Andernfalls wird FALSE zurückgegeben.
Nutzungshinweise¶
Die SEARCH_IP-Funktion arbeitet nur mit VARCHAR (Text)-, VARIANT-, ARRAY- und OBJECT-Daten. Die Funktion gibt einen Fehler zurück, wenn das Argument
search_data
keine Daten dieser Datentypen enthält. Wenn das Argumentsearch_data
sowohl Daten von unterstützten als auch von nicht unterstützten Datentypen enthält, sucht die Funktion die Daten der unterstützten Datentypen und ignoriert die Daten der nicht unterstützten Datentypen. Weitere Beispiele finden Sie unter Beispiele für erwartete Fehlerfälle.Die Funktion gibt einen Fehler zurück, wenn das Argument
search_string
keine gültige IP-Adresse ist. Weitere Beispiele finden Sie unter Beispiele für erwartete Fehlerfälle.Sie können eine Optimierung der FULL_TEXT-Suche für Spalten hinzufügen, die das Ziel von SEARCH_IP-Funktionsaufrufen sind, indem Sie den Befehl ALTER TABLE verwenden, der die ENTITY_ANALYZER angibt. Beispiel:
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT( ipv4_source, ANALYZER => 'ENTITY_ANALYZER');
Der ENTITY_ANALYZER erkennt nur die Entitäten (z. B. IP-Adressen). Daher ist der Suchzugriffspfad in der Regel viel kleiner als die FULL_TEXT-Suchoptimierung mit einem anderen Analysator.
Weitere Informationen finden Sie unter Aktivieren der FULL_TEXT-Suchoptimierung.
Beispiele¶
Die folgenden Beispiele verwenden die Funktion SEARCH_IP.
Suche nach passenden IP-Adressen in VARCHAR-Spalten¶
Die folgenden Beispiele zeigen, wie Sie die Funktion SEARCH_IP verwenden, um VARCHAR (Text)-Spalten abzufragen.
Erstellen Sie zunächst eine Tabelle namens ipt
mit zwei Spalten, in denen IPv4 -Adressen gespeichert werden, und einer Spalte, in der IPv6-Adressen gespeichert werden:
CREATE OR REPLACE TABLE ipt(
id INT,
ipv4_source VARCHAR(20),
ipv4_target VARCHAR(20),
ipv6_target VARCHAR(40));
Fügen Sie zwei Zeilen in die Tabelle ein:
INSERT INTO ipt VALUES(
1,
'192.0.2.146',
'203.0.113.5',
'2001:0db8:85a3:0000:0000:8a2e:0370:7334');
INSERT INTO ipt VALUES(
2,
'192.0.2.111',
'192.000.002.146',
'2001:db8:1234::5678');
Fragen Sie die Tabelle ab:
SELECT * FROM ipt;
+----+-------------+-----------------+-----------------------------------------+
| ID | IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|----+-------------+-----------------+-----------------------------------------|
| 1 | 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
| 2 | 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+----+-------------+-----------------+-----------------------------------------+
In den folgenden Abschnitten werden Abfragen ausgeführt, die die SEARCH_IP-Funktion auf diese Tabellendaten anwenden:
Suchen Sie nach passenden IP -Adressen durch Verwendung der Funktion in einer SELECT-Liste
Suchen Sie nach passenden IP -Adressen durch Verwendung der Funktion in der WHERE-Klausel
Aktivieren Sie die FULL_TEXT-Suchoptimierung in VARCHAR-Spalten
Suchen Sie nach passenden IP -Adressen durch Verwendung der Funktion in einer SELECT-Liste¶
Führen Sie eine Abfrage aus, die die SEARCH_IP-Funktion in der SELECT -Liste verwendet, und durchsuchen Sie die drei VARCHAR-Spalten in der Tabelle:
SELECT ipv4_source,
ipv4_target,
ipv6_target,
SEARCH_IP((ipv4_source, ipv4_target, ipv6_target), '192.0.2.146') AS "Match found?"
FROM ipt
ORDER BY ipv4_source;
+-------------+-----------------+-----------------------------------------+--------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET | Match found? |
|-------------+-----------------+-----------------------------------------+--------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 | True |
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | True |
+-------------+-----------------+-----------------------------------------+--------------+
Beachten Sie, dass search_data
192.000.002.146
eine Übereinstimmung mit search_string
192.0.2.146
ist, obwohl 192.000.002.146
führende Nullen enthält.
Führen Sie eine Abfrage aus, die nach IPv6-Adressen sucht, die mit 2001:0db8:85a3:0000:0000:8a2e:0370:7334
übereinstimmen:
SELECT ipv4_source,
ipv4_target,
ipv6_target,
SEARCH_IP((ipv6_target), '2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS "Match found?"
FROM ipt
ORDER BY ipv4_source;
+-------------+-----------------+-----------------------------------------+--------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET | Match found? |
|-------------+-----------------+-----------------------------------------+--------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 | False |
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | True |
+-------------+-----------------+-----------------------------------------+--------------+
Die folgende Abfrage ist dieselbe wie die vorherige Abfrage, schließt aber die führenden Nullen und die Nullsegmente in search_string
aus:
SELECT ipv4_source,
ipv4_target,
ipv6_target,
SEARCH_IP((ipv6_target), '2001:db8:85a3::8a2e:370:7334') AS "Match found?"
FROM ipt
ORDER BY ipv4_source;
+-------------+-----------------+-----------------------------------------+--------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET | Match found? |
|-------------+-----------------+-----------------------------------------+--------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 | False |
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | True |
+-------------+-----------------+-----------------------------------------+--------------+
Die folgende Abfrage zeigt, dass ein search_string
durch einen CIDR-Bereich für IPv4-Adressen ersetzt wurde:
SELECT ipv4_source,
ipv4_target,
SEARCH_IP((ipv4_source, ipv4_target), '192.0.2.1/20') AS "Match found?"
FROM ipt
ORDER BY ipv4_source;
+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | True |
| 192.0.2.146 | 203.0.113.5 | True |
+-------------+-----------------+--------------+
Diese Abfrage zeigt, dass eine search_string
mit führenden Nullen True
für IPv4-Adressen zurückgibt, bei denen die führenden Nullen weggelassen wurden:
SELECT ipv4_source,
ipv4_target,
SEARCH_IP((ipv4_source, ipv4_target), '203.000.113.005') AS "Match found?"
FROM ipt
ORDER BY ipv4_source;
+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | False |
| 192.0.2.146 | 203.0.113.5 | True |
+-------------+-----------------+--------------+
Suchen Sie nach passenden IP -Adressen durch Verwendung der Funktion in der WHERE-Klausel¶
Die folgende Abfrage verwendet die Funktion in der WHERE-Klausel und sucht nur in der Spalte ipv4_target
.
SELECT ipv4_source,
ipv4_target,
ipv6_target
FROM ipt
WHERE SEARCH_IP(ipv4_target, '203.0.113.5')
ORDER BY ipv4_source;
+-------------+-------------+-----------------------------------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-------------+-----------------------------------------|
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
+-------------+-------------+-----------------------------------------+
Wenn die Funktion in der WHERE-Klausel verwendet wird und es keine Übereinstimmung gibt, werden keine Werte zurückgegeben:
SELECT ipv4_source,
ipv4_target,
ipv6_target
FROM ipt
WHERE SEARCH_IP(ipv4_target, '203.0.113.1')
ORDER BY ipv4_source;
+-------------+-------------+-------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-------------+-------------|
+-------------+-------------+-------------+
Die folgende Abfrage verwendet die Funktion in der WHERE-Klausel und sucht nur in der Spalte ipv6_target
.
SELECT ipv4_source,
ipv4_target,
ipv6_target
FROM ipt
WHERE SEARCH_IP(ipv6_target, '2001:db8:1234::5678')
ORDER BY ipv4_source;
+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+
Sie können das Zeichen *
(oder table.*
) als erstes Argument für die SEARCH-Funktion verwenden, wie im folgenden Beispiel gezeigt. Die Operation bezieht sich auf alle qualifizierten Spalten in der Tabelle, aus der Sie auswählen.
SELECT ipv4_source,
ipv4_target,
ipv6_target
FROM ipt
WHERE SEARCH_IP((*), '192.0.2.146')
ORDER BY ipv4_source;
+-------------+-----------------+-----------------------------------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-----------------+-----------------------------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
+-------------+-----------------+-----------------------------------------+
Sie können auch die Schlüsselwörter ILIKE und EXCLUDE zum Filtern verwenden. Weitere Informationen zu diesen Schlüsselwörtern finden Sie unter SELECT.
Die folgende Suche verwendet das Schlüsselwort ILIKE, um nur in Spalten zu suchen, die mit der Zeichenfolge _target
enden.
SELECT ipv4_source,
ipv4_target,
ipv6_target
FROM ipt
WHERE SEARCH_IP(* ILIKE '%_target', '192.0.2.146')
ORDER BY ipv4_source;
+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+
Aktivieren Sie die FULL_TEXT-Suchoptimierung in VARCHAR-Spalten¶
Um die Optimierung der FULL_TEXT-Suche für die Spalten der Tabelle ipt
zu aktivieren, führen Sie den folgenden ALTER TABLE Befehl aus:
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
ipv4_source,
ipv4_target,
ipv6_target,
ANALYZER => 'ENTITY_ANALYZER');
Bemerkung
Die von Ihnen angegebenen Spalten müssen VARCHAR oder VARIANT-Spalten sein. Spalten mit anderen Datentypen werden nicht unterstützt.
Suche nach passenden IP-Adressen in einer VARIANT-Spalte¶
Die folgenden Beispiele zeigen, wie Sie die Funktion SEARCH_IP verwenden, um VARIANT-Spalten abzufragen.
Das folgende Beispiel verwendet die Funktion SEARCH_IP, um einen Pfad zu einem Feld in einer VARIANT-Spalte zu suchen. Erstellen Sie eine Tabelle mit dem Namen iptv
und fügen Sie zwei Zeilen ein:
CREATE OR REPLACE TABLE iptv(ip1 VARIANT);
INSERT INTO iptv(ip1)
SELECT PARSE_JSON(' { "ipv1": "203.0.113.5", "ipv2": "203.0.113.5" } ');
INSERT INTO iptv(ip1)
SELECT PARSE_JSON(' { "ipv1": "192.0.2.146", "ipv2": "203.0.113.5" } ');
Führen Sie die folgenden Suchabfragen aus. Die erste Abfrage durchsucht nur das Feld ipv1
. Die zweite Abfrage durchsucht ipv1
und ipv2
.
SELECT * FROM iptv
WHERE SEARCH_IP((ip1:"ipv1"), '203.0.113.5');
+--------------------------+
| IP1 |
|--------------------------|
| { |
| "ipv1": "203.0.113.5", |
| "ipv2": "203.0.113.5" |
| } |
+--------------------------+
SELECT * FROM iptv
WHERE SEARCH_IP((ip1:"ipv1",ip1:"ipv2"), '203.0.113.5');
+--------------------------+
| IP1 |
|--------------------------|
| { |
| "ipv1": "203.0.113.5", |
| "ipv2": "203.0.113.5" |
| } |
| { |
| "ipv1": "192.0.2.146", |
| "ipv2": "203.0.113.5" |
| } |
+--------------------------+
Um die Optimierung der FULL_TEXT-Suche für diese ip1
VARIANT Spalte und ihre Felder zu aktivieren, führen Sie den folgenden ALTER TABLE-Befehl aus:
ALTER TABLE iptv ADD SEARCH OPTIMIZATION ON FULL_TEXT(
ip1:"ipv1",
ip1:"ipv2",
ANALYZER => 'ENTITY_ANALYZER');
Bemerkung
Die von Ihnen angegebenen Spalten müssen VARCHAR oder VARIANT-Spalten sein. Spalten mit anderen Datentypen werden nicht unterstützt.
Suche nach übereinstimmenden IP-Adressen in langen Textzeichenfolgen¶
Erstellen einer Tabelle namens ipt_log
und Einfügen von Zeilen:
CREATE OR REPLACE TABLE ipt_log(id INT, ip_request_log VARCHAR(200));
INSERT INTO ipt_log VALUES(1, 'Connection from IP address 192.0.2.146 succeeded.');
INSERT INTO ipt_log VALUES(2, 'Connection from IP address 203.0.113.5 failed.');
INSERT INTO ipt_log VALUES(3, 'Connection from IP address 192.0.2.146 dropped.');
Suchen Sie nach Protokolleinträgen in der Spalte ip_request_log
, die die IP-Adresse 192.0.2.146
enthalten:
SELECT * FROM ipt_log
WHERE SEARCH_IP(ip_request_log, '192.0.2.146')
ORDER BY id;
+----+---------------------------------------------------+
| ID | IP_REQUEST_LOG |
|----+---------------------------------------------------|
| 1 | Connection from IP address 192.0.2.146 succeeded. |
| 3 | Connection from IP address 192.0.2.146 dropped. |
+----+---------------------------------------------------+
Beispiele für erwartete Fehlerfälle¶
Die folgenden Beispiele zeigen Abfragen, die erwartete Syntaxfehler zurückgeben.
Das folgende Beispiel schlägt fehl, weil 5
kein unterstützter Datentyp für das Argument search_string
ist:
SELECT SEARCH_IP(ipv4_source, 5) FROM ipt;
001045 (22023): SQL compilation error:
argument needs to be a string: '1'
Das folgende Beispiel schlägt fehl, weil das Argument search_string
keine gültige IP-Adresse ist.
SELECT SEARCH_IP(ipv4_source, '1925.0.2.146') FROM ipt;
0000937 (22023): SQL compilation error: error line 1 at position 30
invalid argument for function [SEARCH_IP(IPT.IPV4_SOURCE, '1925.0.2.146')] unexpected argument [1925.0.2.146] at position 1,
Das folgende Beispiel schlägt fehl, weil das Argument search_string
eine leere Zeichenfolge ist.
SELECT SEARCH_IP(ipv4_source, '') FROM ipt;
000937 (22023): SQL compilation error: error line 1 at position 30
invalid argument for function [SEARCH_IP(IPT.IPV4_SOURCE, '')] unexpected argument [] at position 1,
Das folgende Beispiel schlägt fehl, weil für das Argument search_data
keine Spalten mit unterstützten Datentypen angegeben sind.
SELECT SEARCH_IP(id, '192.0.2.146') FROM ipt;
001173 (22023): SQL compilation error: error line 1 at position 7: Expected non-empty set of columns supporting full-text search.
Das folgende Beispiel ist erfolgreich, weil eine Spalte mit einem unterstützten Datentyp für das Argument search_data
angegeben ist. Die Funktion ignoriert die id
-Spalte, da es sich nicht um einen unterstützten Datentyp handelt:
SELECT SEARCH_IP((id, ipv4_source), '192.0.2.146') FROM ipt;
+---------------------------------------------+
| SEARCH_IP((ID, IPV4_SOURCE), '192.0.2.146') |
|---------------------------------------------|
| True |
| False |
+---------------------------------------------+