PostgreSQL 提供专门的网络数据类型(inet,cidr , macaddr )用来存储 IPv4, IPv6, 和 MAC 地址，官方建议使用 PostgreSQL中提供的专有网络数据类型存储网络地址，而不是用 text 类型存储，因为网络类型数据提供数据检验和各种网络操作符，函数，带来很大方便。

网络地址数据类型

Network Address Types
PostgreSQL: Network Address Types

Inet 和 Cidr

The essential difference between inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, whereas cidr does not.

备注：上面是手册上说的，没整明白，网上看了半天也没找到 inet 和 cidr 的区别，但根据实验来看, cidr 默认存储子网的信息而 inet 可以不存储，实验如下。

测试 Inet 与 Cidr

以下测试 Inet 与 Cidr 网络地址类型的差异。

测试 cidr 类型

skytf=> create table test_cidr (id serial,ip cidr);  
NOTICE: CREATE TABLE will create implicit sequence "test_cidr_id_seq" for serial column "test_cidr.id"  
CREATE TABLE
skytf=> insert into test_cidr(ip) values ('192.168.1.10/32');  
INSERT 0 1
skytf=> insert into test_cidr(ip) values ('192.168.1.11/32');  
INSERT 0 1
skytf=> insert into test_cidr(ip) values ('192.168.1.1');  
INSERT 0 1
skytf=> insert into test_cidr(ip) values ('192.168.1.0');  
INSERT 0 1
skytf=> insert into test_cidr(ip) values ('192.168.1.0/24');  
INSERT 0 1

skytf=> select * from test_cidr;  
id | ip  
----+-----------------  
 1 | 192.168.1.10/32  
 2 | 192.168.1.11/32  
 3 | 192.168.1.1/32  
 4 | 192.168.1.0/32  
 5 | 192.168.1.0/24  
(5 rows)

备注：从上面看出,cidr 类型网络数据都是以 address/y 类型存储，address 为网络IP，y 为子网掩码，如果 y 没有指定，默认为填充 32(ipv4) 或者 128(ipv6)。

测试 inet 类型

skytf=> create table test_inet(id serial,ip inet);  
NOTICE: CREATE TABLE will create implicit sequence "test_inet_id_seq" for serial column "test_inet.id"  
CREATE TABLE
skytf=> insert into test_inet (ip) values (inet'192.168.1.1');  
INSERT 0 1  
skytf=> insert into test_inet (ip) values (inet'192.168.1.1/32');  
INSERT 0 1  
skytf=> insert into test_inet (ip) values (inet'192.168.1.0/23');  
INSERT 0 1  
skytf=> insert into test_inet (ip) values (inet'192.168.1.0/24');  
INSERT 0 1

skytf=> select * From test_inet;  
id | ip  
----+----------------  
 1 | 192.168.1.1  
 2 | 192.168.1.1  
 3 | 192.168.1.0/23  
 4 | 192.168.1.0/24  
(4 rows)

备注： inet 类型网络数据，可以不存储子网掩码。

测试索引

插入测试数据并创建索引

skytf=> insert into test_inet select generate_series(5,10000),inet('192.168.1.2/32');  
INSERT 0 9996
  
skytf=> create index concurrently idx_test_inet_ip on test_inet using btree (ip);  
CREATE INDEX
skytf=> /d test_inet  
 Table "skytf.test_inet"  
Column | Type | Modifiers  
--------+---------+--------------------------------------------------------  
id | integer | not null default nextval('test_inet_id_seq'::regclass)  
ip | inet |  
Indexes:  
 "idx_test_inet_ip" btree (ip)
  
skytf=> analyze test_inet;  
ANALYZE

skytf=> select * from test_inet limit 10;  
id | ip  
----+----------------  
 1 | 192.168.1.1  
 2 | 192.168.1.1  
 3 | 192.168.1.0/23  
 4 | 192.168.1.0/24  
 5 | 192.168.1.2  
 6 | 192.168.1.2  
 7 | 192.168.1.2  
 8 | 192.168.1.2  
 9 | 192.168.1.2  
10 | 192.168.1.2  
(10 rows)

查看执行计划

skytf=> explain analyze select * from test_inet where ip='192.168.1.1';  
 QUERY PLAN  
-----------------------------------------------------------------------------------------------------------------------------  
Index Scan using idx_test_inet_ip on test_inet (cost=0.00..8.27 rows=1 width=11) (actual time=0.127..0.150 rows=2 loops=1)  
 Index Cond: (ip = '192.168.1.1'::inet)  
Total runtime: 0.260 ms  
(3 rows)
  
skytf=> select * from test_inet where ip < '192.168.1.2';  
id | ip  
----+----------------  
 3 | 192.168.1.0/23  
 4 | 192.168.1.0/24  
 1 | 192.168.1.1  
 2 | 192.168.1.1  
(4 rows)

skytf=> explain analyze select * from test_inet where ip < '192.168.1.2';  
 QUERY PLAN  
-----------------------------------------------------------------------------------------------------------------------------  
Index Scan using idx_test_inet_ip on test_inet (cost=0.00..8.32 rows=4 width=11) (actual time=0.028..0.075 rows=4 loops=1)  
 Index Cond: (ip < '192.168.1.2'::inet)  
Total runtime: 0.201 ms  
(3 rows)

备注：在 inet 类型数据上创建普通的 btree 索引，在查询时可以用到索引。

Inet 和 Cidr 相关函数

host 函数取 IP

skytf=> select cidr '192.168.1.1/32';  
 cidr  
----------------  
192.168.1.1/32  
(1 row)

skytf=> select host (cidr '192.168.1.1/32');  
 host  
-------------  
192.168.1.1  
(1 row)

text 函数: 取出 IP 地址和子网掩码，输出为 text 类型

skytf=> select text (cidr '192.168.1.1/32');  
 text  
----------------  
192.168.1.1/32  
(1 row)

取 netmask

skytf=> select netmask (cidr '192.168.1.1/32');  
 netmask  
-----------------  
255.255.255.255  
(1 row)

skytf=> select netmask (cidr '192.168.0.0/16');  
 netmask  
-------------  
255.255.0.0  
(1 row)

参考

http://www.postgresql.org/docs/9.2/static/datatype-net-types.html#DATATYPE-INET

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/237869.html

PostgreSQL: Network Address Types