PostgreSQL實(shí)現(xiàn)批量插入、更新與合并操作的方法

前言

就在 2019 年 1 月份微軟收購(gòu)了 PostgreSQL 數(shù)據(jù)庫(kù)的初創(chuàng)公司 CitusData, 在云數(shù)據(jù)庫(kù)方面可以增強(qiáng)與 AWS 的竟?fàn)帯WS 的 RDS 兩大開(kāi)源數(shù)據(jù)庫(kù)就是 MySQL(Aurora 和 MariaDB 是它的變種) 和 PostgreSQL。

而 PostgreSQL 跳出了普通關(guān)系型數(shù)據(jù)庫(kù)的類(lèi)型約束，它靈活的支持 JSON, JSONB, XML, 數(shù)組等類(lèi)型。比如說(shuō)字段類(lèi)型可以是各種形式的數(shù)組，一維或多維。

create table t1(
 address varchar(5)[3],
 counter integer[3][3],
 schedule text[][]
)

上面只是認(rèn)識(shí)了一下 PostgreSQL 這一亮眼的特性，本篇重點(diǎn)不在如何定義操作數(shù)組類(lèi)型的字段，而是對(duì)于普通的非數(shù)組字段類(lèi)型如何用與數(shù)組相關(guān)的 unnest 關(guān)鍵字進(jìn)行記錄的批量插入，更新以及合并操作。

在正式使用介入 unnest 之前先熟悉一下 PostgreSQL 的 upsert(update insert) 操作。受其他數(shù)據(jù)庫(kù)的影響，總以后 PostgreSQL 也應(yīng)該支持 merge into 語(yǔ)句，而且竟然 PostgreSQL 官方也有文檔介紹 MERGE 有模有樣的，然而試了一下根本就不支持 merge into 操作。

普通的 insert upsert 操作

不過(guò) PostgreSQL 有另一種 upsert 的語(yǔ)法，或稱(chēng)之為 insert on conflict，也就是 PostgreSQL 加強(qiáng)版的 insert 語(yǔ)句。如下

INSERT INTO users (id, level)
VALUES (1, 0)
ON CONFLICT (id) DO UPDATE
SET level = users.level + 1;

即插入記錄時(shí)存在相同的主鍵可對(duì)原記錄的某些列進(jìn)行更新，否則插入該記錄。更詳細(xì)的 insert 語(yǔ)法請(qǐng)參考官方文檔 PostgreSQL INSERT。

注意到上面的 upsert 只是應(yīng)用于處理單條記錄，而本文將要討論到的是批量的處理。我們知道 insert 時(shí)可以同時(shí)插入多行記錄，那么這個(gè)  upsert 語(yǔ)法是否也能同時(shí)操作多行記錄呢？我們大膽假設(shè)，小心求證，那就動(dòng)手一試吧

create table users(
 id int primary key,
 level int
)

創(chuàng)建一個(gè)表 users, 然后插入一條記錄

INSERT INTO users (id, level) VALUES (1, 0)

查詢(xún) select * from users 表的內(nèi)容如下

執(zhí)行批量的 insert

INSERT INTO users (id, level)
VALUES (1, 3), (2, 1)
ON CONFLICT (id) DO UPDATE
SET level = excluded.level;

理看看 users 表中的內(nèi)容

我們看到已存在行(id=1) 的 level 更新為新的值 3, 并且插入新行。

這里注意到我們用到一個(gè)關(guān)鍵字 excluded 用于引用 INSERT 語(yǔ)句中 VALUES 提供的值，相應(yīng)的表名 users 用于引用表中原有的值。

基于 unnest 的 upsert 操作

前面仍然是寫(xiě)作此文的意外收獲，實(shí)際上想要總結(jié)的是 unnest 關(guān)鍵字在批量操作中的作用。下面來(lái)進(jìn)行演示

create table testunnest(
  id int primary key,
 col1 int,
 col2 varchar(50)
)

創(chuàng)建一個(gè)表并用 unnest 提供并行數(shù)組來(lái)進(jìn)行批量插入

insert into testunnest(id, col1, col2) 
 values (unnest(array[1,2]), unnest(array[30,40]), unnest(array['val1', 'val2']));

這時(shí)候表中的內(nèi)容為

從執(zhí)行效果來(lái)看，它與下面的 insert 語(yǔ)句是等效的

insert into testunnest(id, col1, col2) values
 (1, 30, 'val1')
 (2, 40, 'val2')

換成成 unnest(array[..]) 的形式有一種行轉(zhuǎn)列的行為。

用 unnest 加上 unsert 再執(zhí)行一次插入

insert into testunnest(id, col1, col2)
 values (unnest(array[2,3]), unnest(array[80,90]), unnest(array['valupdated', 'val3']))
 on conflict (id) do update
 set col1 = excluded.col1, col2 = excluded.col2

再查看表的內(nèi)容如下

unnest 與 JDBC 操作

insert into users values (?, ?) on conflict.... 的 SQL 語(yǔ)句的單條記錄或批量操作(addBatch(), executeBatch()) 就不多說(shuō)了，主要看下用 JDBC 怎么對(duì) unnest 進(jìn)行賦值操作。

PreparedStatement pstmt = conn.prepareStatement(
 "INSERT INTO testunnest(id, col1, col2) " +
  " VALUES (unnest(?), unnest(?), unnest(?))" +
  " ON CONFLICT (id) DO UPDATE" +
  " SET col1 = excluded.col1, col2 = excluded.col2"
);
 
pstmt.setArray(1, conn.createArrayOf("int", new Integer[]{2, 3}));
pstmt.setArray(2, conn.createArrayOf(JDBCType.INTEGER.getName(), new Integer[]{80, 90}));
pstmt.setArray(3, conn.createArrayOf("varchar", new String[]{"val1", "val2"}));
 
int update = pstmt.executeUpdate();
System.out.println(update); //影響的記錄數(shù)是 2

點(diǎn)位符要用 unnest(?)，設(shè)置參數(shù)時(shí)要用 setArray(), 參數(shù)用 conn.createArrayOf(type, array) 來(lái)指定。需要指明數(shù)組中的元素類(lèi)型，這么普通的 setInt(), setString() 是一個(gè)意思。

用不著轉(zhuǎn)換為 PostgreSQL 特定的 PreparedStatement 來(lái)操作，用 JDBC 通用的 PreparedStatement 接口就能支持對(duì)數(shù)組類(lèi)型的賦值，難道是其他類(lèi)型的數(shù)據(jù)庫(kù)也能支持類(lèi)似的數(shù)組操作？

鏈接：

Upsert Records with PostgresSQL 9.5

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。