摘要.

# 用一条 SQL 批量更新大量记录的最高效做法？

liberize ·

liberize · 2016-04-22 20:44:44 +08:00 · 16968 次点击

这是一个创建于 1762 天前的主题，其中的信息可能已经有所发展或是发生改变。

有一张表 T ，几个字段 id, c1, c2 ...，其中 id 为主键。

现在有大量数据需要更新，由于以下原因：

后台仍然使用旧的 php 的 mysql 扩展，一次 query 只能包含一条 SQL 语句
数据库和脚本不在一台机器，需要考虑网络开销

需要将更新操作合并为一条 SQL 。

目前已知大致有这么几种方法：

INSERT INTO T (id, c1, c2)
VALUES (1, 1, 1), (2, 2, 2)
ON DUPLICATE KEY
UPDATE c1 = c1 + VALUES(c1), c2 = c2 + VALUES(c2);

UPDATE T
SET c1 = c1 + CASE id 
             WHEN 1 THEN 1 
             WHEN 2 THEN 2 
         END, 
    c2 = c2 + CASE id 
             WHEN 1 THEN 1 
             WHEN 2 THEN 2 
         END
WHERE id IN (1, 2);

UPDATE T t
JOIN (
    SELECT 1 as id, 1 as x,
    UNION ALL
    SELECT 2, 2
) v ON t.id = v.id
SET c1 = c1 + x, c2 = c2 + x;

目前的问题是：

方法 1 ：因为有其他字段，不希望 id 不存在时 insert ，所以不能使用；
方法 2 ： c1 和 c2 的增量时相同的，用两个 case...when 感觉没有必要，但又不知道如何只用一个；
方法 3 ：随着数据量增加，效率显著降低；

16968 次点击 ∙ 3 人收藏

加入收藏 Tweet 忽略主题

感谢

[

when](/tag/when)[

then](/tag/then)[

values](/tag/values)[

SQL](/tag/SQL)

8 条回复 • 2016-04-23 08:51:01 +08:00

| | |

隐藏感谢回复者

shoaly 2016-04-22 22:25:59 +08:00

有 php 代码就肯定能找到 mysql 的访问账号,
直接重新写就可以, 抛开老代码?

| | |

隐藏感谢回复者

hp3325 2016-04-22 23:07:55 +08:00

不能用事务来处理？

| | |

隐藏感谢回复者

cxbig 2016-04-22 23:16:21 +08:00

我很奇怪为啥不换 pdo ，如果数据量很大，我建议还是用 INSERT ，做一个临时表，新数据先放进去，再用
INSERT INTO dest (...)
SELECT ...
FROM tmp
INNER JOIN dest ON dest.id = tmp.id
ON DUPLICATE KEY UPDATE ...

| | |