首页 > 解决方案 > 有没有办法将许多 upserts 批量发送到 mysql db?

问题描述

当前进程正在执行如下代码所示的 upsert。

但是,大量的请求似乎会导致问题。

有没有办法用更少的请求来做到这一点?

1.

INSERT INTO example_table (id, prop_a, prop_b) VALUES (1,1,7) 
ON DUPLICATE KEY UPDATE prop_a=1,prop_b=7;

-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       7       0
-------------------------------------------------------------
INSERT INTO example_table (id, prop_b, prop_c) VALUES (1,5,8) 
ON DUPLICATE KEY UPDATE prop_b=5,prop_c=8;
-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       5       8
-------------------------------------------------------------
INSERT INTO example_table (id, prop_a, prop_b) VALUES (7,3,3) 
ON DUPLICATE KEY UPDATE prop_a=3,prop_b=3;
-------------------------------------------------------------
id(primary key), prop_a, prop_b, prop_c
1                1       5       8
7                3       3       0
-------------------------------------------------------------

标签: mysqlsqlamazon-aurora

解决方案


INSERT INTO example_table (id, prop_a, prop_b, prop_c) 
SELECT 1,1,7,0 UNION ALL
SELECT 1,5,0,8 UNION ALL
SELECT 7,3,3,0
ON DUPLICATE KEY UPDATE 
prop_a=CASE WHEN VALUES(prop_a) = 0 
            THEN COALESCE(prop_a, 0)
            ELSE VALUES(prop_a) END,
prop_b=CASE WHEN VALUES(prop_b) = 0 
            THEN COALESCE(prop_b, 0)
            ELSE VALUES(prop_b) END,
prop_c=CASE WHEN VALUES(prop_c) = 0 
            THEN COALESCE(prop_c, 0)
            ELSE VALUES(prop_c) END;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=64a0ef6520c65de8ac7e97ea90dc40d5


推荐阅读