An update statement from a colleague made me miserable enough

Time:2021-4-14

An update statement from a colleague made me miserable enough
Source: ju.outofmemory.cn/entry/336774

Recently, several development students have asked me on the nail, such as the following figure:

An update statement from a colleague made me miserable enough

The problem can be summed up as follows:Update a record in MySQL. The syntax is correct, but the record has not been updated

When I first encountered this problem, I got this statement and executed it directly in the test library. I found that there was a problem, but it was still different from the development description. Here I use the test data to simulate the following:

Problematic SQL statement:

An update statement from a colleague made me miserable enough

The record before execution is as follows:

An update statement from a colleague made me miserable enough

The record after execution is as follows:

An update statement from a colleague made me miserable enough

It can be seen that the result is not like “it seems to have no effect” as the developer said. In fact, it is effective

An update statement from a colleague made me miserable enough

why?

It seems that there is no problem with the syntax at all. I looked at the update syntax in the official MySQL document

An update statement from a colleague made me miserable enough

See assignment_ The format of a list is a comma separated col_ Name = value list, all of a sudden, the development of students want to update the multi field statement should be written as follows:

Go back and try again

An update statement from a colleague made me miserable enough

Sure enough, this got the desired results!

Summary:In an update statement, if you want to update multiple fields, you can’t use “and” between the fields, but separate them with commas.

Postscript:Later, when I have time, I look back to see why the owner appears when I use “and” to separate_ Strange result of code = 0? After many attempts, we found that:

An update statement from a colleague made me miserable enough

Equivalent to:

An update statement from a colleague made me miserable enough

And (‘43212 ‘and owner_ Name =’li Si ‘) is a logical expression, and it is not difficult to know the owner here_ Name is not Li Si.

Therefore, the result of this logical expression is false, which is equivalent to 0 in MySQL!