First, it is considered that multiple records are updated in the same transaction, and the execution sequence of MySQL is the same as that of BINLOG generation.
In Java code, JDBC is called to update the database in the following order,
TX. Begin
Sql1
Sql2
Sql3
TX. End
The generated BINLOG is in the execution sequence of sql1, sql2, and sql3.
However, the BINLOG generated in the case when update statement is not in the left-to-right order of the statement.
Connection con = DriverManager.getConnection(url, user, password);Statement stmt = con.createStatement();con.setAutoCommit(false);String st = "UPDATE `jw_user` SET `nick_name` = 1WHEN id = 6 THEN 1WHEN id = 5 THEN 1WHEN id = 4 THEN 1592 END";stmt.execute(st);con.commit();
In the operation result, the corresponding nick_name fields of ID and 6 are changed to 1, but the value of nick_name corresponding to ID 7 is changed from 0 to null. Why should I check it again?
BINLOG content
13600996 BEGIN13600997 /*!*/;13600998 # at 32295611413600999 # at 32295616613601000 #130626 18:40:57 server id 110 end_log_pos 322956166 Table_map: `jingwei`.`jw_user` mapped to number 652637313601001 #130626 18:40:57 server id 110 end_log_pos 322956291 Update_rows: table id 6526373 flags: STMT_END_F13601002 ### UPDATE jingwei.jw_user13601003 ### WHERE13601004 ### @1=413601005 ### @2='1592'13601006 ### SET13601007 ### @1=413601008 ### @2='1'13601009 ### UPDATE jingwei.jw_user13601010 ### WHERE13601011 ### @1=513601012 ### @2='4925'13601013 ### SET13601014 ### @1=513601015 ### @2='1'13601016 ### UPDATE jingwei.jw_user13601017 ### WHERE13601018 ### @1=613601019 ### @2='2952'13601020 ### SET13601021 ### @1=613601022 ### @2='1'13601023 ### UPDATE jingwei.jw_user13601024 ### WHERE13601025 ### @1=713601026 ### @2='0'13601027 ### SET13601028 ### @1=713601029 ### @2=NULL 13601030 # at 32295629113601031 #130626 18:40:57 server id 110 end_log_pos 322956318 Xid = 207311583813601032 COMMIT/*!*/;
It is found that the order in which MySQL internal (InnoDB) executes updates is in the ascending order of the primary key, rather than the literal order of the SQL statement. First, execute the SQL statement with the ID equal to 6 and then execute the statement with the ID equal to 5, the last execution ID is 4