问题简介
update taba set col1=123 and col2='abc' and col3='cde' where id=1;
语法错误，但是SQL不报错，只更新了col1字段。
环境说执行明
v8.5.4 单节点混合部署
问题详细说明
日常DML审核只会比对要更新的数据量和实际的数据量是否相同，本次update语法有错误，但是执行后，研发验证发现col1字段被更新为0，SQL没有报错。（针对MySQL系列，该语法是正确的，但是属于比较危险的用法）。
作为一个从事Oracle较多的DBA来说，感觉很炸裂。
解决方案
更正语法，update taba set col1=123,col2='abc',col3='cde' where id=1;
当更改的字段是int类型，该语法默认更新的是col1字段，后面的被当做boolean类型做判断，true更新为1，false更新为0.update taba set col1=(123 and col2='abc' and col3='cde')，后面被视为一个整体。
验证如下：
MySQL [test]> create table testa(id int,name varchar(10),col1 varchar(10),col2 varchar(10),col3 varchar(10));
Query OK, 0 rows affected (0.025 sec)
MySQL [test]> insert into testa values(1,'li','l1','l2','l3');
Query OK, 1 row affected (0.002 sec)
MySQL [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MySQL [test]> select * from testa;
+------+------+------+------+------+
| id | name | col1 | col2 | col3 |
+------+------+------+------+------+
| 1 | li | l1 | l2 | l3 |
+------+------+------+------+------+
1 row in set (0.001 sec)
MySQL [test]> update testa set name='abc' and col1='l11' and col2='l12' and col3='l13' where id=1;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'abc'
MySQL [test]> select * from testa;
+------+------+------+------+------+
| id | name | col1 | col2 | col3 |
+------+------+------+------+------+
| 1 | li | l1 | l2 | l3 |
+------+------+------+------+------+
1 row in set (0.001 sec)
MySQL [test]> alter table testa modify name varchar(100);
Query OK, 0 rows affected (0.029 sec)
MySQL [test]> update testa set name='abc' and col1='l11' and col2='l12' and col3='l13' where id=1;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'abc'
MySQL [test]> select * from testa;
+------+------+------+------+------+
| id | name | col1 | col2 | col3 |
+------+------+------+------+------+
| 1 | li | l1 | l2 | l3 |
+------+------+------+------+------+
1 row in set (0.001 sec)
MySQL [test]> alter table testa add supid bigint;
Query OK, 0 rows affected (0.055 sec)
MySQL [test]> update testa set supid=123456 where id=1;
Query OK, 1 row affected (0.006 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MySQL [test]> select * from testa;
+------+------+------+------+------+--------+
| id | name | col1 | col2 | col3 | supid |
+------+------+------+------+------+--------+
| 1 | li | l1 | l2 | l3 | 123456 |
+------+------+------+------+------+--------+
1 row in set (0.001 sec)
布尔为假。更新为0.
MySQL [test]> update testa set supid=45678 and name='abc' and col1='l11' and col2='l12' and col3='l13' where id=1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]> select * from testa;
+------+------+------+------+------+-------+
| id | name | col1 | col2 | col3 | supid |
+------+------+------+------+------+-------+
| 1 | li | l1 | l2 | l3 | 0 |
+------+------+------+------+------+-------+
1 row in set (0.001 sec)
布尔为真，更新为1.
MySQL [test]> update testa set supid=45678 and name='li' and col1='l1' and col2='l2' and col3='l3' where id=1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]> select * from testa;
+------+------+------+------+------+-------+
| id | name | col1 | col2 | col3 | supid |
+------+------+------+------+------+-------+
| 1 | li | l1 | l2 | l3 | 1 |
+------+------+------+------+------+-------+
1 row in set (0.001 sec)
拓展
Oracle 场景下验证
该类语法直接报错，不会执行：
SQL> desc testa;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID INTEGER Y
NAME VARCHAR2(10) Y
COL1 VARCHAR2(10) Y
COL2 VARCHAR2(10) Y
COL3 VARCHAR2(10) Y
SQL> insert into testa values(1,'li','l1','l2','l3');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from testa;
ID NAME COL1 COL2 COL3
--------------------------------------- ---------- ---------- ---------- ----------
1 li l1 l2 l3
SQL> update testa set name='abc' and col1='l11' and col2='l12' and col3='l13' where id=1;
update testa set name='abc' and col1='l11' and col2='l12' and col3='l13' where id=1
ORA-00933: SQL 命令未正确结束
MySQL环境验证
与TiDB一致。应该是MySQL兼容的都是一致的实现方式。
增加SQL审核语法审计还是很有必要的。