网络类型（inet / cidr / macaddr）
PostgreSQL 内置了网络地址（Network Address）类型，可以像处理数字一样处理 IP 地址与网段：判断某个 IP 属于哪个子网、两个网段是否同源、地址是否落在白名单区间。本节讲解 inet、cidr、macaddr、macaddr8 四种类型的差异、输入格式、操作符与常用函数，并给出 IP 黑白名单与网段归属查询的实战方案。
前置知识
阅读本节前，建议先了解：几何操作符与函数
基础概念
四种网络类型
| 类型 | 名称 | 存储大小 | 说明 |
|---|---|---|---|
inet | 主机地址 | 7 或 19 字节 | IPv4/IPv6 地址，可选带子网掩码 |
cidr | 网络地址 | 7 或 19 字节 | IPv4/IPv6 网络，强制要求主机位为 0 |
macaddr | MAC 地址 | 6 字节 | 48 位以太网地址 |
macaddr8 | MAC 地址（8 字节） | 8 字节 | 64 位 EUI-64 地址（PostgreSQL 10+） |
inet 与 cidr 的区别是本节最核心的知识点：
| 对比项 | inet | cidr |
|---|---|---|
| 语义 | 一台主机的地址 | 一个网络 |
| 掩码是否必填 | 可选（缺失时按 /32 或 /128） | 必填（缺失时报错） |
| 主机位是否强制为 0 | 不强制，原样保留 | 强制清零 |
192.168.1.5/24 输入 | 存为 192.168.1.5/24 | 报错（主机位非 0） |
192.168.1.0/24 输入 | 存为 192.168.1.0/24 | 存为 192.168.1.0/24 |
| 典型用途 | 客户端 IP、服务器地址 | 网段、白名单、路由表 |
sql
-- inet 保留主机位，地址与掩码都按原样存储
SELECT '192.168.1.5/24'::inet AS as_inet; -- 192.168.1.5/24text
as_inet
---------------
192.168.1.5/24sql
-- cidr 要求主机位必须为 0，否则直接报错
-- 错误！主机位非 0
SELECT '192.168.1.5/24'::cidr;
-- 报错：invalid cidr value: "192.168.1.5/24"
-- DETAIL: Value has bits set to right of mask.
-- 错误！即使省略掩码也会按 /32 判定，同样报错
SELECT '192.168.1.5'::cidr;
-- 报错：invalid cidr value: "192.168.1.5"
-- DETAIL: Value has bits set to right of mask.
-- 正确：主机位本来就是 0
SELECT '192.168.1.0/24'::cidr; -- 192.168.1.0/24
-- 正确：想从带主机位的地址得到网段，先转 inet 再取 network()
SELECT network('192.168.1.5/24'::inet); -- 192.168.1.0/24
-- 也可以对 cidr 用 set_masklen() 调整前缀长度
SELECT set_masklen('192.168.1.0/24'::cidr, 28); -- 192.168.1.0/28
-- inet 可以不带掩码
SELECT '192.168.1.5'::inet; -- 192.168.1.5术语提示
网络地址（Network Address）、子网（Subnet）、子网掩码（Netmask）、前缀长度（Prefix Length）、主机位（Host Bits）、网络位（Network Bits）、无类别域间路由（CIDR, Classless Inter-Domain Routing）、物理地址（MAC Address）。/24 中的 24 就是前缀长度。
输入格式
text
IPv4 主机 192.168.1.1
IPv4 带掩码 192.168.1.1/24 掩码可用 /24 或 /255.255.255.0
IPv6 主机 2001:4f8:3:ba::10
IPv6 带掩码 2001:4f8:3:ba::10/64
MAC 地址 08:00:2b:01:02:03
MAC（点分） 0800.2b01.0203
MAC（连写） 08002b010203
MAC 广播 08:00:2b:ff:ff:ff
MAC 空值 00:00:00:00:00:00sql
-- 掩码的两种写法等价
SELECT '192.168.1.0/24'::cidr AS slash,
'192.168.1.0/255.255.255.0'::cidr AS dotted;text
slash | dotted
-----------------+-----------------
192.168.1.0/24 | 192.168.1.0/24sql
-- MAC 三种写法等价
SELECT '08:00:2b:01:02:03'::macaddr AS colon,
'0800.2b01.0203'::macaddr AS dotted,
'08002b010203'::macaddr AS plain;text
colon | dotted | plain
------------------+-----------------+-----------------
08:00:2b:01:02:03 | 08:00:2b:01:02:03 | 08:00:2b:01:02:03语法与用法
操作符总表
网络类型的操作符全部为子网（Subnet）关系判断：
| 操作符 | 读法 | 左类型 | 右类型 | 说明 |
|---|---|---|---|---|
< | 严格小于 | inet / cidr | inet / cidr | 按网络位与掩码比较 |
<= | 小于等于 | 同上 | 同上 | |
= | 等于 | 同上 | 同上 | |
>= | 大于等于 | 同上 | 同上 | |
> | 严格大于 | 同上 | 同上 | |
<> | 不等于 | 同上 | 同上 | |
<< | 严格包含于 | inet / cidr | inet / cidr | A 是 B 的真子网 |
<<= | 包含于或等于 | inet / cidr | inet / cidr | A 是 B 的子网 |
>> | 严格包含 | inet / cidr | inet / cidr | A 是真包含 B |
>>= | 包含或等于 | inet / cidr | inet / cidr | A 包含 B |
&& | 同网段 | inet / cidr | inet / cidr | 两者落在同一子网内 |
~ | 取反 | — | inet | 前缀操作符，按位取反 |
& | 按位与 | inet | inet | 两个地址按位与 |
| | 按位或 | inet | inet | 两个地址按位或 |
+ | 加 | inet | integer | 地址偏移（IPv4） |
- | 减 | inet | integer | 地址偏移（IPv4） |
- | 减 | inet | inet | 两地址差值（IPv4） |
sql
-- 包含关系：<< 与 <<=
SELECT '192.168.1.0/24'::cidr << '192.168.0.0/16'::cidr AS strictly_in, -- t
'192.168.0.0/16'::cidr << '192.168.0.0/16'::cidr AS not_strictly, -- f（相等不是"严格"）
'192.168.0.0/16'::cidr <<= '192.168.0.0/16'::cidr AS in_or_equal; -- t
-- 主机地址是否属于某网段
SELECT '192.168.1.5'::inet << '192.168.1.0/24'::cidr AS ip_in_subnet, -- t
'192.168.2.5'::inet << '192.168.1.0/24'::cidr AS ip_out; -- f
-- 同网段：两个地址是否在同一子网
SELECT '192.168.1.5'::inet && '192.168.1.9'::inet AS same_24, -- t
'192.168.1.5'::inet && '192.168.2.9'::inet AS diff_24; -- ftext
strictly_in | not_strictly | in_or_equal | ip_in_subnet | ip_out | same_24 | diff_24
-------------+--------------+-------------+--------------+--------+---------+---------
t | f | t | t | f | t | f&& 的语义容易误解
&& 不是"网段有交集"，而是"两个地址的网络部分相同"。'192.168.1.5'::inet && '192.168.2.9'::inet 返回 f，因为默认掩码是 /32，两个完整的 /32 网络不同。要让 && 真正有用，两边都要带上掩码：
sql
-- 两边都带掩码时才符合同网段的直觉
SELECT '192.168.1.5/24'::inet && '192.168.1.9/24'::inet AS same; -- t
SELECT '192.168.1.5/24'::inet && '192.168.2.9/24'::inet AS different; -- f常用函数总表
| 函数 | 返回 | 说明 |
|---|---|---|
host(inet) | text | 只取地址部分（去掉掩码） |
text(inet) | text | 取原始文本（等价于 ::text） |
abbrev(inet) | text | 缩写形式（IPv6 会压缩） |
network(inet) | cidr | 所属网络（主机位清零） |
netmask(inet) | inet | 掩码本身 |
masklen(inet) | integer | 前缀长度 |
set_masklen(inet, integer) | inet | 设置前缀长度 |
set_masklen(cidr, integer) | cidr | 设置前缀长度 |
broadcast(inet) | inet | 广播地址（仅 IPv4） |
hostmask(inet) | inet | 主机掩码（~netmask，仅 IPv4） |
family(inet) | integer | 地址族：4 = IPv4，6 = IPv6 |
inet_same_family(inet, inet) | boolean | 是否同一地址族 |
inet_merge(inet, inet) | cidr | 合并两个网络的最小公共网络 |
macaddr8_set7bit(macaddr8) | macaddr8 | 设置第 7 位（生成 EUI-64 链路本地地址） |
sql
-- 地址拆分：host / network / netmask / broadcast / masklen
SELECT '192.168.1.5/24'::inet AS original,
host('192.168.1.5/24'::inet) AS host,
network('192.168.1.5/24'::inet) AS network,
netmask('192.168.1.5/24'::inet) AS netmask,
broadcast('192.168.1.5/24'::inet) AS broadcast,
masklen('192.168.1.5/24'::inet) AS masklen,
family('192.168.1.5/24'::inet) AS family;text
original | host | network | netmask | broadcast | masklen | family
----------------+--------------+----------------+----------------+----------------+---------+--------
192.168.1.5/24 | 192.168.1.5 | 192.168.1.0/24 | 255.255.255.0 | 192.168.1.255 | 24 | 4详细说明
inet 与 cidr 的选型
sql
-- 场景一：记录客户端 IP —— 用 inet
CREATE TABLE access_logs (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
client_ip INET NOT NULL, -- 天然带掩码，用 inet
path TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO access_logs (client_ip, path) VALUES
('203.0.113.7', '/api/users'),
('203.0.113.7/32', '/api/orders'), -- 显式写 /32，与上一行值相同
('198.51.100.22', '/api/products'),
('2001:db8::1', '/api/health'); -- IPv6
SELECT id, client_ip, path FROM access_logs ORDER BY id;text
id | client_ip | path
----+--------------+--------------
1 | 203.0.113.7 | /api/users
2 | 203.0.113.7 | /api/orders
3 | 198.51.100.22 | /api/products
4 | 2001:db8::1 | /api/health注意第 1 行与第 2 行的显示值相同：inet 输入时不带掩码等价于 /32，输出时若掩码是满长度就省略不显示。
sql
-- 场景二：记录网段 —— 用 cidr
CREATE TABLE allowed_networks (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
cidr CIDR NOT NULL, -- 业务语义是"网段"，用 cidr
remark TEXT NOT NULL
);
INSERT INTO allowed_networks (cidr, remark) VALUES
('10.0.0.0/8', '内网'),
('192.168.0.0/16', '办公网'),
('203.0.113.0/24', '合作方 A'),
('2001:db8::/32', 'IPv6 测试网');
-- INSERT 0 4
-- 查询：某 IP 属于哪些白名单网段
SELECT cidr, remark
FROM allowed_networks
WHERE '192.168.10.25'::inet << cidr
ORDER BY remark;text
cidr | remark
-----------------+--------
192.168.0.0/16 | 办公网
(1 row)子网包含关系：<< <<= >> >>=
这四个操作符是判断"地址归属"和"网段层级"的核心。
sql
-- 网段层级关系
SELECT '10.1.0.0/16'::cidr << '10.0.0.0/8'::cidr AS l1, -- t（/16 是 /8 的子网）
'10.0.0.0/8'::cidr << '10.0.0.0/8'::cidr AS l2, -- f（不是"严格"包含）
'10.0.0.0/8'::cidr <<= '10.0.0.0/8'::cidr AS l3, -- t
'10.0.0.0/8'::cidr >> '10.1.0.0/16'::cidr AS l4; -- t（反向：包含）
-- 无关网段
SELECT '10.0.0.0/8'::cidr << '172.16.0.0/12'::cidr AS unrelated; -- ftext
l1 | l2 | l3 | l4 | unrelated
----+----+----+----+-----------
t | f | t | t | f判断"某主机是否在某网段内"时，左侧通常是 inet（不带掩码），右侧是 cidr：
sql
SELECT '10.20.30.40'::inet << '10.0.0.0/8'::cidr AS a, -- t
'10.20.30.40'::inet << '10.20.30.0/24'::cidr AS b, -- t
'10.20.30.40'::inet << '10.20.31.0/24'::cidr AS c; -- f同网段判断：&&
sql
-- 两个配置在 192.168.1.0/24 内的设备
SELECT '192.168.1.10/24'::inet && '192.168.1.200/24'::inet AS in_same_subnet; -- t
-- 不带掩码时，& & 退化为"地址完全相同"
SELECT '192.168.1.10'::inet && '192.168.1.10'::inet AS identical, -- t
'192.168.1.10'::inet && '192.168.1.11'::inet AS different; -- f
-- 用它做"同网段设备分组"
SELECT device_a, device_b
FROM device_pairs
WHERE a_ip && b_ip;按位运算：~ & |
sql
-- 取反：得到主机掩码（IPv4）
SELECT ~netmask('192.168.1.5/24'::inet) AS hostmask_ops,
hostmask('192.168.1.5/24'::inet) AS hostmask_fn;text
hostmask_ops | hostmask_fn
------------------+------------------
0.0.0.255 | 0.0.0.255sql
-- 按位与：手工计算网络地址
SELECT '192.168.1.5'::inet & '255.255.255.0'::inet AS and_result;
-- 结果：192.168.1.0
-- 按位或：手工计算广播地址
SELECT '192.168.1.5'::inet | '0.0.0.255'::inet AS or_result;
-- 结果：192.168.1.255按位运算只支持 IPv4
~、&、| 以及 + / - 的整数偏移只对 IPv4 的 inet 定义。对 IPv6 使用会报"operator does not exist"。
sql
-- 错误！IPv6 不支持按位与
SELECT '2001:db8::1'::inet & 'ffff:ffff::'::inet;
-- 报错：operator does not exist: inet & inet （IPv6 路径下）替代方案是对 inet 做 ::text 或使用 ::bit(32) 转换处理。
地址偏移：+ -
sql
-- 在网段内枚举地址（IPv4 专用）
SELECT '192.168.1.0'::inet + 1 AS first_host, -- 192.168.1.1
'192.168.1.0'::inet + 254 AS last_host, -- 192.168.1.254
'192.168.1.10'::inet - 5 AS back; -- 192.168.1.5
-- 两地址之间的"距离"
SELECT '192.168.1.200'::inet - '192.168.1.100'::inet AS diff; -- 100text
first_host | last_host | back | diff
--------------+---------------+--------------+------
192.168.1.1 | 192.168.1.254 | 192.168.1.5 | 100有了 +，就能用 generate_series 生成整个网段的主机列表：
sql
-- 生成 192.168.1.1 ~ 192.168.1.10
SELECT ('192.168.1.0'::inet + n) AS ip
FROM generate_series(1, 10) AS n;text
ip
-------------
192.168.1.1
192.168.1.2
192.168.1.3
192.168.1.4
192.168.1.5
192.168.1.6
192.168.1.7
192.168.1.8
192.168.1.9
192.168.1.10
(10 rows)函数详解
host() 与 text()：
sql
SELECT host('192.168.1.5/24'::inet) AS host_only,
text('192.168.1.5/24'::inet) AS full_text,
'192.168.1.5/24'::inet::text AS cast_text;text
host_only | full_text | cast_text
--------------+------------------+------------------
192.168.1.5 | 192.168.1.5/24 | 192.168.1.5/24host() 会去掉掩码，这正是从"作者地址 + 掩码"中剥离纯 IP 的标准做法。
abbrev()：
sql
-- IPv6 缩写：省略中间的零组
SELECT '2001:0db8:0000:0000:0000:0000:0000:0001'::inet AS full_form,
abbrev('2001:0db8:0000:0000:0000:0000:0000:0001'::inet) AS abbreviated;text
full_form | abbreviated
-----------------------------------------+------------------
2001:db8::1/128 | 2001:db8::1abbrev() 有两个额外好处：一是去掉掩码（当掩码是满长度时），二是对 IPv6 输出最紧凑形式，适合在报表里展示。
set_masklen()：
sql
-- 用一个 IP 派生出不同粒度的网络
SELECT set_masklen('192.168.1.5'::inet, 24) AS net_24,
set_masklen('192.168.1.5'::inet, 16) AS net_16,
set_masklen('192.168.1.0/24'::cidr, 16) AS cidr_16;text
net_24 | net_16 | cidr_16
----------------+-----------------+---------------
192.168.1.5/24 | 192.168.1.5/16 | 192.168.0.0/16注意 inet 版本的 set_masklen 只改掩码，不改地址；cidr 版本会同时把主机位清零。
inet_merge()：
sql
-- 把两个网段合并成能同时包含它们的最小网络
SELECT inet_merge('192.168.1.0/24'::inet, '192.168.2.0/24'::inet) AS merged;
-- 结果：192.168.0.0/22inet_merge 在配置管理里很有用：把一组离散网段归并成少数几个大网段，减少防火墙规则条数。
inet_same_family() 与 family()：
sql
SELECT '192.168.1.1'::inet AS ipv4,
'2001:db8::1'::inet AS ipv6,
family('192.168.1.1'::inet) AS fam4,
family('2001:db8::1'::inet) AS fam6;
-- 避免跨地址族比较报错
SELECT inet_same_family('192.168.1.1'::inet, '2001:db8::1'::inet) AS same_family;
-- 结果：f< 、<< 等操作符比较不同地址族时会报错（"cannot compare inet values of different families"），所以在拼接 SQL 或做联合查询前用 inet_same_family() 预判是稳妥做法。
MAC 地址函数与转换：
sql
-- MAC 与文本互转
SELECT '08:00:2b:01:02:03'::macaddr::text AS mac_text,
'0800.2b01.0203'::macaddr AS normalized;
-- MAC 支持截断为前缀（用于 OUI 厂商识别）
SELECT trunc('08:00:2b:01:02:03'::macaddr) AS oui; -- 08:00:2b:00:00:00
-- macaddr8：48 位与 64 位互转
SELECT '08:00:2b:01:02:03'::macaddr::macaddr8 AS as_eui64,
'08:00:2b:ff:fe:01:02:03'::macaddr8::macaddr AS truncate;text
mac_text | normalized | oui | as_eui64 | truncate
------------------+-----------------+------------------+----------------------------+-----------------
08:00:2b:01:02:03 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 | 08:00:2b:ff:fe:01:02:03 | 08:00:2b:01:02:03trunc(macaddr) 是"取 OUI 前缀"的常用技巧：MAC 前 3 字节是厂商编号（OUI），把所有设备截断到 OUI 就能按厂商聚合。
macaddr8_set7bit()
macaddr8_set7bit('08:00:2b:01:02:03') 返回 0a:00:2b:ff:fe:01:02:03。它的逻辑是把第 7 位（u/l 位）置 1，用于从 EUI-48 地址生成符合 RFC 4291 的 EUI-64 链路本地地址。真实网络运维场景才用得到。
排序规则与索引
网络类型有完整的 B-tree 操作符类，可以直接排序、建索引、用于 GROUP BY：
sql
-- 排序：先按地址族？不，IPv4 在 IPv6 之前，然后按网络位比较
SELECT ip FROM (
VALUES ('2001:db8::1'::inet),
('10.0.0.1'::inet),
('192.168.1.1'::inet),
('9.9.9.9'::inet)
) AS t(ip)
ORDER BY ip;text
ip
---------------
9.9.9.9
10.0.0.1
192.168.1.1
2001:db8::1
(4 rows)IPv4 数值上按 32 位无符号整数比较，所以 9.9.9.9 < 10.0.0.1（而不是字符串的 "10" < "9"）。这一点比用 VARCHAR 存 IP 好得多：
| 存储方式 | 排序是否正确 | 空间 | 支持子网判断 |
|---|---|---|---|
VARCHAR(15) | ✘（'10.1.1.1' < '9.1.1.1'） | 变长 | ✘ |
BIGINT（手工转换） | ✔ | 8 字节 | ✘ |
INET | ✔ | 7 字节 | ✔ |
索引方面：inet / cidr 默认有 B-tree 操作符类，=、<、ORDER BY 都能走索引。但 <<、&& 这类子网包含判断无法走普通 B-tree 索引，这是性能坑。
sql
-- 白名单查询的两种写法
-- 写法 A：反向查询，无法走索引（左列是常量表达式）
SELECT * FROM allowed_networks WHERE '192.168.10.25'::inet << cidr;
-- 写法 B：把判断结果物化（PostgreSQL 18 的 inet_merge 或表达式索引）
CREATE INDEX idx_allowed_net ON allowed_networks (cidr);
-- 对 << 仍无帮助，需要改写策略，见"实战示例"中的方案网络类型 vs 文本存储
| 维度 | inet / cidr | TEXT / VARCHAR |
|---|---|---|
| 输入校验 | 自动（非法地址直接报错） | 无，脏数据可入库 |
| 存储大小 | 7 或 19 字节，定长 | 变长，最长 45 字节 |
| 排序 | 数值序（正确） | 字典序（错误） |
| 子网判断 | 原生操作符 | 需自己解析 |
| 提取网段 | network() | 需字符串处理 |
| 掩码归一化 | 自动 | 无 |
| 迁移成本 | 需应用层适配 | 通用 |
结论：只要涉及 IP 语义，一律用 inet / cidr，字符串仅用于日志的原始文本留存。
实战示例
案例一：IP 黑白名单系统
sql
-- 1. 名单表：白名单与黑名单共用一个结构，用 kind 区分
DROP TABLE IF EXISTS ip_rules;
CREATE TABLE ip_rules (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN ('allow', 'deny')),
cidr CIDR NOT NULL,
reason TEXT NOT NULL,
priority INTEGER NOT NULL DEFAULT 100, -- 数字越小优先级越高
enabled BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT no_dup_rule UNIQUE (kind, cidr)
);
CREATE INDEX idx_ip_rules_cidr ON ip_rules (cidr);
CREATE INDEX idx_ip_rules_lookup ON ip_rules (kind, priority) WHERE enabled;
INSERT INTO ip_rules (kind, cidr, reason, priority) VALUES
('deny', '0.0.0.0/0', '默认拒绝（兜底规则）', 999),
('deny', '203.0.113.0/24', '已知攻击源', 10),
('deny', '198.51.100.7/32', '暴力破解单个 IP', 5),
('allow', '10.0.0.0/8', '内网办公网段', 20),
('allow', '192.168.0.0/16', '内部 VPN 网段', 20),
('allow', '203.0.113.128/25','合作方特定子段', 15);
-- INSERT 0 6
-- 2. 判断某 IP 命中的最高优先级规则
SELECT kind, cidr, reason, priority
FROM ip_rules
WHERE enabled
AND '203.0.113.200'::inet << cidr
ORDER BY priority, masklen(cidr) DESC
LIMIT 1;text
kind | cidr | reason | priority
-------+--------------------+-----------------+----------
allow | 203.0.113.128/25 | 合作方特定子段 | 15
(1 row)sql
-- 3. 判断一个黑名单 IP 的归属
SELECT kind, cidr, reason, priority
FROM ip_rules
WHERE enabled AND '198.51.100.7'::inet << cidr
ORDER BY priority, masklen(cidr) DESC
LIMIT 1;text
kind | cidr | reason | priority
------+-------------------+---------------------+----------
deny | 198.51.100.7/32 | 暴力破解单个 IP | 5
(1 row)sql
-- 4. 规则审计：列出所有含"允许"规则的网段，并统计与"拒绝"规则的重叠情况
SELECT a.cidr AS allow_cidr,
d.cidr AS deny_cidr,
CASE WHEN a.cidr <<= d.cidr THEN '完全被拒绝覆盖'
WHEN a.cidr >>= d.cidr THEN '包含该拒绝网段'
ELSE '部分重叠' END AS relation
FROM ip_rules a
JOIN ip_rules d
ON a.kind = 'allow' AND d.kind = 'deny'
AND (a.cidr <<= d.cidr OR a.cidr >>= d.cidr)
WHERE a.enabled AND d.enabled
ORDER BY a.cidr, d.cidr;text
allow_cidr | deny_cidr | relation
-------------------+--------------------+--------------------
192.168.0.0/16 | 0.0.0.0/0 | 完全被拒绝覆盖
203.0.113.128/25 | 0.0.0.0/0 | 完全被拒绝覆盖
10.0.0.0/8 | 0.0.0.0/0 | 完全被拒绝覆盖
(3 rows)案例二：网段归属查询与流量分析
sql
-- 1. 网络资产表
DROP TABLE IF EXISTS subnets;
CREATE TABLE subnets (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
cidr CIDR NOT NULL,
vlan_id INTEGER,
dc TEXT NOT NULL -- 所在机房
);
INSERT INTO subnets (name, cidr, vlan_id, dc) VALUES
('web-prod', '10.1.0.0/24', 100, 'bj-dc1'),
('app-prod', '10.1.1.0/24', 101, 'bj-dc1'),
('db-prod', '10.1.2.0/28', 102, 'bj-dc1'),
('web-stage', '10.2.0.0/24', 200, 'sh-dc2'),
('office', '192.168.10.0/24', NULL, 'bj-office');
-- 2. 访问日志
DROP TABLE IF EXISTS access_logs;
CREATE TABLE access_logs (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
client_ip INET NOT NULL,
status SMALLINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO access_logs (client_ip, status) VALUES
('10.1.0.15', 200),
('10.1.0.16', 500),
('10.1.1.22', 200),
('10.1.2.5', 200),
('10.2.0.30', 404),
('192.168.10.77', 200),
('203.0.113.9', 403),
('10.9.9.9', 500);
-- 3. 按网段归属统计请求量（LEFT JOIN 保证未归类 IP 也出现）
SELECT coalesce(s.name, '(未归类)') AS subnet_name,
coalesce(s.dc, '-') AS dc,
count(*) AS requests,
count(*) FILTER (WHERE l.status >= 400) AS errors
FROM access_logs l
LEFT JOIN subnets s ON l.client_ip << s.cidr
GROUP BY s.name, s.dc
ORDER BY requests DESC;text
subnet_name | dc | requests | errors
-------------+-----------+----------+--------
web-prod | bj-dc1 | 2 | 1
app-prod | bj-dc1 | 1 | 0
db-prod | bj-dc1 | 1 | 0
web-stage | sh-dc2 | 1 | 1
office | bj-office | 1 | 0
(未归类) | - | 2 | 1
(6 rows)sql
-- 4. 找出数量最多但未归类的来源地址（用于补全网段表）
SELECT client_ip, count(*) AS cnt
FROM access_logs l
WHERE NOT EXISTS (
SELECT 1 FROM subnets s WHERE l.client_ip << s.cidr
)
GROUP BY client_ip
ORDER BY cnt DESC;text
client_ip | cnt
-------------+-----
203.0.113.9 | 1
10.9.9.9 | 1
(2 rows)sql
-- 5. 网段容量与广播地址（IPv4）
SELECT name,
cidr,
-- 2 的幂用 ^，注意 SQL 中 ^ 是幂运算而非按位异或
2 ^ (32 - masklen(cidr)) AS total_ips,
broadcast(cidr) AS bc
FROM subnets
ORDER BY masklen(cidr), name;text
name | cidr | total_ips | bc
------------+----------------+-----------+---------------
db-prod | 10.1.2.0/28 | 16 | 10.1.2.15
web-prod | 10.1.0.0/24 | 256 | 10.1.0.255
app-prod | 10.1.1.0/24 | 256 | 10.1.1.255
web-stage | 10.2.0.0/24 | 256 | 10.2.0.255
office | 192.168.10.0/24 | 256 | 192.168.10.255
(5 rows)交叉连接的代价
access_logs LEFT JOIN subnets ON client_ip << cidr 是一对多的区间查找，无法用普通索引优化。日志表上百万行、网段表上千行时，这个 JOIN 会退化成笛卡尔积级别的比较。
优化方案有三条：
- 物化归属列：写入时用触发器算出
subnet_id存到日志表，查询直接GROUP BY subnet_id。 - 用 GiST 索引 + 排除式 EXCLUDE 思路：把
cidr转成int8range或自定义范围类型（参考《自定义范围类型》一节），走 GiST。 - 限制网段表规模：先
inet_merge()归并网段，减少参与比较的行数。
sql
-- 方案一：物化归属列的写法
ALTER TABLE access_logs ADD COLUMN subnet_id BIGINT;
UPDATE access_logs l
SET subnet_id = (
SELECT s.id FROM subnets s
WHERE l.client_ip << s.cidr
ORDER BY masklen(s.cidr) DESC -- 取最精确的网段
LIMIT 1
);
CREATE INDEX idx_access_logs_subnet ON access_logs (subnet_id);
SELECT s.name, count(*) AS requests
FROM access_logs l
JOIN subnets s ON s.id = l.subnet_id
GROUP BY s.name
ORDER BY requests DESC;text
name | requests
----------+----------
web-prod | 2
app-prod | 1
db-prod | 1
web-stage| 1
office | 1
(5 rows)案例三：MAC 地址管理
sql
DROP TABLE IF EXISTS devices;
CREATE TABLE devices (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
hostname TEXT NOT NULL UNIQUE,
mac MACADDR NOT NULL,
ip INET,
vendor TEXT
);
INSERT INTO devices (hostname, mac, ip) VALUES
('web-01', '08:00:2b:01:02:03', '10.1.0.11'),
('web-02', '08:00:2b:01:02:04', '10.1.0.12'),
('db-01', '00:1b:21:aa:bb:cc', '10.1.2.5'),
('app-01', '00:1b:21:aa:bb:dd', '10.1.1.22');
-- 按 OUI（前 3 字节）聚合，识别厂商设备数量
SELECT trunc(mac) AS oui,
count(*) AS device_count,
string_agg(hostname, ', ' ORDER BY hostname) AS devices
FROM devices
GROUP BY trunc(mac)
ORDER BY device_count DESC;text
oui | device_count | devices
------------------+--------------+------------------
00:1b:21:00:00:00 | 2 | app-01, db-01
08:00:2b:00:00:00 | 2 | web-01, web-02
(2 rows)sql
-- 设备与所在网段关联
SELECT d.hostname, d.ip, s.name AS subnet
FROM devices d
JOIN subnets s ON d.ip << s.cidr
ORDER BY d.hostname;text
hostname | ip | subnet
----------+-------------+----------
app-01 | 10.1.1.22 | app-prod
db-01 | 10.1.2.5 | db-prod
web-01 | 10.1.0.11 | web-prod
web-02 | 10.1.0.12 | web-prod
(4 rows)sql
-- 找出网段内"应分配但未被任何设备占用"的地址（容量巡检）
SELECT ('10.1.0.0'::inet + n) AS free_ip
FROM generate_series(1, 20) AS n
WHERE ('10.1.0.0'::inet + n) NOT IN (SELECT ip FROM devices WHERE ip IS NOT NULL)
ORDER BY free_ip
LIMIT 5;text
free_ip
------------
10.1.0.1
10.1.0.2
10.1.0.3
10.1.0.4
10.1.0.5
(5 rows)注意事项
cidr要求主机位必须为 0，否则直接报错。 它不会帮你静默归零，报错信息是invalid cidr value ... Value has bits set to right of mask.。若手上是带主机位的地址，先转inet再用network()归零。
sql
-- 错误！主机位非 0
SELECT '192.168.1.5/24'::cidr;
-- 正确：先转 inet 再取 network
SELECT network('192.168.1.5/24'::inet); -- 192.168.1.0/24inet不带掩码等价于/32（IPv4）或/128（IPv6）。 输出时会省略满长度掩码，这与"没有掩码"在显示上无法区分。&&是"同网络"，不是"有交集"。 不带掩码时它退化为等值判断。~、&、|、+、-只支持 IPv4。 对 IPv6 会报操作符不存在的错误。- 跨地址族比较会报错。 IPv4 与 IPv6 之间用
<、<<等会失败，先用inet_same_family()判断。
sql
-- 错误！不能比较不同地址族
SELECT '10.0.0.1'::inet < '2001:db8::1'::inet;
-- 报错：cannot compare inet values of different familiesmasklen()返回的是前缀长度，不是主机数。 主机数要算2^(32 - masklen)。^在 PostgreSQL 中是幂运算。 计算网段容量写2 ^ (32 - masklen(cidr))，注意^与#（异或）的区别。- 子网包含查询
<<走不了 B-tree 索引。 大表关联场景必须物化归属列或改用范围类型。 broadcast()与hostmask()只对 IPv4 有效。 IPv6 没有广播概念，调用会返回NULL或报错。macaddr8是 PostgreSQL 10+ 新增类型。 老版本只有macaddr（48 位）。trunc(macaddr)返回的是macaddr而非文本。 用于GROUP BY完全可行，但要输出"OUI 前缀"应显式::text。- MAC 地址没有唯一性保障。
macaddr类型不做格式之外的校验，唯一约束要自己加，且要注意同一 MAC 在不同网段可重复。 inet的文本输出不补零。'010.001.001.001'::inet输出为10.1.1.1，与输入字符串不一致，需要保留原文就用TEXT列。abbrev()会去掉掩码。 它主要用于 IPv6 的紧凑展示，不要拿它当"标准化"函数。
最佳实践
- 主机地址用
inet，网段用cidr。 语义分开，才不会出现"网段里带了主机位"的脏数据。 inet列不要手工写掩码。 记录客户端 IP 直接写'10.0.0.1'，让 PostgreSQL 按/32处理；需要掩码时明确写出来。- 永远不要用
VARCHAR存 IP。 排序错误、无法子网判断、无法校验，三个问题都会在生产环境爆发。 - 白名单表加
UNIQUE (kind, cidr)。 防止重复配置；cidr的规范化保证了192.168.1.0/24不会与192.168.1.0/255.255.255.0各存一份。 - 规则查询按
priority排序，并用masklen(cidr) DESC打破平局。 更精确的网段（前缀更长）应优先命中。 - 用部分索引限制
enabled规则。CREATE INDEX ... WHERE enabled能显著减小索引体积，因为禁用规则通常占多数。
sql
CREATE INDEX idx_ip_rules_lookup ON ip_rules (kind, priority) WHERE enabled;- 大表做归属分析时物化
subnet_id。 用触发器或批量任务在写入时就算好，别在查询时做<<关联。 - 网段数量多时先
inet_merge()归并。 上千条零散网段先合并成几十条，再参与查询。 - 用
host()剥离掩码后再入库到文本列。 需要把 IP 传给下游系统时，通常下游只认纯地址。 - MAC 地址用于识别设备，不要用于授权。 MAC 可以伪造，安全策略必须以 IP/证书/Token 为准。
inet列也可以建 GiST 索引配合&&。 需要频繁做"同网段"判断时，GiST 的inet_ops操作符类比 B-tree 更合适。
sql
CREATE INDEX idx_logs_ip_gist ON access_logs USING GIST (client_ip inet_ops);- 迁移 IPv4→IPv6 时用
family()做数据体检。 先统计两族数据的分布，再决定是否拆分列或增加地址族标识。
下一节
继续学习：PostGIS 入门
参考链接
- PostgreSQL 官方文档：Network Address Types
- PostgreSQL 官方文档：Network Address Functions and Operators
- PostgreSQL 官方文档：Index Types
- PostgreSQL 官方文档：Full Text Search