Summary of two ways of MYSQL batch UPDATE

It is actually quite common to encounter batch updates during work.
However, how to correctly perform batch UPDATE is often a bit overwhelming.
Here are 2 available methods for selection (please choose method 1, manual dog head.).

If MyBatis enhancement component MyBatisPlus is used, please refer toOfficial websiteThe solution given (updateBatchById), or check it yourself.

Batch UPDATE method one: foreach in SQL

Give it one 🌰

<update  parameterType="">
	<foreach collection="list" item="entity" separator=";">
		UPDATE sys_user
		SET password=#{},age=#{}
		<where>
			id = #{}
		</where>
	</foreach>
</update>

Writing like this is definitely more efficient than for looping single updates in business methods.
However, if you encounter large batches of update actions, it may also cause inefficiency.
The reason is that the foreach in SQL is essentially a loop insertion of each piece of data, which will generate()Each UPDATE statement will be sent to the database server for execution separately.
This means that if there are 100 elements in the list, 100 database round trip communications will be generated.
This approach is not only inefficient, but also can lead to performance bottlenecks and waste of resources for large batch operations.

Optimization: Batch processing via JDBCpassMyBatisofSqlSessionProvided batch processing functions to manually perform batch updates.

try (SqlSession session = ()) {
    UserMapper mapper = ();
    for (UserEntity user : userList) {
        (user);
    }
    ();
}

Here is a single UPDATE statement.

In this way, MyBatis will accumulate all update commands in memory and then call()Submitting to the database at one time is much more efficient than executing it one by one.

Note: Whether there are any efficiency differences, it has not been practiced! ! ! There may be suspicion of misleading people's children.

Batch UPDATE method 2: INSERT + ON DUPLICATE KEY UPDATE

 <update  parameterType="">
    insert into sys_user
    (id,username,password) values
    <foreach collection="list" index="index" item="item" separator=",">
        (#{},
        #{},
        #{})
    </foreach>
    ON DUPLICATE KEY UPDATE
     password=values(password)
</update>

Not recommended for use. There are many requirements and are prone to deadlocks.

Things to note

• Unique key constraints: Make sure that the id field in the sys_user table has a unique key constraint (usually a primary key). If id is not unique, ON DUPLICATE KEY UPDATE will not trigger an update operation.
• Performance: This method is much more efficient in the case of large data volumes than multiple separate INSERT and UPDATE operations.
• Transaction management: Ensure that this operation is executed in a transaction to ensure data consistency. If an error occurs in the middle, the entire operation can be rolled back.
• Field Order: Ensure that the order of fields in the VALUES function is consistent with the order of fields in the ON DUPLICATE KEY UPDATE clause.

Summarize:

It is recommended to use method one, or its optimization method (JDBC batch processing).

This is the end of this article about two methods of MYSQL batch UPDATE. For more related content on MYSQL batch UPDATE, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!