Difference between MySQL update set and and

Difference between MySQL update set and and

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

Recommend

Detailed explanation of MySQL cluster: one master and multiple slaves architecture implementation

Experimental environment: 1. Three CentOS 7 serve...

Solution to the problem that MySQL service cannot be stopped or deleted under Windows

I installed MySQL on Windows by unzipping the com...

JavaScript to achieve a simple countdown effect

This article example shares the specific code of ...

Detailed tutorial on how to monitor Nginx/Tomcat/MySQL using Zabbix

Table of contents Zabbix monitors Nginx Zabbix mo...

HTML form and the use of form internal tags

Copy code The code is as follows: <html> &l...

Let's talk in detail about how the NodeJS process exits

Table of contents Preface Active withdrawal Excep...

How to add configuration options to Discuz! Forum

Discuz! Forum has many configuration options in th...

What is ssh port forwarding? What's the use?

Table of contents Preface 1. Local port forwardin...

The difference and use of json.stringify() and json.parse()

1. Differences between JSON.stringify() and JSON....

Docker-compose quickly builds steps for Docker private warehouse

Create docker-compose.yml and fill in the followi...

HTML table tag tutorial (33): cell vertical alignment attribute VALIGN

In the vertical direction, you can set the cell a...

Detailed explanation of angular parent-child component communication

Table of contents APIs used Simple Example person...

How to deploy ElasticSearch in Docker

1. What is ElasticSearch? Elasticsearch is also d...

Unicode signature BOM (Byte Order Mark) issue for UTF-8 files

I recently encountered a strange thing when debug...

Add crontab scheduled tasks to debian docker container

Now most of the Docker images are based on Debian...