MySQL Case when Update Sequence

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.