How does mysql update data in batches efficiently?

UPDATE kt_accbalance 
SET accbalance_ep = CASE user_id 
WHEN 10001 THEN
accbalance_ep + 8 
WHEN 10002 THEN
accbalance_ep + 5 
WHEN 10004 THEN
accbalance_ep + 3 
WHEN 10006 THEN
accbalance_ep + 10 
WHEN 10039 THEN
accbalance_ep + 100 
END 
WHERE
user_id IN ( 10001, 10002, 10004, 10006, 10039 )

this is a batch update of 5 items. If 2000 items are updated in batch, it means that there are 2000 user_id to be updated. 2000 of this length is scary. Can mysql handle this long? 2000 sql statements are 400 times as long as they are now. Can mysql be allowed? can sql be this long? If not, are there any other high-performance alternatives?

refer to this article: reference

Apr.19,2021

the maximum length of the default statement of mysql is 1m and 2000 items is not a big problem. But you can choose to use a familiar language to spell sql statements, read userid, from the file to support more userid, more efficient


problem analysis: in the mysql statement, there is a case when conditional processing, this conditional processing may directly lead to the whole sql statement because the length is too long and can not run normally.
solution: split the conditional processing of case when from mysql and let php handle it. For example, traversing all user_id, in a loop splices one update statement each time (for a single user only), and then completes the mysql_query in the body of the loop.


updating 2W items in batches with Medoo is not a big problem.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b32cef-2ab8a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b32cef-2ab8a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?