PGSQL 批量更新
概述
在我们的平常业务中,经常需要批量更新数据,例如:现阶段需要更新 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 系统表数据膨胀!
本文作者:booleandev
本文链接:https://www.cnblogs.com/booleandev/p/18297857/pgsql-batch-update-zbsjc4
版权声明:本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步