MySQL update set 和 and的区别

问题描述

最近接到一个奇怪的咨询，update 语句执行没有报错，但是没有更新数据，具体有问题的语句类似于如下形式：

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

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

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

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: 0mysql> 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: 0mysql> 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>

由此可见，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: 0mysql> 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)

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

'0' and 97 = 90 and 81 = 80

解决方案

目前并不能通过 sql_mode 或者其他参数的形式来阻止这种带 and 的 update 语句，因此这一类问题的隐蔽性比较强。建议在开发的时候，利用封装好的框架，或者加强代码或者 SQL review 来避免这个问题。

PS：腾讯云数据库 MySQL 也会有类似的问题，需要警惕。

问题描述

解决方案

更多相关文章

随机推荐