专栏首页腾讯云数据库专家服务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 来避免这个问题。

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 案例:Limit 分页查询优化

    在各类系统的表格类信息展示的功能中,经常会用到“翻页”这个操作,在页面上每次只展示有限的数据,需要看其他数据的时候则像翻书一样翻到后面的“页”。在 MySQL ...

    王文安@DBA
  • MySQL 案例:Update 死锁详解

    锁作为 MySQL 知识体系的主要部分之一,是每个 DBA 都需要学习和掌握的知识。锁保证了数据库在并发的场景下数据的一致性,同时锁冲突也是影响数据库性能的因素...

    王文安@DBA
  • MySQL 5.7 新特性:Generated Column

    不仅是 MySQL 8.0,5.7 版本其实也推出不过少新功能,Generated Column 在 5.7 就算是一个比较实用的功能,能以巧妙的方式应对一些比...

    王文安@DBA
  • 死磕YOLO系列,不会 AI没关系,用OpenCV 调用YOLO 做目标检测

    对于视觉工程师而言,这当然是个小问题。可术业有专攻,不一定每个程序员都懂 AI 算法,那肯定就有一种方法把算法当成一个黑盒子,处理好输入输出就好了。

    Frank909
  • 专访腾讯多媒体实验室商世东:咬咬牙也要跟上新版本的发布

    刚刚毕业的时候,我在摩托罗拉做音频方面的工作,一做就是将近十年的时间。2010年,我去了杜比实验室,算起来是国内入职杜比研发团队的第二个员工。当时负责整个杜比中...

    LiveVideoStack
  • phpexcel踩坑记

    最近在使用phpexcel扩展处理表格导入的时候,遇到两个坑,现做以下记录 坑一:excel表中的富文本导入后数据始终不对 解决方法:假设$temp为循环读...

    lestat
  • YCbCr444转YCbCr422

    YCbCr颜色空间是YUV颜色空间的缩放和偏移版本。Y定义为8bit,标称颜色范围为16-235;Cb和Cr标称颜色表示范围为16-240。YCbCr的采样格式...

    FPGA开源工作室
  • csvtk:高效命令行版极简dplyr

    之前写 datamash 的使用教程 linux 极简统计分析工具 datamash 必看教程,收到了一位读者的私信,内容如上。

    生信技能树
  • Andrew Ng机器学习课程笔记(一)之线性回归

    http://www.cnblogs.com/fydeblog/p/7364598.html

    努力努力再努力F
  • 【排序】快速排序

    爱笑的架构师

扫码关注云+社区

领取腾讯云代金券