SEARCH_IP¶

从一个或多个表中搜索指定字符串列中的有效 IPv4 和 IPv6 地址，包括 VARIANT、OBJECT 和 ARRAY 列中的字段。搜索基于单个 IP 地址或您指定的 IP 地址范围。如果列或字段中的 IP 地址与指定的 IP 地址匹配或位于指定此范围内，则该函数会返回 TRUE。

有关使用此函数的更多信息，请参阅使用全文搜索。

语法¶

SEARCH_IP( <search_data>, '<search_string>' )

Copy

实参¶

search_data

您要搜索的数据，以字符串字面量、列名或到 VARIANT 列中字段的路径的逗号分隔的列表表示。搜索数据也可以是一个单一的字面量字符串，这在测试函数时可能很有用。

您可以指定通配符字符 (*)，其中 * 扩展为函数范围内所有表中所有符合条件的列。符合条件的列是那些具有 VARCHAR（文本）、VARIANT、ARRAY 和 OBJECT 数据类型的列。VARIANT、ARRAY 和 OBJECT 数据已转换为文本以便搜索。您还可以使用 ILIKE 和 EXCLUDE 关键字进行筛选。

有关该实参的更多信息，请参阅 SEARCH 函数的 search_data 描述。

'search_string'

包含以下地址之一的 VARCHAR 字符串：

标准 IPv4 或 IPv6 格式的完整有效 IP 地址，如 192.0.2.1 或 2001:0db8:85a3:0000:0000:8a2e:0370:7334。
标准 IPv4 或 IPv6 格式的有效 IP 地址，其中包含无类域间路由 (CIDR) 范围，如 192.0.2.1/24 或 2001:db8:85a3::/64。
标准 IPv4 或 IPv6 格式的有效 IP 地址，其中包含前导零，如 192.000.002.001``（而不是 ``192.0.2.1）或 2001:0db8:85a3:0333:4444:8a2e:0370:7334``（而不是 ``2001:db8:85a3:333:4444:8a2e:370:7334）。该函数支持 IPv4 地址每个部分最多输入三位数字，IPv6 地址每个部分最多输入四位数字。
有效的压缩 IPv6 地址，例如 2001:db8:85a3:0:0:0:0:0 或 2001:db8:85a3::``（而不是 ``2001:db8:85a3:0000:0000:0000:0000:0000）。
组合了 IPv6 地址和 IPv4 地址的 IPv6 双栈地址，例如 2001:db8:85a3::192.0.2.1。

该实参必须是字面量字符串。指定一对单引号将字符串放在引号里。

不支持以下类型的实参：

列名称
空字符串
多个 IP 地址
部分 IPv4 地址和 IPv6 地址

返回¶

返回 BOOLEAN：

如果在 search_string 中指定了有效的 IP 地址，并且在 search_data 中找到了匹配的 IP 地址，则返回 TRUE。
如果在 search_string 中指定了范围为 CIDR 的有效 IP 地址，并且在 search_data 中找到了指定范围内的 IP 地址，则返回 TRUE。
如果任一实参为 NULL，则返回 NULL。
否则返回 FALSE。

使用说明¶

SEARCH_IP 函数仅针对 VARCHAR（文本）、VARIANT、ARRAY 和 OBJECT 数据执行操作。如果 search_data 实参不包含这些数据类型的数据，则该函数会返回错误。当 search_data 实参同时包含支持的数据类型和不支持的数据类型时，函数会搜索支持的数据类型的数据，并静默忽略不支持的数据类型的数据。有关示例，请参阅预期错误案例的示例。
如果 search_string 实参不是有效的 IP 地址，则该函数将返回错误。有关示例，请参阅预期错误案例的示例。
您可以使用指定 ENTITY_ANALYZER 的 ALTER TABLE 命令在作为 SEARCH_IP 函数调用目标的列上添加 FULL_TEXT 搜索优化。例如：
```
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ipv4_source,
  ANALYZER => 'ENTITY_ANALYZER');
```
Copy
ENTITY_ANALYZER 仅识别实体（例如，IP 地址）。因此，搜索访问路径通常比使用不同分析器的 FULL_TEXT 搜索优化要小得多。

有关更多信息，请参阅启用 FULL_TEXT 搜索优化。

示例¶

以下示例使用 SEARCH_IP 函数：

在 VARCHAR 列中搜索匹配的 IP 地址
在 VARIANT 列中搜索匹配的 IP 地址
在文本的长字符串中搜索匹配的 IP 地址
预期错误案例的示例

在 VARCHAR 列中搜索匹配的 IP 地址¶

以下示例展示了如何使用 SEARCH_IP 函数查询 VARCHAR（文本）列。

首先，创建一个名为 ipt 的表，该表有两列存储 IPv4 地址，一列存储 IPv6 地址：

CREATE OR REPLACE TABLE ipt(
  id INT,
  ipv4_source VARCHAR(20),
  ipv4_target VARCHAR(20),
  ipv6_target VARCHAR(40));

Copy

将两行插入表中：

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');

Copy

查询表：

SELECT * FROM ipt;

Copy

+----+-------------+-----------------+-----------------------------------------+
| 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                     |
+----+-------------+-----------------+-----------------------------------------+

以下部分针对该表数据运行使用 SEARCH_IP 函数的查询：

使用 SELECT 列表中的函数搜索匹配的 IP 地址
通过在 WHERE 子句中使用函数来搜索匹配的 IP 地址
对 VARCHAR 列启用 FULL_TEXT 搜索优化

使用 SELECT 列表中的函数搜索匹配的 IP 地址¶

运行使用 SELECT 列表中的 SEARCH_IP 函数的查询并搜索表中的三个 VARCHAR 列：

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;

Copy

+-------------+-----------------+-----------------------------------------+--------------+
| 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         |
+-------------+-----------------+-----------------------------------------+--------------+

请注意，尽管 192.000.002.146 有前导零，但 search_data 192.000.002.146 是 search_string 192.0.2.146 的匹配项。

运行查询，用于搜索与 2001:0db8:85a3:0000:0000:8a2e:0370:7334 匹配的 IPv6 地址：

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;

Copy

+-------------+-----------------+-----------------------------------------+--------------+
| 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         |
+-------------+-----------------+-----------------------------------------+--------------+

以下查询与前面的查询相同，但它排除了 search_string 中的前导零和零值区段：

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;

Copy

+-------------+-----------------+-----------------------------------------+--------------+
| 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         |
+-------------+-----------------+-----------------------------------------+--------------+

以下查询显示 search_string 具有 IPv4 地址的 CIDR 范围：

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;

Copy

+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | True         |
| 192.0.2.146 | 203.0.113.5     | True         |
+-------------+-----------------+--------------+

以下查询显示，带前导零的 search_string 对省略前导零的 IPv4 地址返回 True：

SELECT ipv4_source,
       ipv4_target,
       SEARCH_IP((ipv4_source, ipv4_target), '203.000.113.005') AS "Match found?"
  FROM ipt
  ORDER BY ipv4_source;

Copy

+-------------+-----------------+--------------+
| IPV4_SOURCE | IPV4_TARGET     | Match found? |
|-------------+-----------------+--------------|
| 192.0.2.111 | 192.000.002.146 | False        |
| 192.0.2.146 | 203.0.113.5     | True         |
+-------------+-----------------+--------------+

通过在 WHERE 子句中使用函数来搜索匹配的 IP 地址¶

以下查询使用 WHERE 子句中的函数，仅搜索 ipv4_target 列。

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv4_target, '203.0.113.5')
  ORDER BY ipv4_source;

Copy

+-------------+-------------+-----------------------------------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET                             |
|-------------+-------------+-----------------------------------------|
| 192.0.2.146 | 203.0.113.5 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |
+-------------+-------------+-----------------------------------------+

在 WHERE 子句中使用该函数时，如果没有匹配项，则不会返回任何值：

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv4_target, '203.0.113.1')
  ORDER BY ipv4_source;

Copy

+-------------+-------------+-------------+
| IPV4_SOURCE | IPV4_TARGET | IPV6_TARGET |
|-------------+-------------+-------------|
+-------------+-------------+-------------+

以下查询使用 WHERE 子句中的函数，仅搜索 ipv6_target 列。

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(ipv6_target, '2001:db8:1234::5678')
  ORDER BY ipv4_source;

Copy

+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET         |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+

您可以将 * 字符（或 table.*）用作 SEARCH 函数的第一个实参，如以下示例所示。搜索会对所选表格中所有符合条件的列进行操作：

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP((*), '192.0.2.146')
  ORDER BY ipv4_source;

Copy

+-------------+-----------------+-----------------------------------------+
| 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 |
+-------------+-----------------+-----------------------------------------+

您还可以使用 ILIKE 和 EXCLUDE 关键字进行筛选。有关这些关键字的更多信息，请参阅 SELECT。

以下搜索使用 ILIKE 关键字仅在以字符串 _target 结尾的列中进行搜索。

SELECT ipv4_source,
       ipv4_target,
       ipv6_target
  FROM ipt
  WHERE SEARCH_IP(* ILIKE '%_target', '192.0.2.146')
  ORDER BY ipv4_source;

Copy

+-------------+-----------------+---------------------+
| IPV4_SOURCE | IPV4_TARGET     | IPV6_TARGET         |
|-------------+-----------------+---------------------|
| 192.0.2.111 | 192.000.002.146 | 2001:db8:1234::5678 |
+-------------+-----------------+---------------------+

对 VARCHAR 列启用 FULL_TEXT 搜索优化¶

要对 ``ipt` 表中的列启用 FULL_TEXT 搜索优化 <label-enable_full_text_search>`，请运行以下 ALTER TABLE 命令：

ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ipv4_source,
  ipv4_target,
  ipv6_target,
  ANALYZER => 'ENTITY_ANALYZER');

Copy

备注

您指定的列必须是 VARCHAR 或 VARIANT 列。不支持其他数据类型的列。

在 VARIANT 列中搜索匹配的 IP 地址¶

以下示例展示了如何使用 SEARCH_IP 函数查询 VARIANT 列。

以下示例使用 SEARCH_IP 函数搜索 VARIANT 列中字段的路径。创建名为 iptv 的表，并插入两行：

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" } ');

Copy

运行以下搜索查询。第一个查询仅搜索 ipv1 字段。第二个查询搜索 ipv1 和 ipv2。

SELECT * FROM iptv
  WHERE SEARCH_IP((ip1:"ipv1"), '203.0.113.5');

Copy

+--------------------------+
| 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');

Copy

+--------------------------+
| IP1                      |
|--------------------------|
| {                        |
|   "ipv1": "203.0.113.5", |
|   "ipv2": "203.0.113.5"  |
| }                        |
| {                        |
|   "ipv1": "192.0.2.146", |
|   "ipv2": "203.0.113.5"  |
| }                        |
+--------------------------+

要对 ``ip1` VARIANT 列及其字段启用 FULL_TEXT 搜索优化 <label-enable_full_text_search>`，请运行以下 ALTER TABLE 命令：

ALTER TABLE iptv ADD SEARCH OPTIMIZATION ON FULL_TEXT(
  ip1:"ipv1",
  ip1:"ipv2",
  ANALYZER => 'ENTITY_ANALYZER');

Copy

备注

您指定的列必须是 VARCHAR 或 VARIANT 列。不支持其他数据类型的列。

在文本的长字符串中搜索匹配的 IP 地址¶

创建名为 ipt_log 的表，并插入行：

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.');

Copy

在包含 192.0.2.146 IP 地址的 ip_request_log 列中搜索日志条目：

SELECT * FROM ipt_log
  WHERE SEARCH_IP(ip_request_log, '192.0.2.146')
  ORDER BY id;

Copy

+----+---------------------------------------------------+
| ID | IP_REQUEST_LOG                                    |
|----+---------------------------------------------------|
|  1 | Connection from IP address 192.0.2.146 succeeded. |
|  3 | Connection from IP address 192.0.2.146 dropped.   |
+----+---------------------------------------------------+

预期错误案例的示例¶

以下示例显示了返回预期语法错误的查询。

以下示例失败了，因为 5 不是 search_string 实参支持的数据类型。

SELECT SEARCH_IP(ipv4_source, 5) FROM ipt;

Copy

001045 (22023): SQL compilation error:
argument needs to be a string: '1'

以下示例失败的原因是 search_string 实参不是有效的 IP 地址。

SELECT SEARCH_IP(ipv4_source, '1925.0.2.146') FROM ipt;

Copy

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,

以下示例失败的原因是 search_string 实参为空字符串。

SELECT SEARCH_IP(ipv4_source, '') FROM ipt;

Copy

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,

以下示例失败了，因为没有为 search_data 实参指定具有受支持数据类型的列。

SELECT SEARCH_IP(id, '192.0.2.146') FROM ipt;

Copy

001173 (22023): SQL compilation error: error line 1 at position 7: Expected non-empty set of columns supporting full-text search.

以下示例成功了，因为确实为 search_data 实参指定了具有受支持的数据类型的列。该函数忽略了 id 列，因为它不是支持的数据类型：

SELECT SEARCH_IP((id, ipv4_source), '192.0.2.146') FROM ipt;

Copy

+---------------------------------------------+
| SEARCH_IP((ID, IPV4_SOURCE), '192.0.2.146') |
|---------------------------------------------|
| True                                        |
| False                                       |
+---------------------------------------------+