注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: Network Address Types  

2012-06-08 16:12:25|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

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

 

一 网络数据类型基础
-1.1 Network Address Types

Name Storage Size Description
cidr 7 or 19 bytes IPv4 and IPv6 networks
inet 7 or 19 bytes IPv4 and IPv6 hosts and networks
macaddr 6 bytes MAC addresses

 
--1.2 inet vs. 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 类型区别     
--2.1 测试 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)。
        
--2.2 测试 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  类型网络数据,可以不存储子网掩码。
    
--2.3 测试索引
--插入测试数据并创建索引

 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 索引,在查询时可以用到索引。


三  cidr and inet 相关函数
--3.1 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)

   

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

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


--3.3 取 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)

   


四 附
--4.1 附一 Network Address Functions and Operators

Operator Description Example
< is less than inet '192.168.1.5' < inet '192.168.1.6'
<= is less than or equal inet '192.168.1.5' <= inet '192.168.1.5'
= equals inet '192.168.1.5' = inet '192.168.1.5'
>= is greater or equal inet '192.168.1.5' >= inet '192.168.1.5'
> is greater than inet '192.168.1.5' > inet '192.168.1.4'
<> is not equal inet '192.168.1.5' <> inet '192.168.1.4'
<< is contained within inet '192.168.1.5' << inet '192.168.1/24'
<<= is contained within or equals inet '192.168.1/24' <<= inet '192.168.1/24'
>> contains inet '192.168.1/24' >> inet '192.168.1.5'
>>= contains or equals inet '192.168.1/24' >>= inet '192.168.1/24'
~ bitwise NOT ~ inet '192.168.1.6'
& bitwise AND inet '192.168.1.6' & inet '0.0.0.255'
| bitwise OR inet '192.168.1.6' | inet '0.0.0.255'
+ addition inet '192.168.1.6' + 25
- subtraction inet '192.168.1.43' - 36
- subtraction inet '192.168.1.43' - inet '192.168.1.19'


--4.2 附二 cidr and inet Functions

Function Return Type Description Example Result

abbrev(inet)
text abbreviated display format as text abbrev(inet '10.1.0.0/16') 10.1.0.0/16

abbrev(cidr)
text abbreviated display format as text abbrev(cidr '10.1.0.0/16') 10.1/16

broadcast(inet)
inet broadcast address for network broadcast('192.168.1.5/24') 192.168.1.255/24

family(inet)
int extract family of address; 4 for IPv4, 6 for IPv6 family('::1') 6

host(inet)
text extract IP address as text host('192.168.1.5/24') 192.168.1.5

hostmask(inet)
inet construct host mask for network hostmask('192.168.23.20/30') 0.0.0.3

masklen(inet)
int extract netmask length masklen('192.168.1.5/24') 24

netmask(inet)
inet construct netmask for network netmask('192.168.1.5/24') 255.255.255.0

network(inet)
cidr extract network part of address network('192.168.1.5/24') 192.168.1.0/24

set_masklen(inet, int)
inet set netmask length for inet value set_masklen('192.168.1.5/24', 16) 192.168.1.5/16

set_masklen(cidr, int)
cidr set netmask length for cidr value set_masklen('192.168.1.0/24'::cidr, 16) 192.168.0.0/16

text(inet)
text extract IP address and netmask length as text text(inet '192.168.1.5') 192.168.1.5/32


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

  评论这张
 
阅读(26873)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016