PostgreSQL 数组类型使用详解
PostgreSQL 数组类型使用详解
可能大家对 PostgreSQL 这个关系型数据库不太熟悉,因为大部分人最熟悉的,公司用的最多的是 MySQL
我们先对 PostgreSQL 数据库 (下面简称 PG
)简单的介绍一下,以后有机会,再单独写一篇专门介绍 pgSql 的文章
The World's Most Advanced Open Source Relational Database
这是 PG
官网对自己的介绍,是的,你没有看错,“世界上最先进的开源关系型数据库”。一段严重违反我国广告法的话,上一个敢那么叫嚣的技术是 PHP
,“世界上最好的语言”,然后这句话就成了码农界最广为人知的梗,存在于各种笑料中。
不过 PG
并没有因为这样明目张胆地自吹自擂而遭到什么抨击或调侃,事实上,无论在务实的码农界,抑或是讲究章法的学术界,人们对 PG
都是赞许有加,PG
是完全当得起这句话的。
下面列出一些 PostgreSQL 的特点
PostgreSQL 是一种功能非常齐全关系型数据库,由加州大学计算机系开发
PostgreSQL 开源协议是类 BSD 的自有协议 ,这是一种非常友好的协议,不论是商用还是自用,或者修改代码再起个名拿来卖钱,都没有任何风险
PostgreSQL 支持的数据类型非常多,除了常用的,还有
枚举类型
,几何类型
,UUID类型
,json类型
,数组类型
等,其中数组类型
也是本篇文章的目的,介绍其中数组类型的使用PostgreSQL 成立时对标的数据库是
Oracle
数据库,所有 PostgreSQL 的功能和性能是非常强的。PostgreSQL 对复杂 SQL 的执行,要好于 MySql
..................
还有很多的特性,这里只简单的写几个,上面的几个特点也是我非常在意的,之所www.helloworld.net
此次改版把 Mysql 换成了 PostgreSQL , 就是有这些原因。
之前很多人问过,hellworld开发者社区
改版用到了哪些技术栈,其中之一,就是把 Mysql
换成了 PostgreSQL
在改版的过程中,所有的表全部重新设计,这对于后端来说,是一个极其需要勇气的决定,好在我们坚持下来了
在改的过程中,其中有这样一个场景:
一篇博客,有多个标签 ,比如 一个博客,有多线程
, 并发
, 线程池
这三个标签
对于这样的需求,我们可以分析一下
- 一篇博客,有多个标签
- 一个标签,也可有对应多篇博客
这样就形成了 多对多
的关系,建表的话,就会有一张关联表,大部分会想到这样建表
博客表: blog
标签表: tag
标签博客表: tag_blog
其中各表的字段,如下(简单起见,只列出最少的列):
blog
表:
- id 数字类型,博客的 id, 自增长的主键
- title 字符串类型,博客的标题
tag
表:
- id 数字类型,标签的 id , 自增长的主键
- name 字符串类型,标签的名字
tag_blog
:
- id 数字类型, 自增长的主键
- tag_id 标签 id (对应 tag 表中的 id)
- blog_id 博客 id (对应 blog 表中的 id)
上面这个博客标签需求,需要 3 张表,我们知道,PostgreSQL 的列的类型是支持数组类型
的
我们是不是可以优化一下上面的需求,把 3 张表变成 1 张表
只保留一张 blog 表,在 blog 表中增加一列 tags , 类似就是 text[ ]
新的博客表字段如下:
blog
表:
- id 数字类型 (bigint),博客的 id, 自增长的主键
- title 字符串类型 (text),博客的标题
- tags 字符串数组类型 (text [] )
为了方便大家测试,建表 SQL 如下
CREATE TABLE IF NOT EXISTS public.blog
(
id bigint NOT NULL DEFAULT nextval('blog_id_seq'::regclass),
title text COLLATE pg_catalog."default",
tags text[] COLLATE pg_catalog."default",
CONSTRAINT blog_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.blog
OWNER to postgres;
下面我们针对 tags 字段作一些基本操作
数组类型的基本操作
1 查询
现在表中没有数据,我们查询一下看看
select * from blog
结果如下:
2 插入数据
插入一条记录,标题是 www.helloworld.net
, 对应的标签有 3 个,分别是 helloworld
, 技术
, 社区
insert into blog (title,tags) values('www.helloworld.net','{"helloworld","技术","社区"}')
再次查询
select * from blog
结果如下:
可以看到,已经有了一条数据了,tags 数组里面有 3 个元素,分别是 helloworld
, 技术
, 社区
我们再次插入两条数据,方便我们测试
insert into blog (title,tags) values('www.juejin.im','{"掘金","技术","开发者"}');
insert into blog (title,tags) values('www.oschina.net','{"开源中国","oschina","开源"}');
查询结果如下:
3 条件查询
3.1 查询标签中有 技术
标签的博客,语法 select * from blog where 'xx' = any(数组字段)
sql 语句如下
select * from blog where '技术'= any(tags)
查询结果如下:
3.2 查询标签中有 helloworld
标签或者有 开源中国
标签的博客
sql 语句如下:
select * from blog where 'helloworld'= any(tags) or '开源中国' = any(tags)
结果如下:
4 更新
4.1 更新标签的名称
我们将 id = 1 的记录的 tags 数组中, 社区
改成开发者社区
注意:pg 中数组类型,索引是从 1 开始,我们将 id = 1 的记录,社区
元素索引为 3,修改语法为: update 表名 set 字段[index] = 'xx' where id=1
sql 如下:
update blog set tags[3] = '开发者社区' where id=1
再次查询,结果如下:
可以发现,通过 tags[3] = '开发者社区'
,成功的把 社区
修改成了 开发者社区
4.2 添加一个标签
我们把 id=1 的记录,标签再增加一个 程序员
标签
可以使用 PostgreSQL 的 array_append
函数
使用方法如下:
sql 写法如下:
update blog set tags = array_append(tags, '程序员'::text) where id=1
再次查询结果如下:
5 删除
我们删除标签
把 id= 3 的记录中的标签,删除开源
sql 如下:
update blog set tags = array_remove(tags, '开源'::text) where id=3
执行后,再次查询,如下:
总结
以上就是关于 PostgreSQL 的数组类型的常见的用法,至于其它的用法,大家可以看一下官方文档,再结合本例的 SQL 写法
应该很容易就能掌握,博主的环境都是搭建在cnaaa服务器上的。
以上的需求,其实实际应用中并不是适合用数组类型解决,数组适合的场景,操作,交互不多,不太重要,可以用。
此例只是方便说明用法,具体实际中怎么用,大家还需要结合自己的业务需求,灵活选择