mysql大批量数据更新

批量更新操作常用的方式如下：

1.replace into

本质是先delete重复数据，然后再insert数据，非空字段会设置成默认值，极其危险慎用，可能清空大量数据

replace into tst (id,attr) values (1,'2'),(2,'3'),...(x,'y');

2.insert into

只更新主键id记录的需要更新的字段，其他字段不清除。如果主键id不存在，会新建一条记录。下面例子只更新attr1，保留attr2的原值。

insert into test (id,attr1,attr2) values (1,'2',''),(2,'3',''),...(x,'y','') on duplicate key update attr1=values(attr1);

3.按照更新表新建临时表，然后将需要导入的数据写入临时表，最后更新主表

4.mysql语句中通过case when来判断更新，这种方式适合确定小数目的情况

UPDATE test 
    SET attr1 = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
    END, 
    attr2 = CASE id 
        WHEN 1 THEN 6
        WHEN 2 THEN 7
        WHEN 3 THEN 8
    END
WHERE id IN (1,2,3)

 

可能遇到的错误：

ERROR 2006 (HY000): MySQL server has gone away

首先查看mysql允许的最大数据输入上线

mysql> show variables like 'max_allowed_packet';

修改最大输入上限为16M

mysql> set max_allowed_packet=16777216; 

还有一个可能出现的问题是mysql引擎运行超时

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> show variables like 'innodb_lock_wait_timeout';

mysql> set innodb_lock_wait_timeout=1000;

还有一点建议就是，如果更新数据量过大，建议讲大数据量切割成较小块运行，虽然提高了运行次数，但是可以大大加快效率。