MySQL Quick insert and batch update

MySQL Quick insert and batch update

Insert:

MySQL provides a way to insert multiple data at once:

[SQL]

INSERT into Tbl_name (a,b,c) VALUES (All-in-all), (4,5,6), (7,8,9), (10,11,12) ...;

In the program, you can add a list of values by looping, and finally use a executeupdate to complete the insert operation. But the MySQL statement is not the longer the better, the MySQL statement length is limited, you can view the MySQL configuration file my.in in the Max_allowed_packet property, and set the corresponding.

Update:

MySQL does not provide the same as an insert to update multiple records, need to stitch each statement.

[SQL]

Update Weibo set userName = ' Xyw ' WHERE id = ' n '; update Weibo set UserID = ' 143 ' WHERE id = ' 35 ';

You can use the Addbatch statement to do a one-time processing of the stitched SQL statements, but the efficiency is not high.

Also need to deal with ResultSet release problem, otherwise MySQL will error: "Commands out of sync; You can ' t run the This command now "

The UPDATE statement, although not ResultSet returned, still needs to be freed. And for an unknown reason (perhaps the SQL statement is too long?) ), releasing ResultSet is time consuming and ultimately not worth the candle.

For these deficiencies, you can use a different method to perform a bulk update.

[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)

The condition must be met using this method: Col_name1, Col_name2,... Must have a primary key or a unique key.

Username is the column to update.

If you want to update multiple columns at once, you can continue to add them 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 the username and userid two fields to be updated at the same time.

Its implementation principle is that MySQL first finds the table (because it is a primary key, so it exists only in the table), based on the primary key listed after the table name. If the row data exists, the corresponding field is updated by the value given in the values list according to the last col_name = values (col_name) list. Recommendation: The list of fields after the table name, in addition to the primary key, is best listed as the updated object, that is, at the end of the statement must have the corresponding Col_name = VALUES (col_name), otherwise, you list the table name after the field, in the values are assigned, but is not the updated object, Obviously a waste.

If the row data does not exist, then an insert operation is made, and the column that is not the update object is populated by default (provided that MySQL is running in non-strict mode. If in strict mode, no column is required to have a default value, otherwise run an error).

Attention:

The primary key can act as an updated object, but only when the record does not exist in the table, that is, the insert operation is performed, and if the row data for that primary key already exists in the table, the next update does not insert the row again, but instead performs an update operation on the column other than the primary key. Therefore, it is best not to set the primary key as the updated object.

Instance:

[SQL]

INSERT into keywordtable (ID, keyword, userName, UserID) VALUES (1, ' hello ', ' Eliot ', ' + '), (2, ' hello ', ' Jhon ', 23),

(3, ' hehe ', ' Jim ', 24) On DUPLICATE KEY UPDATE keyword=values (keyword), username=values (UserName), userid=values (UserID);

In addition to the IDs, fields have keyword, userName, UserID, and they are the fields to update.