MySQL should never write update statements like this

Preface

Today I will share with you a very classic MySQL "pitfall". MySQL UPDATE statements must not be written like this!

cause

Recently, several developers have asked me on DingTalk, such as the following picture:

The problem can be summarized as follows: when updating a record in MySQL, the syntax is correct, but the record is not updated...

Conclusion: In an UPDATE statement, if you want to update multiple fields, you cannot use "AND" between the fields, but should separate them with commas.

Phenomenon

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

The problematic SQL statement:

update apps set owner_code='43212' and owner_name='李四' where 
owner_code='13245' and owner_name='张三';

The record before execution is as follows:

The record after execution is as follows:

As you can see, the result is not as the developer said, "it seems to have no effect", but it actually has an effect:

The value of owner_name did not change, but owner_code became 0!

why? Analysis

It seems that there is no problem with the syntax. I looked up the update syntax in the MySQL official documentation:

Seeing that the format of assignment_list is a comma-separated list of col_name=value, it suddenly became clear that the multi-field update statement that the developer wanted should be written like this:

update apps set owner_code='43212' , owner_name='李四' where 
owner_code='13245' and owner_name='张三'; 

Go back and try again:

Sure enough, this time I got the desired result!

Summary: In an UPDATE statement, if you want to update multiple fields, you cannot use "AND" between the fields, but should separate them with commas.

Postscript: When I had some free time later, I looked back and wondered why the strange result of owner_code=0 appeared when “AND” was used to separate them.

After many attempts, I found that:

update apps set owner_code='43212' and owner_name='李四' where 
owner_code='13245' and owner_name='张三'; 

is equivalent to:

update apps set owner_code=('43212' and owner_name='李四') where 
owner_code='13245' and owner_name='张三'; 

And ('43212' and owner_name='李四') is a logical expression, and it is not difficult to know that owner_name is not '李四'.

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

Summarize

This is the end of this article about how to never write update statements in MySQL. For more information about writing update statements in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!