前言

批量插入由于mysql的VALUES原生支持，使用較為便利。

批量更新的寫(xiě)法一般有三種，在更新數(shù)量較少的情況下，前兩種性能不相上下。但是在更新字段增加，更新條數(shù)較多(500以上)建議使用第三種寫(xiě)法。

• 常規(guī)寫(xiě)法，拼接多個(gè)單條更新語(yǔ)句。
• CASE...WHEN... 寫(xiě)法
• JOIN 寫(xiě)法

Batch Update

spring/mybatis/JDBI都支持這種批量更新方式。
這種更新方式需要設(shè)置jdbc連接的參數(shù)：

allowMultiQueries=true # 完整url舉例 jdbc.url=jdbc:mysql://localhost:3306/db_name？useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

具體實(shí)現(xiàn)以Spring的JdbcTemplate為例。
batchUpdate的主要代碼如下圖：

首先檢查了jdbc連接是否支持批量更新操作，如果allowMultiQueries值為false，將被攔截。
然后拼接了SQL語(yǔ)句，拼接代碼如下：

直接用分號(hào)拼接。

CASE WHEN

示例:

UPDATE test SET code = ( CASE WHEN id = 1 THEN 11 WHEN id = 2 THEN 22 WHEN id = 3 THEN 33 END ) WHERE id IN (1,2,3);

注意：CASE WHEN一定要和WHERE語(yǔ)句一起使用，否則UPDATE會(huì)遍歷和更新數(shù)據(jù)庫(kù)中所有的行。會(huì)把未出現(xiàn)在WHEN中的數(shù)據(jù)都更新成null，如果code列設(shè)置為NOT NULL則會(huì)報(bào)錯(cuò)，否則會(huì)置為NULL或者默認(rèn)值。

JOIN

這種寫(xiě)法不太常見(jiàn)。

UPDATE `test` a JOIN ( SELECT 1 AS id, 11 AS code, 'holy' AS name UNION SELECT 2 AS id, 22 AS code, 'shit' AS name ) b USING(id, code) SET a.name=b.name;

上述SQL要表達(dá)的更新語(yǔ)義是：將id=1且code=11的name更新為'holy'，將id=2且code=22的name更新為'shit'。
注意，條件字段必須放在USING中

性能對(duì)比

RC隔離級(jí)別

更新條數(shù)小(一般小于500條)，CASE WHEN和JOIN優(yōu)于UDPATE。
更新條數(shù)較大(千級(jí)別)，CASE WHEN效率迅速下降，UPDATE居中，推薦使用JOIN寫(xiě)法

RR隔離級(jí)別

JOIN性能優(yōu)于CASE WHEN，CASE WHEN優(yōu)于UPDATE。

RC級(jí)別下的測(cè)試數(shù)據(jù)

數(shù)據(jù)庫(kù)實(shí)例規(guī)格：8核，20G內(nèi)存，100G硬盤(pán)
隔離級(jí)別：READ-COMMITTED
連接方式：JDBC

更新方式更新300條記錄平均耗時(shí)更新3000條記錄平均耗時(shí)

UPDATE	230ms	560ms
CASE WHEN	110ms	1170ms
JOIN	100ms	320ms

轉(zhuǎn)載于:https://www.cnblogs.com/AaronCui/p/10968893.html

總結(jié)

以上是生活随笔為你收集整理的Mysql批量更新的三种方式的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。