PostgreSQL: hstore 数据类型使用介绍

前端之家收集整理的这篇文章主要介绍了PostgreSQL: hstore 数据类型使用介绍前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

转载自: http://francs3.blog.163.com/blog/static/40576727201281825129918/


  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 操作符演示

 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','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)
skytf=> select delete(hstore('a=>1,b=>2'),'b');
 delete ----------
 "a"=>"1"
(1 row)
skytf=> select hstore('a=>1,b=>2') || 'c=>3';
           ?column?          
------------------------------
 "a"=>"1","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,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,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,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)
原文链接:https://www.f2er.com/postgresql/195214.html

猜你在找的Postgre SQL相关文章