Mysql批量更新 [英] Mysql Bulk Update

问题描述

我必须通过sql在表上执行〜6k更新查询(无Hibernate/JDBC).查询就像

I have to execute ~6k update queries on a table through sql (No Hibernate/JDBC). Query is something like

UPDATE A 
SET 
    some_id = 'value1'
WHERE
    id = 'value2';

执行所有这些查询花费的时间太长.有没有办法提高性能?

It takes too long to execute all these queries. Is there a way to improve the performance?

推荐答案

创建一个临时表(仅包含value1和value2值)并批量填充(即，您可以使用单个insert语句来完成此操作).然后使用现有表和临时表之间的联接进行更新.

Create a temp table (containing just the value1 and value2 values) and populate it in bulk (ie, you can potentially do this with a single insert statement). Then do an update using a join between your existing table and the temp table.

类似

INSERT INTO SomeTempTable(id, some_id)
VALUES (1,2), (3,4), (5,6), .......

UPDATE A
        INNER JOIN
    SomeTempTable ON A.id = SomeTempTable.id 
SET 
    A.some_id = SomeTempTable.some_id;    

这篇关于Mysql批量更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！