mysql 批量更新与批量更新多条记录的不同值

在mysql中批量更新我们可能使用update,replace into来操作,下面小编来给各位同学详细介绍mysql 批量更新与性能吧.

批量更新,mysql更新语句很简单,更新一条数据的某个字段,一般这样写,代码如下:

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';

如果更新同一字段为同一个值,mysql也很简单,修改下where即可,代码如下:

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

这里注意 ‘other_values’ 是一个逗号（,）分隔的字符串,如,1,2,3,那如果更新多条数据为不同的值,可能很多人会这样写,代码如下:

1. foreach($display_orderas$id=>$ordinal){
2. $sql="UPDATEcategoriesSETdisplay_order=$ordinalWHEREid=$id";
3. mysql_query($sql);
4. }

即是循环一条一条的更新记录,一条记录update一次,这样性能很差,也很容易造成阻塞,那么能不能一条sql语句实现批量更新呢？mysql并没有提供直接的方法来实现批量更新,但是可以用点小技巧来实现,代码如下:

1. UPDATEmytable
2. SETmyfield=CASEid
3. WHEN1THEN'value'
4. WHEN2THEN'value'
5. WHEN3THEN'value'
6. END
7. WHEREidIN(1,2,3)

这里使用了case when 这个小技巧来实现批量更新,举个例子,代码如下:

1. UPDATEcategories
2. SETdisplay_order=CASEid
3. WHEN1THEN3
4. WHEN2THEN4
5. WHEN3THEN5
6. END
7. WHEREidIN(1,2,3)

这句sql的意思是,更新display_order 字段,如果id=1 则display_order 的值为3,如果id=2 则 display_order 的值为4,如果id=3 则 display_order 的值为5,即是将条件语句写在了一起.

这里的where部分不影响代码的执行,但是会提高sql执行的效率,确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行.

如果更新多个值的话,只需要稍加修改,代码如下:

1. UPDATEcategories
2. SETdisplay_order=CASEid
3. WHEN1THEN3
4. WHEN2THEN4
5. WHEN3THEN5
6. END,
7. title=CASEid
8. WHEN1THEN'NewTitle1'
9. WHEN2THEN'NewTitle2'
10. WHEN3THEN'NewTitle3'
11. END
12. WHEREidIN(1,2,3)

到这里,已经完成一条mysql语句更新多条记录了,但是要在业务中运用,需要结合服务端语言,这里以php为例,构造这条mysql语句,代码如下:

1. $display_order=array(
2. 1=>4,
3. 2=>1,
4. 3=>2,
5. 4=>3,
6. 5=>9,
7. 6=>5,
8. 7=>8,
9. 8=>9
10. );
11. $ids=implode(',',array_keys($display_order));
12. $sql="UPDATEcategoriesSETdisplay_order=CASEid";
13. foreach($display_orderas$id=>$ordinal){
14. $sql.=sprintf("WHEN%dTHEN%d",$id,$ordinal);
15. }//phpfensi.com
16. $sql.="ENDWHEREidIN($ids)";
17. echo$sql;

这个例子,有8条记录进行更新,代码也很容易理解,你学会了吗.

性能分析:当我使用上万条记录利用mysql批量更新,发现使用最原始的批量update发现性能很差,将网上看到的总结一下一共有以下三种办法.

1.批量update,一条记录update一次,性能很差,代码如下:

update test_tbl set dr='2' where id=1;

2.replace into 或者insert into ...on duplicate key update,代码如下:

1. replaceintotest_tbl(id,dr)values(1,'2'),(2,'3'),...(x,'y');
2. --或者使用如下代码:
3. insertintotest_tbl(id,dr)values(1,'2'),(2,'3'),...(x,'y')onduplicatekeyupdatedr=values(dr);

3.创建临时表,先更新临时表,然后从临时表中update,代码如下:

1. createtemporarytabletmp(idint(4)primarykey,drvarchar(50));
2. insertintotmpvalues(0,'gone'),(1,'xx'),...(m,'yy');
3. updatetest_tbl,tmpsettest_tbl.dr=tmp.drwheretest_tbl.id=tmp.id;

注意:这种方法需要用户有temporary 表的create 权限,下面是上述方法update 100000条数据的性能测试结果.

逐条update:

1. real0m15.557s
2. user0m1.684s
3. sys0m1.372s
4. replaceinto
5. real0m1.394s
6. user0m0.060s
7. sys0m0.012s
8. insertintoonduplicatekeyupdate
9. real0m1.474s
10. user0m0.052s
11. sys0m0.008s
12. createtemporarytableandupdate:
13. real0m0.643s
14. user0m0.064s
15. sys0m0.004s

就测试结果来看,测试当时使用replace into性能较好.

replace into 和insert into on duplicate key update的不同在于,replace into　操作本质是对重复的记录先delete 后insert,如果更新的字段不全会将缺失的字段置为缺省值.insert into 则是只update重复记录,不会改变其它字段.