PostgreSQL 數(shù)組類型使用詳解

可能大家對 PostgreSQL這個關(guān)系型數(shù)據(jù)庫不太熟悉，因為大部分人最熟悉的，公司用的最多的是 MySQL

我們先對PostgreSQL數(shù)據(jù)庫 （下面簡稱 PG）簡單的介紹一下，以后有機(jī)會，再單獨寫一篇專門介紹pgSql的文章

The World's Most Advanced Open Source Relational Database

這是PG官網(wǎng)對自己的介紹，是的，你沒有看錯，“世界上最先進(jìn)的開源關(guān)系型數(shù)據(jù)庫”。一段嚴(yán)重違反我國廣告法的話，上一個敢那么叫囂的技術(shù)是PHP，“世界上最好的語言”，然后這句話就成了碼農(nóng)界最廣為人知的梗，存在于各種笑料中。

不過PG并沒有因為這樣明目張膽地自吹自擂而遭到什么抨擊或調(diào)侃，事實上，無論在務(wù)實的碼農(nóng)界，抑或是講究章法的學(xué)術(shù)界，人們對PG都是贊許有加，PG是完全當(dāng)?shù)闷疬@句話的。

下面列出一些PostgreSQL的特點

• PostgreSQL是一種功能非常齊全關(guān)系型數(shù)據(jù)庫，由加州大學(xué)計算機(jī)系開發(fā)
• PostgreSQL開源協(xié)議是類BSD的自有協(xié)議 ，這是一種非常友好的協(xié)議，不論是商用還是自用，或者修改代碼再起個名拿來賣錢，都沒有任何風(fēng)險
• PostgreSQL支持的數(shù)據(jù)類型非常多，除了常用的，還有枚舉類型, 幾何類型,UUID類型  ， json類型 , 數(shù)組類型 等，其中數(shù)組類型 也是本篇文章的目的，介紹其中數(shù)組類型的使用
• PostgreSQL 成立時對標(biāo)的數(shù)據(jù)庫是 Oracle數(shù)據(jù)庫，所有 PostgreSQL 的功能和性能是非常強(qiáng)的。
• PostgreSQL 對復(fù)雜SQL的執(zhí)行，要好于MySql
• ..................

還有很多的特性，這里只簡單的寫幾個，上面的幾個特點也是我非常在意的，之所 www.helloworld.net 此次改版把Mysql換成了PostgreSQL ,就是有這些原因。

之前很多人問過，hellworld開發(fā)者社區(qū) 改版用到了哪些技術(shù)棧，其中之一，就是把 Mysql 換成了 PostgreSQL

在改版的過程中，所有的表全部重新設(shè)計，這對于后端來說，是一個極其需要勇氣的決定，好在我們堅持下來了

在改的過程中，其中有這樣一個場景：

一篇博客，有多個標(biāo)簽 ，比如 一個博客，有多線程, 并發(fā) , 線程池 這三個標(biāo)簽

對于這樣的需求，我們可以分析一下

• 一篇博客，有多個標(biāo)簽
• 一個標(biāo)簽，也可有對應(yīng)多篇博客

這樣就形成了 多對多 的關(guān)系，建表的話，就會有一張關(guān)聯(lián)表，大部分會想到這樣建表

博客表： blog

標(biāo)簽表： tag

標(biāo)簽博客表： tag_blog

其中各表的字段，如下（簡單起見，只列出最少的列）：

blog 表：

• id 數(shù)字類型，博客的 id, 自增長的主鍵
• title 字符串類型，博客的標(biāo)題

tag表：

• id 數(shù)字類型, 標(biāo)簽的 id , 自增長的主鍵
• name 字符串類型，標(biāo)簽的名字

tag_blog ：

• id 數(shù)字類型， 自增長的主鍵
• tag_id 標(biāo)簽 id (對應(yīng) tag 表中的 id )
• blog_id 博客id (對應(yīng) blog 表中的 id )

上面這個博客標(biāo)簽需求，需要 3 張表，我們知道，PostgreSQL 的列的類型是支持數(shù)組類型的

我們是不是可以優(yōu)化一下上面的需求，把 3 張表變成 1 張表

只保留一張 blog 表，在 blog 表中增加一列 tags ， 類似就是 text[ ]

新的博客表字段如下：

blog表：

• id 數(shù)字類型(bigint)，博客的 id, 自增長的主鍵
• title 字符串類型(text )，博客的標(biāo)題
• tags 字符串?dāng)?shù)組類型(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 字段作一些基本操作

數(shù)組類型的基本操作

1 查詢

現(xiàn)在表中沒有數(shù)據(jù)，我們查詢一下看看

select * from blog

結(jié)果如下：

2 插入數(shù)據(jù)

插入一條記錄，標(biāo)題是 www.helloworld.net , 對應(yīng)的標(biāo)簽有3個，分別是 helloworld , 技術(shù) , 社區(qū)

insert into blog (title,tags) values('www.helloworld.net','{"helloworld","技術(shù)","社區(qū)"}')

再次查詢

select * from blog

結(jié)果如下：

可以看到，已經(jīng)有了一條數(shù)據(jù)了，tags 數(shù)組里面有3個元素，分別是 helloworld , 技術(shù) , 社區(qū)

我們再次插入兩條數(shù)據(jù)，方便我們測試

insert into blog (title,tags) values('www.juejin.im','{"掘金","技術(shù)","開發(fā)者"}');
insert into blog (title,tags) values('www.oschina.net','{"開源中國","oschina","開源"}');

查詢結(jié)果如下：

3 條件查詢

3.1 查詢標(biāo)簽中有 技術(shù)標(biāo)簽的博客

語法 select * from blog where 'xx' = any(數(shù)組字段) 

sql 語句如下

select * from blog where '技術(shù)'= any(tags)

查詢結(jié)果如下：

3.2 查詢標(biāo)簽中有 helloworld標(biāo)簽或者有 開源中國標(biāo)簽的博客

sql語句如下：

select * from blog where 'helloworld'= any(tags) or '開源中國' = any(tags)

結(jié)果如下：

4 更新

4.1 更新標(biāo)簽的名稱

我們將 id = 1 的記錄的 tags 數(shù)組中， 社區(qū)改成開發(fā)者社區(qū)

注意：pg中數(shù)組類型，索引是從 1 開始，我們將 id = 1 的記錄，社區(qū)元素索引為3，修改語法為： update 表名 set 字段[index] = 'xx' where id=1 

sql如下:

update blog set tags[3] = '開發(fā)者社區(qū)' where id=1 

再次查詢，結(jié)果如下：

可以發(fā)現(xiàn)，通過 tags[3] = '開發(fā)者社區(qū)' ，成功的把 社區(qū)修改成了 開發(fā)者社區(qū)

4.2 添加一個標(biāo)簽

我們把 id=1 的記錄，標(biāo)簽再增加一個 程序員標(biāo)簽

可以使用PostgreSQL的 array_append 函數(shù)

使用方法如下：

sql寫法如下：

update blog set tags = array_append(tags, '程序員'::text) where id=1

再次查詢結(jié)果如下：

5 刪除

我們刪除標(biāo)簽

把 id= 3 的記錄中的標(biāo)簽，刪除開源

sql如下：

update blog set tags = array_remove(tags, '開源'::text) where id=3

執(zhí)行后，再次查詢，如下：

總結(jié)

以上就是關(guān)于 PostgreSQL 的數(shù)組類型的常見的用法，至于其它的用法，大家可以看一下官方文檔，再結(jié)合本例的SQL寫法

應(yīng)該很容易就能掌握

以上的需求，其實實際應(yīng)用中并不是適合用數(shù)組類型解決，數(shù)組適合的場景，操作，交互不多，不太重要，可以用

helloworld開發(fā)者社區(qū)在改版的過程中，數(shù)據(jù)庫雖然換成了 PostgreSQL ，但是博客的標(biāo)簽這塊需求，并沒有用這種方式

此例只是方便說明用法，具體實際中怎么用，大家還需要結(jié)合自己的業(yè)務(wù)需求，靈活選擇

以上就是PostgreSQL 數(shù)組類型操作使用及特點詳解的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL 數(shù)組類型的資料請關(guān)注腳本之家其它相關(guān)文章！