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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: hstore 数据类型使用介绍  

2012-09-18 15:20:29|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

        hstore 数据类型作为 Extension 模块, 在之前版本已经有了,之前大概知道概念,一直没怎么
研究,今天做了些测试。

      简单的说 hstore 数据类型用来存储具有多个属性值的数据,模板如 key => value , key 代表存储
的属性, values 为相应属性对应的值,一个简单的 hstore 例子如下:


一 hstore Example

 skytf=> select 'a=>1,b=>2'::hstore;
       hstore      
--------------------
 "a"=>"1", "b"=>"2"
(1 row)

skytf=> select hstore('a=>1,b=>2');
       hstore      
--------------------
 "a"=>"1", "b"=>"2"
(1 row)

   


二 hstore 操作符演示
--2.1 查询属性(key)  a 的值;

 skytf=>  select hstore('a=>1,b=>2') -> 'a';
 ?column?
----------
 1
(1 row)
   

--2.2 判断是否包含指定属性(key),如果包含,返回 t

 skytf=>  select hstore('a=>1,b=>2') ? 'a';
 ?column?
----------
 t
(1 row)

skytf=>  select hstore('a=>1,b=>2') ? 'c';
 ?column?
----------
 f
(1 row)

   

--2.3 判断左边的 hstore 是否包含右边的 hstore ,如果包含,返回 t。

 skytf=>  select hstore('a=>1,b=>2') @> 'a=>1'::hstore;
 ?column?
----------
 t
(1 row)

skytf=>  select hstore('a=>1,b=>2') @> 'a=>2'::hstore;
 ?column?
----------
 f
(1 row)

   备注:更多 hstore 函数和操作符,详见本文的附录部分。


 
三 hstore 函数演示
--3.1 array 类型转换成 hstore

 skytf=> select hstore(ARRAY['a','1','b','2']);
       hstore      
--------------------
 "a"=>"1", "b"=>"2"
(1 row)
   

--3.2 将两个 array 类型数据转换成 hstore 类型,比较难表达,看例子理解。

 skytf=> select hstore(ARRAY['a','b','c'], ARRAY['1','2','3']);
            hstore           
------------------------------
 "a"=>"1", "b"=>"2", "c"=>"3"
(1 row)

 备注:看到了吧,非常强大。  

 

--3.3 将 hstore 类型数据的 key 转换成 array

 skytf=> select akeys('a=>1,b=>2');
 akeys
-------
 {a,b}
(1 row)
   

--3.4 将 hstore 类型数据的 key 转换成 结果集

 skytf=> select skeys('a=>1,b=>2');
 skeys
-------
 a
 b
(2 rows)
   


--3.5 将 hstore 类型数据的 values 转换成 array

 skytf=> select avals('a=>1,b=>2');
 avals
-------
 {1,2}
(1 row)
   

--3.6 将 hstore 类型数据的 values 转换成 结果集

 skytf=> select svals('a=>1,b=>2');
 svals
-------
 1
 2
(2 rows)

 

--3.7 删除一个属性

 skytf=> select delete(hstore('a=>1,b=>2'),'b');
  delete 
----------
 "a"=>"1"
(1 row)
   

--3.8 增加一个属性

 skytf=> select hstore('a=>1,b=>2') || 'c=>3';
           ?column?          
------------------------------
 "a"=>"1", "b"=>"2", "c"=>"3"
(1 row)  

备注:先演示这么多吧,更多内容,参考本文的附。

 

四 Example1 : hstore 使用
--4.1 环境
PG版本:PostgreSQL 9.2
OS版本:Red Hat Enterprise Linux Server release 6.2

--4.2 创建 hstore 外部模块

 skytf=> \c skytf postgres;
You are now connected to database "skytf" as user "postgres".

skytf=# create extension hstore;
CREATE EXTENSION

skytf=# \c skytf skytf;
You are now connected to database "skytf" as user "skytf".
skytf=> \dT hstore;
      List of data types
 Schema |  Name  | Description
--------+--------+-------------
 public | hstore |
(1 row)

   


--4.3 创建测试表,并生成测试数据

 skytf=> create table tbl_role(id serial primary key,role_name varchar(32), exp int8, wealth int8, status char(1));
NOTICE:  CREATE TABLE will create implicit sequence "tbl_role_id_seq" for serial column "tbl_role.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_role_pkey" for table "tbl_role"
CREATE TABLE

skytf=> insert into tbl_role(role_name,exp,wealth) select 'user_' ||  generate_series(1,100000) ,generate_series(1,100000),generate_series(100001,200000);
INSERT 0 100000

skytf=> select * from tbl_role limit 3;
 id | role_name | exp | wealth | status
----+-----------+-----+--------+--------
  1 | user_1    |   1 | 100001 |
  2 | user_2    |   2 | 100002 |
  3 | user_3    |   3 | 100003 |
(3 rows)

   备注:上面创建一张角色信息表,属性值有 exp:经验值, wealth:财富值等。
  

--4.4 增加 hstore 数据类型

 skytf=> alter table tbl_role add column attr hstore;
ALTER TABLE

skytf=> update tbl_role set attr=('exp=>' || exp || ', wealth=>' || wealth )::hstore;
UPDATE 100000

skytf=> select * from tbl_role limit 3;;
 id | role_name | exp | wealth | status |              attr              
----+-----------+-----+--------+--------+---------------------------------
 22 | user_22   |  22 | 100022 |        | "exp"=>"22", "wealth"=>"100022"
 23 | user_23   |  23 | 100023 |        | "exp"=>"23", "wealth"=>"100023"
 24 | user_24   |  24 | 100024 |        | "exp"=>"24", "wealth"=>"100024"
(3 rows)

   


--4.5 创建索引

 skytf=> create index concurrently idx_tbl_role_attr on tbl_role using GIST ( attr);
CREATE INDEX

  备注: hstore 类型的数据支持 GIN,GIST 索引扫描的操作符有 @>, ?, ?& 和 ?| 
 

--4.6 测试:查询 hstore 子元素 exp 值为 22 的记录

 skytf=>  select id,role_name,attr,attr -> 'exp' From tbl_role where attr @> 'exp=>22';
 id | role_name |              attr               | ?column?
----+-----------+---------------------------------+----------
 22 | user_22   | "exp"=>"22", "wealth"=>"100022" | 22
(1 row)

skytf=>  select id,role_name,attr,attr -> 'wealth' From tbl_role where attr @> 'wealth=>100001';
 id | role_name |              attr              | ?column?
----+-----------+--------------------------------+----------
  1 | user_1    | "exp"=>"1", "wealth"=>"100001" | 100001

   

 

--4.7 执行计划:

 skytf=> explain analyze  select id,role_name,attr,attr -> 'exp' From tbl_role where attr @> 'exp=>22';
                                                          QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_role  (cost=5.36..347.63 rows=100 width=54) (actual time=3.267..7.111 rows=1 loops=1)
   Recheck Cond: (attr @> '"exp"=>"22"'::hstore)
   Rows Removed by Index Recheck: 1545
   ->  Bitmap Index Scan on idx_tbl_role_attr  (cost=0.00..5.33 rows=100 width=0) (actual time=2.813..2.813 rows=1546 loops=1)
         Index Cond: (attr @> '"exp"=>"22"'::hstore)
 Total runtime: 7.185 ms
(6 rows)
    
    备注:消耗 7 ms 左右,这个查询速度并不很快。
 

五:Example2 :将结果集转换成 hstore 类型
--5.1 测试表

 skytf=> select * From test_1;
 id |  name 
----+--------
  1 | francs
  2 | fpzhou
(2 rows)
   


--5.2 将结果集转换成 hstore 类型输出

 skytf=> select hstore(test_1) From test_1;
           hstore           
-----------------------------
 "id"=>"1", "name"=>"francs"
 "id"=>"2", "name"=>"fpzhou"
(2 rows)


skytf=> select hstore(test_1) From test_1 where id=1;
           hstore           
-----------------------------
 "id"=>"1", "name"=>"francs"
(1 row) 

   

 
六 附
--6.1 hstore Operators

Operator Description Example Result
hstore -> text get value for key (NULL if not present) 'a=>x, b=>y'::hstore -> 'a' x
hstore -> text[] get values for keys (NULL if not present) 'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] {"z","x"}
hstore || hstore concatenate hstores 'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore "a"=>"b", "c"=>"x", "d"=>"q"
hstore ? text does hstore contain key? 'a=>1'::hstore ? 'a' t
hstore ?& text[] does hstore contain all specified keys? 'a=>1,b=>2'::hstore ?& ARRAY['a','b'] t
hstore ?| text[] does hstore contain any of the specified keys? 'a=>1,b=>2'::hstore ?| ARRAY['b','c'] t
hstore @> hstore does left operand contain right? 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' t
hstore <@ hstore is left operand contained in right? 'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' f
hstore - text delete key from left operand 'a=>1, b=>2, c=>3'::hstore - 'b'::text "a"=>"1", "c"=>"3"
hstore - text[] delete keys from left operand 'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] "c"=>"3"
hstore - hstore delete matching pairs from left operand 'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore "a"=>"1", "c"=>"3"
record #= hstore replace fields in record with matching values from hstore see Examples section  
%% hstore convert hstore to array of alternating keys and values %% 'a=>foo, b=>bar'::hstore {a,foo,b,bar}
%# hstore convert hstore to two-dimensional key/value array %# 'a=>foo, b=>bar'::hstore {{a,foo},{b,bar}}

 

 

--6.2 hstore Functions

Function Return Type Description Example Result

hstore(record)
hstore construct an hstore from a record or row hstore(ROW(1,2)) f1=>1,f2=>2

hstore(text[])
hstore construct an hstore from an array, which may be either a key/value array, or a two-dimensional array hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']]) a=>1, b=>2, c=>3, d=>4

hstore(text[], text[])
hstore construct an hstore from separate key and value arrays hstore(ARRAY['a','b'], ARRAY['1','2']) "a"=>"1","b"=>"2"

hstore(text, text)
hstore make single-item hstore hstore('a', 'b') "a"=>"b"

akeys(hstore)
text[] get hstore's keys as an array akeys('a=>1,b=>2') {a,b}

skeys(hstore)
setof text get hstore's keys as a set skeys('a=>1,b=>2')
a  b

avals(hstore)
text[] get hstore's values as an array avals('a=>1,b=>2') {1,2}

svals(hstore)
setof text get hstore's values as a set svals('a=>1,b=>2')
1  2

hstore_to_array(hstore)
text[] get hstore's keys and values as an array of alternating keys and values hstore_to_array('a=>1,b=>2') {a,1,b,2}

hstore_to_matrix(hstore)
text[] get hstore's keys and values as a two-dimensional array hstore_to_matrix('a=>1,b=>2') {{a,1},{b,2}}

slice(hstore, text[])
hstore extract a subset of an hstore slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x']) "b"=>"2", "c"=>"3"

each(hstore)
setof(key text, value text) get hstore's keys and values as a set select * from each('a=>1,b=>2')
 key | value  -----+-------   a   | 1   b   | 2

exist(hstore,text)
boolean does hstore contain key? exist('a=>1','a') t

defined(hstore,text)
boolean does hstore contain non-NULL value for key? defined('a=>NULL','a') f

delete(hstore,text)
hstore delete pair with matching key delete('a=>1,b=>2','b') "a"=>"1"

delete(hstore,text[])
hstore delete pairs with matching keys delete('a=>1,b=>2,c=>3',ARRAY['a','b']) "c"=>"3"

delete(hstore,hstore)
hstore delete pairs matching those in the second argument delete('a=>1,b=>2','a=>4,b=>2'::hstore) "a"=>"1"

populate_record(record,hstore)
record replace fields in record with matching values from hstore see Examples section  

 

七 总结:

 1  hstore 适用于拥有多个属性值的对像,同时这些属性值查询并不非常频繁的场合,根据上面的测试,
     在 10 万数据中,根据元素属性值查询需要7ms 左右,这个速度已经算慢的了。

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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