Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The method of fast insertion and batch update of MySql

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article mainly explains the "MySql fast insertion and batch update method", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySql fast insertion and batch update method"!

Insert:

MySql provides a way to insert more than one piece of data at a time:

[sql]

INSERT INTO tbl_name (a recordbpenc) VALUES (1 mine2, 3), (4, 5, 6), (7, 8, 9), (10, 11, 12).

In the program, you can add the list corresponding to Values through a loop, and finally use executeUpdate to complete the insertion operation. However, the Mysql statement is not as long as possible. The length of the MYsql statement is limited. You can view the max_allowed_packet property in the mysql configuration file my.in and set it accordingly.

Update:

There is no provision in Mysql to update more than one record at a time like Insert, which needs to be concatenated statement by statement.

[sql]

Update weibo set userName = 'xyw' where id =' 22 update weibo set userID = '143' where id ='35'

You can use the addBatch statement to process the spliced SQL statement at one time, but it is not efficient.

You also need to deal with the release of resultset, otherwise mysql will report an error: "Commands out of sync; you can't run this command now"

For the update statement, although there is no resultset return, it still needs to be released. And for unknown reasons (maybe the sql statement is too long? ), releasing resultset is very time-consuming, and in the end, the loss outweighs the gain.

In view of the above shortcomings, you can use another method to perform batch updates.

[sql]

INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES (col_value1,col_value2,...), (col_value1,col_value2,...) ON DUPLICATE KEY UPDATE userName=VALUES (userName)

To use this method, the conditions must be met: col_name1, col_name2,... There must be a primary key or a unique key in.

UserName is the column to update.

If you want to update more than one column at a time, you can continue to add after userName=VALUES (userName), for example:

[sql]

INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES (col_value1,col_value2,...), (col_value1,col_value2,...) ON DUPLICATE KEY UPDATE userName=VALUES (userName), userID = VALUES (userID)

This allows you to update both the userName and userID fields.

The principle of its implementation is that first Mysql looks up the table based on the primary key listed after the table name (because it is the primary key, it only exists in the table). If that line of data exists, the corresponding fields are updated according to the final col_name = values (col_name) list and the values given in the values list. Suggestion: the list of fields after the table name, except for the primary key, is best listed as an updated object, that is, there must be a corresponding col_name = values (col_name) at the end of the statement, otherwise, you list the fields after the table name and assign a value in values, but it is not an updated object, which is obviously a waste.

If that row of data does not exist, the insert operation occurs, and the column as the update object is not populated by the default value (provided that Mysql is running in non-strict mode. If you are in strict mode, you need to have a default value for all columns, otherwise there will be an error.

Note:

The primary key can be used as an updated object, but it only works when the record does not exist in the table, that is, the insert operation is performed, and if the row data corresponding to the primary key already exists in the table, the row will not be inserted in the next update. instead, update operations are performed for columns other than the primary key. So it's best not to set the primary key as an updated object.

Example:

[sql]

INSERT INTO keywordtable (id, keyword, userName, userID) VALUES (1, 'Hello', 'Eliot', 22), (2,' hello', 'Jhon', 23)

(3, 'hee hee', 'Jim', 24) ON DUPLICATE KEY UPDATE keyword=VALUES (keyword), userName=VALUES (userName), userID=VALUES (userID)

In addition to id, the fields include keyword, userName, and userID, which are the fields to be updated.

Thank you for reading, the above is the content of "MySql fast insertion and batch update method". After the study of this article, I believe you have a deeper understanding of the method of MySql rapid insertion and batch update, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report