Several Ways of Batch Update for Mysql Update

Typically, we use the following SQL statement to update the field value:

UPDATE mytable SET myfield='value' WHERE other_field='other_value';

But what if you want to update multiple rows of data and each row of records has a different field value? At first, you may think of the way to execute multiple UPDATE statements in a loop, just like the following python program example:

for x in xrange(10):
  sql = ''' UPDATE mytable SET myfield='value' WHERE other_field='other_value'; '''

There is nothing wrong with this method, and the code is easy to understand, but SQL query is executed more than once in the loop statement. When doing system optimization, we always want to reduce the number of database queries as much as possible to reduce resource occupation and improve system speed. Fortunately, there is a better solution, except that the SQL statement is slightly more complicated, but only needs to execute the query once, and the syntax is as follows:

UPDATE mytable
  SET myfield = CASE other_field
    WHEN 1 THEN 'value'
    WHEN 2 THEN 'value'
    WHEN 3 THEN 'value'
  END
WHERE id IN (1,2,3)

This SQL statement is easy to understand, that is, the keyword CASE, which is common in many programming languages, is used to judge the type of different branches according to the field value of id.

If you need to update multiple fields of a 1-row record, you can use the following SQL statement:

UPDATE categories
  SET display_order = CASE id
    WHEN 1 THEN 3
    WHEN 2 THEN 4
    WHEN 3 THEN 5
  END,
  title = CASE id
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
  END
WHERE id IN (1,2,3)

The above scheme greatly reduces the number of database query operations and greatly saves system resources

However, this has a disadvantage: the problem to pay attention to is the length of SQL statement, which needs to consider the string length supported by the program running environment. Of course, this can also be extended by updating the settings of mysql.

Of course python such a powerful language also provides us with executemany such a powerful method, it can not only insert data, but also can be used to update data. As a person who often does things, these things should often be used with each other to compare the results

update_sql = ''' UPDATE mayi_order_image 
set order_city = %s
where user_ip = %s and dt = %s and id = %s 
 and user_ip is not null and (order_city is null or order_city = '' )
 '''
pp = []
for x in xrange(len(result)):
  ip = result[x][0]
  id_ = result[x][1]
  add = dbip.lookup(str(ip))
  adds = add.split('\t')
  address = str(adds[0]) + ','+str(adds[1] )+ ','+ str(adds[2])
  pp.append((address,ip,end,id_))
  if x%5000 == 0:
    saveLog_many(update_sql,pp)
    pp = []
saveLog_many(update_sql,pp)

Is this more convenient 1, but is the speed problem I feel that it can be combined with the second one, and it will be better to compare 1

Summarize