MySQL如何实现数据批量更新

电玩女神 2024-03-23 22:28 117阅读 0赞

最近需要批量更新大量数据,习惯了写sql,所以还是用sql来实现,下面这篇文章主要给大家总结介绍了关于MySQL批量更新的方式,需要的朋友可以参考下

### 根据不同条件批量更新同一值 ###

这种场景直接通过update语句更新即可，如：

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>UPDATE</code> <code>t_sys_user </code><code>SET</code> <code>`</code><code>desc</code><code>` = </code><code>'CaseWhen-0'</code> <code>WHERE</code> <code>id </code><code>IN</code> <code>(1001,1002,1003);</code> </td> 
 </tr> 
 </tbody> 
</table>

### 根据不同条件更新不同值 ###

#### 循环遍历逐条更新 ####

*  这种方式虽然简单，但是每更新一条数据就需要连接一次数据库，数据量多一点就会非常耗性能

#### foreach多条更新： ####

*  通过foreach组装成多条update语句，实现一次数据库连接执行多条update语句
 *  但Mybatis映射文件中的sql语句默认是不支持以" ; " 结尾的，也就是不支持多条sql语句的执行
 *  所以需要在连接mysql的url上加 `&allowMultiQueries=true`这个才可以执行

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>&lt;</code><code>update</code> <code>id</code><code>=</code><code>"updateForeach"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>foreach</code> <code>collection</code><code>=</code><code>"list"</code> <code>item</code><code>=</code><code>"param"</code> <code>separator</code><code>=</code><code>";"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>UPDATE t_sys_user</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>set</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>if</code> <code>test</code><code>=</code><code>" param.desc != null and param.desc != ''"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>`desc` = #{param.desc}</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>if</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>set</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>WHERE id = #{param.id}</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>foreach</code><code>&gt;</code> <code>&lt;/</code><code>update</code><code>&gt;</code> </td> 
 </tr> 
 </tbody> 
</table>

#### case when 进行数据批量更新： ####

*  通过 case when 的语法特性来实现根据不同条件更新不同值，也是进行一次数据库连接即可；当然，如果需要更新多个字段也是可以的
 *  这里提一下：由于表中有个desc字段是mysql关键字，所以需要用\`斜单引号符号来处理一些
 *  值得注意：使用case when 更新时，最好也指定where条件，不然会进行全表扫描再更新

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 </td> 
 <td> <code>&lt;</code><code>update</code> <code>id</code><code>=</code><code>"updateBatchCaseWhen"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>UPDATE t_sys_user</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>set</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>trim</code> <code>prefix</code><code>=</code><code>"`DESC`= CASE id"</code> <code>suffix</code><code>=</code><code>"END,"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>foreach</code> <code>collection</code><code>=</code><code>"list"</code> <code>item</code><code>=</code><code>"param"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>if</code> <code>test</code><code>=</code><code>"param.id != null"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>WHEN #{param.id} THEN #{param.desc}</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>if</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>foreach</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>trim</code><code>&gt;</code> <code>&lt;!--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;trim prefix="username = CASE id" suffix="END,"&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;foreach collection="list" item="param"&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;if test="param.username != null and param.username != ''"&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>WHEN #{param.} THEN #{param.username}</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/if&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/foreach&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/trim&gt;&nbsp;&nbsp; </code> <code>--&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>set</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>where</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id in</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>foreach</code> <code>collection</code><code>=</code><code>"list"</code> <code>item</code><code>=</code><code>"param"</code> <code>separator</code><code>=</code><code>","</code> <code>open</code><code>=</code><code>"("</code> <code>close</code><code>=</code><code>")"</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>#{param.id}</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>foreach</code><code>&gt;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;/</code><code>where</code><code>&gt;</code> <code>&lt;/</code><code>update</code><code>&gt;</code> </td> 
 </tr> 
 </tbody> 
</table>

*  我个人本地测了一下，40w数据使用case when更新1000条用400ms-500ms，使用foreach用时2000ms左右
 *  循环遍历逐条更新没测，想想也是最慢的
 *  如果数据量大，case when更新多个字段估计也够呛，要遍历多次（上述代码5-10行）构建SQL

到此这篇关于MySQL实现数据批量更新功能详解的文章就介绍到这了,希望可以帮到你。

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com/][Link 1]

[Link 1]: https://www.weidianyuedu.com/