mysql批量update语句优化

Love The Way You Lie 2022-06-13 05:29 483阅读 0赞

对[MySQL][]中的表进行批量update的时候，如果每个记录用一个update语句，5000条记录的话，大概需要30秒钟。下面采用的case when方法可以把5000条update拼成一条：

**\[plain\]**  [view plain][]   [copy][view plain]

1.  Update myTable  
2.    SET myField = CASE id  
3.      WHEN 1 THEN 'value1'  
4.      WHEN 2 THEN 'value2'  
5.      WHEN 3 THEN 'value3'  
6.    END  
7.  WHERE id IN (1,2,3)

具体的MyBatis代码如下：

**\[html\]**  [view plain][]   [copy][view plain]

1.  **<****update** id="updatePrice" parameterType="java.util.List"**>**  
2.      UPDATE t\_price  
3.      SET price = CASE code  
4.      **<****foreach** collection="list" item="item"**>**  
5.          WHEN \#\{item.code\} THEN \#\{item.price\}  
6.      **</****foreach****>**  
7.      END  
8.      WHERE  
9.          code in  
10.         **<****foreach** collection="list" item="item" open="(" separator="," close=")"**>**  
11.          \#\{item.code\}   
12.         **</****foreach****>**  
13. **</****update****>**

实际上，为了避免一条语句超过MySQL语句的最大长度限制，一般会把5000个数据分成几组，每组拼凑一条语句。因此，Java代码调用MyBatis的dao之前需要拆分被update的List，以避免sql语句超长。比如按1000条每组来拆分，使用Guava的工具类Lists中的partition方法：

**\[java\]**  [view plain][]   [copy][view plain]

1.  **import** com.google.common.collect.Lists;  
2.      ......  
3.      List<List<Map<String, Object>>> list = (List<List<Map<String, Object>>>) Lists.partition(orders, 1000);  
4.      **int** success = 0;  
5.      **for** (List<Map<String, Object>> l : list) \{  
6.          success += priceDao.updatePrice(l);  
7.      \}

这样优化后，原来的5000条update语句，就被合并成了5条，执行时间缩短到了2秒钟以内。

[MySQL]: http://lib.csdn.net/base/mysql
[view plain]: http://blog.csdn.net/clementad/article/details/50083703#