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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

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

有一张表 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 ：随着数据量增加，效率显著降低；

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 ...