MySQL中update set和and的區別有哪些

這篇文章主要介紹了MySQL中update set和and的區別有哪些，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

問題描述

最近接到一個奇怪的咨詢，update 語句執行沒有報錯，但是沒有更新數據，具體有問題的語句類似于如下形式：

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

原因分析

直觀上看，這個 update 語句的語法是有問題的，正常更新多列數據的語法應該是用逗號，類似于如下形式：

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

直接用 and 第一反應其實是會報語法錯誤，不太像是能正常執行的。那么基于騰訊云數據庫 MySQL，實際構造一個簡單的場景，嘗試復現一下這個問題。

SQL 語句如下：

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);

然后分別試一試正常的 update 語句和使用 and 的 update 語句，看一下實際的運行結果：

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>

可以看到這兩個語句確實都不會報錯，且帶 and 的 update 語句匹配到了具體的行（Rows matched: 1），但是沒有修改數據（Changed: 0），標準語法下的 update 語句倒是正常修改了數據。

由此可見，MySQL 在語法上，并不認為 and 這個用法是錯誤的，那么說明 MySQL 用另外的方式“解讀”了這個語句。最容易想到的，就是 MySQL 是不是在 set 的時候，把 and 解釋成了邏輯運算符，而不是英文意義上的“和”？而且 cname 的取值本來就是 0，也符合數據庫處理 bool 數據時的行為（用 0 和 1 代替 False 和 True）。

驗證起來很簡單，換個 cname 不為 0 的數據 update 一下就可以了：

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)

從結果來看，MySQL 修改 cname 的值為 0，說明確實是當成邏輯運算符來處理了，仔細分析這個語句，會發現 MySQL 按照如下方式來處理：

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

math 和 his 的取值是根據 where 條件篩選的行來決定的，實際對應到上面測試的場景，會變成如下的邏輯判斷：

'0' and 97 = 90 and 81 = 80

PS：需要注意，即便是字符型的數據 0，也會被當做 False。

解決方案

目前并不能通過 sql_mode 或者其他參數的形式來阻止這種帶 and 的 update 語句，因此這一類問題的隱蔽性比較強。建議在開發的時候，利用封裝好的框架，或者加強代碼或者 SQL review 來避免這個問題。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL中update set和and的區別有哪些”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!