Several ways to update batches in MySQL

Several ways to update batches in MySQL

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

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

But what would you do if you want to update multiple rows of data and the field values ​​in each row are different? At first you might think of using a loop to execute multiple UPDATE statements, 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 simple and easy to understand, but more than one SQL query is executed in the loop statement. When optimizing the system, we always want to reduce the number of database queries as much as possible to reduce resource usage and improve system speed. Fortunately, there is a better solution. The SQL statement is a little more complicated, but you only need to execute one query. 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. It uses the keyword CASE, which is common in many programming languages, to perform type judgments for different branches based on the value of the id field.

If you need to update multiple fields in a row, 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 issue that needs attention is the length of the SQL statement. It is necessary to consider the string length supported by the program running environment. Of course, this can also be expanded by updating the MySQL settings.

Of course, Python, such a powerful language, also provides us with such a powerful method as executemany, which can not only insert data, but also update data. As a person who often does things, these things need to be used frequently 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? But as for the speed issue, I think it would be better to combine it with the second method for comparison.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:
  • MySQL batch update and batch update of different values ​​of multiple records
  • Summary of Mysql update multi-table joint update method
  • Batch modification, clearing, copying and other update commands for MySQL data table field contents
  • MySQL implements batch update of data in different tables

<<:  Axios project with 77.9K GitHub repository: What are the things worth learning?

>>:  How to check PCIe version and speed in Linux

Recommend

JS practical object-oriented snake game example

Table of contents think 1. Greedy Snake Effect Pi...

Sample code for implementing radar chart with vue+antv

1. Download Dependency npm install @antv/data-set...

Ubuntu MySQL version upgraded to 5.7

A few days ago, the library said that the server ...

Automatically install the Linux system based on cobbler

1. Install components yum install epel-rpm-macros...

Writing a shell script in Ubuntu to start automatically at boot (recommended)

The purpose of writing scripts is to avoid having...

jQuery achieves seamless scrolling of tables

This article example shares the specific code of ...

CSS to achieve dynamic secondary menu

Dynamically implement a simple secondary menu Whe...

Explanation of the process of docker packaging node project

As a backend programmer, sometimes I have to tink...

The shortest JS to determine whether it is IE6 (IE writing method)

Commonly used JavaScript code to detect which ver...

Diagram of the process of implementing direction proxy through nginx

This article mainly introduces the process of imp...

MySQL database operation and maintenance data recovery method

The previous three articles introduced common bac...