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

Nginx Layer 4 Load Balancing Configuration Guide

1. Introduction to Layer 4 Load Balancing What is...

Steps to build a Docker image using Dockerfile

Dockerfile is a text file that contains instructi...

New ways to play with CSS fonts: implementation of colored fonts

What if you designers want to use the font below ...

How to deploy DoNetCore to Alibaba Cloud with Nginx

Basic environment configuration Please purchase t...

Several ways to set the expiration time of localStorage

Table of contents Problem Description 1. Basic so...

Practical record of solving MySQL deep paging problem

Table of contents Preface Why does limit deep pag...

Excel export always fails in docker environment

Excel export always fails in the docker environme...

How to install Jenkins on CentOS 8

To install Jenkins on CentOS 8, you need to use t...

border-radius method to add rounded borders to elements

border-radius:10px; /* All corners are rounded wi...

Vue two same-level components to achieve value transfer

Vue components are connected, so it is inevitable...

js array entries() Get iteration method

Table of contents 1. Detailed syntax of entires()...

Using vue3 to implement counting function component encapsulation example

Table of contents Preface 1. The significance of ...

Native js to realize the upload picture control

This article example shares the specific code of ...

Detailed explanation of the underlying implementation method of Nginx polling algorithm

Introduction to the polling algorithm Many people...

Detailed analysis of SQL execution steps

Detailed analysis of SQL execution steps Let'...