MySQL Case when Update Sequence

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