The cause of

Several times recently, some developers asked me on Dingding, such as the picture below:

Update a record in MySQL, the syntax is correct, but the record is not updated…

conclusion

Summary: If you want to UPDATE multiple fields in an UPDATE statement, do not use “AND” between the fields. Instead, separate the fields with commas.

The phenomenon of

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

SQL statement with problem:

Update apps set owner_code='43212' and owner_name=' li3 'WHERE owner_code='13245' and owner_name=' li3 '; update apps set owner_code='43212' and owner_name=' li3 ';Copy the code

The record before execution looks like this:

The record after execution looks like this:

As you can see, the result is not “as if it didn’t work”, but actually it does:

The value of owner_name does not change, but owner_code becomes 0!Copy the code

why?Analysis of the

MySQL > update MySQL > update MySQL > update MySQL

The format of assignment_list is a comma-separated list of col_name=value.

Update apps set owner_code='43212', owner_name=' li3 'WHERE owner_code='13245' and owner_name=' li3 ';Copy the code

Go back and try again:

Sure enough, I got the result I wanted!

Summary: If you want to UPDATE multiple fields in an UPDATE statement, do not use “AND” between the fields. Instead, separate the fields with commas.

Owner_code =0; owner_code=0; After many attempts:

Update apps set owner_code='43212' and owner_name=' li3 'WHERE owner_code='13245' and owner_name=' li3 '; update apps set owner_code='43212' and owner_name=' li3 ';Copy the code

Is equivalent to:

Update apps set owner_code=('43212') WHERE owner_code='13245' and owner_name=' z3 '; update apps set owner_code=('43212' and owner_name=' z3 ') where owner_code='13245' and owner_name=' z3 ';Copy the code

(‘ 43212 ‘and owner_name=’ li Si ‘) is a logical expression, and it is not obvious that owner_name is not ‘Li Si’. Therefore, the result of this logical expression is false, which in MySQL is equivalent to 0!

Author: Zhu Kang links: http://tech.dianwoda.com/2017/12/14/mysql-updateyu-ju-ge-jing-dian-de-keng/ all copyright belongs to the author, reprint please indicate the source

Show Disqus Comments

Gitalking …