Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people don't know what to do about how to achieve batch update of Update in Mysql. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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 records different field values? At first you might think of a way to execute multiple UPDATE statements using a loop, like the following example of a python program:

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 the SQL query is executed more than once in the loop statement. When we do system optimization, we always want to reduce the number of database queries as much as possible in order to reduce resource consumption and improve the speed of the system. Fortunately, there is a better solution, except that the SQL statement is a little more complex, but you only need to execute the query once, with the following syntax:

UPDATE mytable SET myfield = CASE other_field WHEN 1 THEN 'value' WHEN 2 THEN' value' WHEN 3 THEN 'value' ENDWHERE id IN

This kind of 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 value of id field.

If you need to update multiple fields of a row of records, 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' ENDWHERE id IN

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

However, this has a drawback: the problem to note is the length of the SQL statement, which needs to take into account the string length supported by the program's running environment, which can also be extended by updating the mysql setting.

Of course, a language as powerful as python also provides us with such a powerful method as executemany, which can not only insert data, but also update data as a troublemaker, and these things have to be used frequently with each other to compare the results.

Update_sql =''UPDATE mayi_order_image set order_city =% swhere 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) finish reading the above Have you mastered how to implement Update batch updates in Mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.