概述

在我們的平常業務中，經常需要批量更新數據，例如：現階段需要更新 1000 個包裹的上網時間，每個包裹的上網時間是不一樣的，假如我們一個一個包裹更新，則需要與數據庫進行 1000 次的交互，很大的消耗數據庫的性能，并且更新的速度也很慢，因此，我們通常需要進行批量更新數據。

數據生成

首先，我們需要生成一批測試數據，方便對批量更新進行測試。

創建數據表

CREATE TABLE users  
(  
    id         SERIAL PRIMARY KEY,  
    username   VARCHAR(255) NOT NULL UNIQUE,  
    password   VARCHAR(255) NOT NULL,  
    email      VARCHAR(255) NOT NULL UNIQUE,  
    status     VARCHAR(255),  
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,  
    created_by VARCHAR(255) DEFAULT 'system'  
);

批量生成數據

INSERT INTO  
    users (username, email, password, status)  
SELECT  
    SUBSTR(MD5(RANDOM()::TEXT), 10),  
    SUBSTR(MD5(RANDOM()::TEXT), 10) || '@qq.com',  
    MD5(RANDOM()::TEXT),  
    get_split_str('在職,離職')  
FROM  
    GENERATE_SERIES(1, 10000);

生成數據的相關函數：PGSQL 快速生成數據函數

生成的數據

方案

臨時表

將每次生成的數據插入至 PGSQL 的臨時表，然后使用 UPDATE table FROM tmp_table 方式進行數據更新。

注意：假如此條 SQL 需要大量的執行，則不推薦此種方案，此種方案將會導致pg_attribute 表發生數據膨脹,而這張表又屬于系統表，執行 VAUNCAM 的時候將會鎖庫，當數據膨脹后，VAUNCAM pg_attribute 將消耗大量的數據庫性能，因此，當批量更新頻率過大的時候，不推薦使用。

創建臨時表

create TEMP table  "users_temp"  AS SELECT * FROM "users" WHERE 1=2;

將待更新的數據插入至臨時表

INSERT INTO
    users_temp(id, username, password, email, status, created_at, created_by)
SELECT
    id, username, password, email, status, created_at, created_by
FROM
    users
WHERE
    id < 500
;

更新臨時表的數據

UPDATE users_temp SET status = '在職';

批量更新

UPDATE users SET status = tmp.status FROM users_temp tmp WHERE users.id = tmp.id;

插入更新

插入更新主要是使用插入數據時， ID 主鍵沖突，利用ON CONFLICT(id) DO UPDTAE 特性，當 ID 重復時，數據庫會自動執行更新語句。

下面是具體的 SQL

INSERT INTO users VALUES
(12, '152c336aa714f12a45608ed7', '87d166ef4357382a89709d243bf2f1bd', '1bd4c564afa1e5fee1f0f411@qq.com', '開心', '2024-02-23 16:33:22.133835', 'system'),
(13, '182eeda111284cbd39094ce0', 'e9f658d777fb7c83449166a430538ee2', '13675703c00f1040d762bef8@qq.com', '開心', '2024-02-23 16:33:22.133835', 'system'),
(14, '1d4ef943f76346625bbad9c6', '01a03911bc1039b68768dc62f55cae1d', '1a44dca3c9b6ca5329bbc415@qq.com', '開心', '2024-02-23 16:33:22.133835', 'system'),
(15, '1028f917f9fc53191564ce17', '031f3b53d6ce9eadbb2a15a69a4c223b', '19404aa589c9db19ee07ab75@qq.com', '開心', '2024-02-23 16:33:22.133835', 'system'),
(16, '164115c6e526c50ca41b7eba', '2165358e24236ce26b919cea7d0d1d86', '12aa7e3c1a23827b3e07d129@qq.com', '開心', '2024-02-23 16:33:22.133835', 'system')
ON CONFLICT(id)
    DO UPDATE SET
      id         = excluded.id,
      username   = excluded.username,
      password   = excluded.password,
      email      = excluded.email,
      status     = excluded.status,
      created_at = excluded.created_at,
      created_by = excluded.created_by
;

上面示例中，當主鍵沖突的時候，自動更新部分字段

子表

將待更新的數據放入 SQL 子表中，使用子表替換上面的臨時表，已達到 UPDATE table FROM 子表 效果，這種方案相對于臨時表方案，不會創建新表，不會造成數據膨脹問題

下面是示例的 SQL

UPDATE users
SET
    id         = temp.id,
    username   = temp.username,
    password   = temp.password,
    email      = temp.email,
    status     = temp.status,
    created_at = temp.created_at,
    created_by = temp.created_by
FROM
    (VALUES
         (12, '52c336aa714f12a45608ed7', '87d166ef4357382a89709d243bf2f1bd', '1bd4c564afa1e5fee1f0f411@qq.com', '快樂', '2024-02-23 16:33:22.133835'::TIMESTAMP, 'system'),
         (13, '82eeda111284cbd39094ce0', 'e9f658d777fb7c83449166a430538ee2', '13675703c00f1040d762bef8@qq.com', '快樂', '2024-02-23 16:33:22.133835'::TIMESTAMP, 'system'),
         (14, 'd4ef943f76346625bbad9c6', '01a03911bc1039b68768dc62f55cae1d', '1a44dca3c9b6ca5329bbc415@qq.com', '快樂', '2024-02-23 16:33:22.133835'::TIMESTAMP, 'system'),
         (15, '028f917f9fc53191564ce17', '031f3b53d6ce9eadbb2a15a69a4c223b', '19404aa589c9db19ee07ab75@qq.com', '快樂', '2024-02-23 16:33:22.133835'::TIMESTAMP, 'system'),
         (16, '64115c6e526c50ca41b7eba', '2165358e24236ce26b919cea7d0d1d86', '12aa7e3c1a23827b3e07d129@qq.com', '快樂', '2024-02-23 16:33:22.133835'::TIMESTAMP, 'system'))
    AS temp(id, username, password, email, status, created_at, created_by)
WHERE
    users.id = temp.id
;

實體表

這種方案使用實體表替換臨時表，每次 SQL 執行錢，執行 TRUNCAT TABLE 表名, 用來達到清空表數據的作用。

創建臨時表

CREATE TABLE users_tmp AS SELECT * FROM "users" WHERE 1=2;

待更新數據插入臨時表

INSERT INTO
    users_tmp(id, username, password, email, status, created_at, created_by)
SELECT
    id, username, password, email, status, created_at, created_by
FROM
    users
WHERE
    id < 500
;

更新臨時表中的數據

UPDATE users_tmp SET status = '在職';

批量更新數據

UPDATE users SET status = tmp.status FROM users_tmp tmp WHERE users.id = tmp.id;

清空表數據

TRUNCATE TABLE users_tmp;

總結

以上是各種執行 PGSQL 批量更新的方案，個人的推薦度是 插入更新 > 子表 > 實體表 > 臨時表。

插入更新比較推薦的理由是這種方案 SQL 寫起來結構比較清晰

最不推薦的是臨時表放哪，因為此種方案如果頻繁執行，會導致 PG 系統表數據膨脹！