随笔 - 391  文章 - 0  评论 - 88 

Mysql批量更新的三种方式

From: https://www.cnblogs.com/AaronCui/p/10968893.html

前言

批量插入由于mysql的VALUES原生支持,使用较为便利。

批量更新的写法一般有三种,在更新数量较少的情况下,前两种性能不相上下。但是在更新字段增加,更新条数较多(500以上)建议使用第三种写法。

  • 常规写法,拼接多个单条更新语句。
  • CASE...WHEN... 写法
  • JOIN 写法

Batch Update

spring/mybatis/JDBI都支持这种批量更新方式。
这种更新方式需要设置jdbc连接的参数:

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

具体实现以Spring的JdbcTemplate为例。
batchUpdate的主要代码如下图:

首先检查了jdbc连接是否支持批量更新操作,如果allowMultiQueries值为false,将被拦截。
然后拼接了SQL语句,拼接代码如下:

直接用分号拼接。

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语句一起使用,否则UPDATE会遍历和更新数据库中所有的行。会把未出现在WHEN中的数据都更新成null,如果code列设置为NOT NULL则会报错,否则会置为NULL或者默认值。

JOIN

这种写法不太常见。

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要表达的更新语义是:将id=1且code=11的name更新为'holy',将id=2且code=22的name更新为'shit'。
注意,条件字段必须放在USING

性能对比

RC隔离级别

更新条数小(一般小于500条),CASE WHENJOIN优于UDPATE
更新条数较大(千级别),CASE WHEN效率迅速下降,UPDATE居中,推荐使用JOIN写法

RR隔离级别

JOIN性能优于CASE WHENCASE WHEN优于UPDATE

RC级别下的测试数据

数据库实例规格:8核,20G内存,100G硬盘
隔离级别:READ-COMMITTED
连接方式:JDBC

更新方式更新300条记录平均耗时更新3000条记录平均耗时
UPDATE 230ms 560ms
CASE WHEN 110ms 1170ms
JOIN 100ms 320ms
posted on 2020-10-12 11:37  清清飞扬  阅读(143)  评论(0编辑  收藏