mysql批量更新

最近有用到mysql批量更新，使用最原始的批量update发现性能很差，将网上看到的总结一下一共有以下三种办法：

1.批量update，一条记录update一次，性能很差
update test_tbl set dr='2' where id=1;

2.replace into 或者insert into ...on duplicate  key update

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

或者使用
insert into test_tbl (id,dr) values   (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

3.创建临时表，先更新临时表，然后从临时表中update
create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values   (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有temporary 表的create 权限。

下面是上述方法update 100000条数据的性能测试结果：

逐条update
real       0m15.557s
user       0m1.684s
sys       0m1.372s

replace into
real       0m1.394s
user       0m0.060s
sys       0m0.012s

insert into on duplicate key update
real       0m1.474s
user       0m0.052s
sys       0m0.008s

create temporary table and update:
real       0m0.643s
user       0m0.064s
sys       0m0.004s

就测试结果来看，测试当时使用replace into性能较好。
replace into   和insert into on duplicate key update的不同在于：
replace into　操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值

insert into 则是只update重复记录，不会改变其它字段。

MySql快速插入以及批量更新

 

插入：

 

MySql提供了可以一次插入多条数据的用法：

 

[sql] 

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12)...;  

在程序中可以通过循环，添加Values对应的列表，最后使用一次executeUpdate完成插入操作。但是Mysql语句并不是越长越好，MYsql语句长度有限制，可以查看mysql的配置文件my.in中max_allowed_packet属性，并进行相应设置。

更新：

 

Mysql中没有提供像Insert一样一次更新多条记录，需要逐条语句拼接。

 

[sql] 

update weibo set userName = 'xyw' where id = '22';update weibo set userID = '143' where id = '35';  

你可以使用addBatch语句，将拼接起来的SQL语句进行一次性处理，但是效率并不高。

还需要处理resultset的释放问题，否则mysql会报错："Commands out of sync; you can't run this command now"

针对update语句，虽然并没有resultset返回，但仍然需要释放。而由于未知原因（可能是sql语句太长？），释放resultset非常耗时，最终算下来得不偿失。

针对以上的不足，可以使用另一种方法执行批量更新。

[sql] 

INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES(col_value1,col_value2,...), (col_value1,col_value2,...)ON DUPLICATE KEY UPDATE userName=VALUES(userName)  

使用这种方法必须满足条件：col_name1, col_name2,...中必须有主键或者唯一键。

userName是要更新的列。

 

如果想一次更新多列，可以在userName=VALUES(userName)后面继续添加，例如：

 

[sql] 

INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES(col_value1,col_value2,...), (col_value1,col_value2,...)ON DUPLICATE KEY UPDATE userName=VALUES(userName), userID = VALUES(userID)  

这样就可以同时更新userName和userID两个字段。

它的实现原理是，首先Mysql根据表名后面列出的主键，查找表（因为是主键，所以在表中唯一存在）。如果存在该行数据，则按照最后的col_name = values(col_name)列表对相应的字段，按照values列表中给出的值进行更新。建议：表名后面的字段列表，除了主键之外，列出来的最好都作为更新的对象，即在语句最后都要有相应的col_name = values(col_name)，否则，你在表名后罗列出来字段，在values中赋值了，但是不是更新的对象，显然是浪费。

 

如果不存在该行数据，则进行插入操作，没有作为更新对象的列按照默认值填充（前提是Mysql运行在非严格模式下。如果在严格模式下，没列都需要有默认值，否则运行出错）。

 

注意：

 

主键可以作为更新的对象 ，但是只是在表中不存在该记录时起作用，即执行了插入操作，如果表中已经存在了该主键对应的行数据，下次更新时不会再插入该行，而是执行除了主键之外的其他列的更新操作。所以最好不要将主键设置为更新的对象。

 

实例：

 

[sql] 

INSERT INTO keywordtable(id, keyword, userName, userID) VALUES(1, '你好', 'Eliot', 22), (2, 'hello', 'Jhon', 23),   

(3, '嘻嘻', 'Jim', 24) ON DUPLICATE KEY UPDATE keyword=VALUES(keyword), userName=VALUES(userName),userID=VALUES(userID);  

除了id外，字段有keyword, userName, userID，他们是要更新的字段。