MySQL UPDATE statement differs from standard SQL

"There is a concept called simultaneous execution in the SQL standard ."

Simultaneous execution refers to the timing of the execution of the various parts of the same clause as distinct, such as the following SQL statement

Select ABS(-1),ABS(2); +---------+--------+| ABS(-1)| ABS(2)|+---------+--------+|       1 |      2 |+---------+--------+1Rowinch Set(0.01Sec

According to SQL Standard abs ( -1) and abs (2) These two functions are " simultaneous execution "!

"mysql update with SQL standard back "

　　1): in order to illustrate the problem I define the following table structure, table t contains two data columns ' x ', ' Y '

Create Table int  not NULL Primary Key int int);                   

　　

　　2): Add a row of data to table T

Insert  intoT (x, y)Values(1,1);Select *  fromT;+----+------+------+|Id|X|Y|+----+------+------+|  1 |    1 |    1 |+----+------+------+1Rowinch Set(0.00Sec

　　

　　3): execute an UPDATE statement

UpdateTSetX=X+1Y=x;Select *  fromT; +----+------+------+|Id|X|Y|+----+------+------+|  1 |    2 |    2 |+----+------+------+1Rowinch Set(0.00Sec

From the above results, it can be seen that the UPDATE statement is not executed concurrently, if the execution of the theory y=x this statement execution, the value of x will be the initial value "1" instead of the value of the self-increment "2";

If you feel that the result is not a problem for you, let's look at the next UPDATE statement, I just swap the two parts of "x=x+1" and "y=x".

　　4): Execute the adjusted SQL statement

UpdateTSetY=X, X=X+1; MySQL> Select *  fromT;+----+------+------+|Id|X|Y|+----+------+------+|  1 |    3 |    2 |+----+------+------+1Rowinch Set(0.00Sec

You can see that the update in the MySQL database is not executed at the same time, it is in order, and this sequence directly affects the results of your SQL execution.

" Summary "

When you write a program, realize that MySQL is dealing with the specifics of the update statement, and that the assignment statement that precedes it will have an effect on the statements in the background;

That's because MySQL explicitly mentions this in its official documentation, in the age of the bug, and we can only say that this is a workaround to the SQL standard.

Official Document: Https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-update.html

-----------------------------http://www.sqlpy.com-------------------------------------------------

-----------------------------http://www.sqlpy.com-------------------------------------------------

MySQL UPDATE statement differs from standard SQL