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

[liberize]

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

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

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

需要将更新操作合并为一条 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. 方法 1 ：因为有其他字段，不希望 id 不存在时 insert ，所以不能使用；
2. 方法 2 ： c1 和 c2 的增量时相同的，用两个 case...when 感觉没有必要，但又不知道如何只用一个；
3. 方法 3 ：随着数据量增加，效率显著降低；

[shoaly]

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

[hp3325]

不能用事务来处理？

[cxbig]

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

[skydiver]

大量数据不应该直接 load data 么……

[billgreen1]

我一般都删除然后插入

[liberize]

@shoaly 暂时不考虑重写，太麻烦，没时间。
@cxbig 数据其实也不算太多，大概几万条。用临时表没办法写成一条吧。
@skydiver 数据不是从文件读的。
@hp3325 不需要事务的原子性，可以只有部分记录更新成功。

[liberize]

@billgreen1 不能删啊，有其他字段。

[zhujinliang]

我一般先做一个临时表，把要更新的 ID 和对应的新值写进去，然后用 UPDATE … INNER JOIN 临时表 来更新
做临时表时可以批量 INSERT ，而且实际更新只发生在 update 语句，如果中间出错，丢弃临时表即可