MySql quick insert and batch update

MySql quick INSERT and batch update INSERT: MySql provides the function of inserting multiple data records at a time: [SQL] insert into tbl_name (a, B, c) VALUES (1, 2, 3 ), (, 6), (, 9), (, 12 )...; in the program, you can add the list of Values through a loop and use executeUpdate to complete the insert operation. However, the Mysql statement is not as long as possible, and the MYsql statement length is limited. You can view and set the max_allowed_packet attribute in the mysql configuration file my. in. Update: Mysql does not provide multiple records updated at the same time as Insert. You Need To concatenate statements one by one. [SQL] update weibo set userName = 'xyw' where id = '22'; update weibo set userID = '000000' where id = '35'; you can use the addBatch statement, concatenates the SQL statements to perform one-time processing, but the efficiency is not high. You also need to deal with the resultset release issue, otherwise mysql will report the error "Commands out of sync; you can't run this command now" for the update statement, although no resultset is returned, but it still needs to be released. But for unknown reasons (maybe the SQL statement is too long ?), Releasing the resultset is time-consuming, and the final result is not worth the candle. To solve the preceding problems, you can use another method to Perform Batch 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) This method must meet the following conditions: col_name1, col_name2 ,... must have a primary key or a unique key. UserName is the column to be updated. To update multiple columns at a time, you can add more columns 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) so that the userName and userID fields can be updated simultaneously. The implementation principle is that Mysql first searches for a table based on the primary key listed after the table name (because it is a primary key, it exists uniquely in the table ). If this row of data exists, the corresponding fields are updated according to the value given in the values list according to the last col_name = values (col_name) list. Suggestion: in the field list after the table name, except for the primary key, the columns are best used as the updated object, that is, the corresponding col_name = values (col_name) at the end of the statement ), otherwise, fields listed after the table name are assigned values in values, but not updated objects, which is a waste of resources. If this row of data does not exist, the data is inserted. columns that are not updated are filled by default (provided that Mysql runs in non-strict mode. If no columns are in strict mode, a default value is required; otherwise, an error occurs ). Note: The primary key can be used as the updated object, but it only takes effect when the record does not exist in the table, that is, the insert operation is executed. If the table already has row data corresponding to the primary key, the row will not be inserted in the next update, but will be updated for columns 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 ', 22), (2, 'Hello ', 'jhon', 23), (3, 'giggle ', 'Jim', 24) on duplicate key update keyword = VALUES (keyword), userName = VALUES (userName ), userID = VALUES (userID); besides id, the fields include keyword, userName, and userID. They are the fields to be updated.