Difference between MySQL update set and and

Problem Description
Cause Analysis
Solution

Problem Description

Recently, I received a strange inquiry. The update statement was executed without error, but the data was not updated. The specific problematic statement was similar to the following:

update test.stu set cname = '0' and math = 90 and his = 80 where id = 100;

Cause Analysis

Intuitively, the syntax of this update statement is problematic. The normal syntax for updating multiple columns of data should use commas, similar to the following form:

update test.stu set cname = '0',math = 90,his = 80 where id = 100;

The first reaction when using and directly is actually that it will report a syntax error, which does not seem to be able to execute normally. Then, based on Tencent Cloud Database MySQL, we actually construct a simple scenario and try to reproduce this problem.

The SQL statement is as follows:

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `sname` varchar(16) NOT NULL,
  `cname` varchar(8) DEFAULT NULL,
  `math` int(11) NOT NULL,
  `eng` int(11) DEFAULT NULL,
  `his` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into stu values(100,'sam','0',90,88,83);
insert into stu values(101,'jhon','1',97,82,81);
insert into stu values(102,'mary','2',87,89,92);
insert into stu values(103,'adam','2',87,89,92);

Then try the normal update statement and the update statement using and to see the actual running results:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test.stu set cname = '0' and math = 90 and his = 80 where id = 100;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> select * from stu;
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | sam | 0 | 90 | 88 | 83 |
| 101 | jhon | 1 | 97 | 82 | 81 |
| 102 | mary | 2 | 87 | 89 | 92 |
| 103 | adam | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
4 rows in set (0.00 sec)

mysql> update test.stu set cname = '0',math = 90,his = 80 where id = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from stu;
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | sam | 0 | 90 | 88 | 80 |
| 101 | jhon | 1 | 97 | 82 | 81 |
| 102 | mary | 2 | 87 | 89 | 92 |
| 103 | adam | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql>

You can see that neither statement will report an error, and the update statement with and matches the specific rows (Rows matched: 1), but does not modify the data (Changed: 0). The update statement under the standard syntax modifies the data normally.

This shows that MySQL does not consider the use of and to be grammatically incorrect, which means that MySQL "interprets" this statement in another way. The easiest thing to think of is whether MySQL interprets "and" as a logical operator instead of "and" in the English sense when setting? Moreover, the value of cname is originally 0, which is consistent with the behavior of the database when processing bool data (using 0 and 1 instead of False and True).

It is very simple to verify. Just update the data with a different cname value:

mysql> select * from stu;
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | sam | 0 | 90 | 88 | 83 |
| 101 | jhon | 1 | 97 | 82 | 81 |
| 102 | mary | 2 | 87 | 89 | 92 |
| 103 | adam | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
4 rows in set (0.00 sec)

mysql> begin;update test.stu set cname = '0' and math = 90 and his = 80 where id = 101;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from stu;
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | sam | 0 | 90 | 88 | 83 |
| 101 | jhon | 0 | 97 | 82 | 81 |
| 102 | mary | 2 | 87 | 89 | 92 |
| 103 | adam | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

From the result, we can see that MySQL changes the value of cname to 0, which means that it is indeed treated as a logical operator. If we analyze this statement carefully, we will find that MySQL processes it as follows:

set cname = ('0' and math = 90 and his = 80)

The values of math and his are determined by the rows filtered by the where condition. In the above test scenario, the following logical judgment is made:

'0' and 97 = 90 and 81 = 80

PS: Please note that even character data 0 will be treated as False.

Solution

Currently, it is not possible to prevent this type of update statement with "and" through sql_mode or other parameters, so this type of problem is relatively hidden. It is recommended to use encapsulated frameworks, or strengthen code or SQL review to avoid this problem during development.

PS: Tencent Cloud Database MySQL will also have similar problems, so be vigilant.

The above is the detailed content of the difference between MySQL update set and and. For more information about MySQL update set and and, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Practical MySQL + PostgreSQL batch insert update insertOrUpdate
Non-standard implementation code for MySQL UPDATE statement
mysql update case update field value is not fixed operation
Summary of Mysql update multi-table joint update method
Explanation of mysql transaction select for update and data consistency processing
A "classic" pitfall of MySQL UPDATE statement

<<: A small collection of html Meta tags

>>: Analyze the problem of transferring files and other parameters in the upload component of element-ui