update语法错误导致数据更新异常

前几天测试发来一个脚本要更新uat环境的某几条数据来做测试用。脚本如下：update test_table set col1='xxx' and col2='xxx' where id in(xx,xx,xx,xx) ;

由于是测试环境，直接就执行了。
过了一会，测试说数据有误。仔细一看，原来是脚本写错了，导致更新了其他的数据到col1。

针对此脚本做了一下测试，过程见下。

测试环境:

MySQL: 5.7.25-log
sql_mode: 空

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+

mysql> show VARIABLES like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+

测试步骤：

mysql> CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(10) DEFAULT NULL,
  `col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `test_table`(`id`, `col1`, `col2`) VALUES (1, 'a', '红'),(2, 'b', '橙'),(3, 'c', '黄'),(4, 'd', '绿'),(5, 'e', '蓝'),(6, 'f', '靛'),(7, 'g', '紫');

mysql> select * from test_table;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | a    | 红   |
|  2 | b    | 橙   |
|  3 | c    | 黄   |
|  4 | d    | 绿   |
|  5 | e    | 蓝   |
|  6 | f    | 靛   |
|  7 | g    | 紫   |
+----+------+------+

## 执行以下update语句：
mysql> update test_table set col1='aa' and col2='bb' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+
1 row in set (0.04 sec)

mysql> update test_table set col1='bb' and col2='橙' where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bb' |
+---------+------+----------------------------------------+
1 row in set (0.04 sec)

mysql> update test_table set col1='c' and col2='黄' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'c' |
+---------+------+---------------------------------------+
1 row in set (0.04 sec)

mysql> update test_table set col1='0' and col2='绿' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_table set col1='1' and col2='蓝1' where id=5;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_table set col1='2' and col2='靛' where id=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_table set col1=2 and col2='紫' where id=7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  select * from test_table;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | 0    | 红   |
|  2 | 0    | 橙   |
|  3 | 0    | 黄   |
|  4 | 0    | 绿   |
|  5 | 0    | 蓝   |
|  6 | 1    | 靛   |
|  7 | 1    | 紫   |
+----+------+------+
7 rows in set (0.04 sec)

分析上面的执行结果可知：

1. 最终的结果只更新了字段col1；
2. 最终的结果是一个布尔类型，0或者1；(mysql中的布尔类型是tinyint(1)的同义词，0为false，非0为true)
3. 当col1='字符' 时，会有warnings；

可推测出update语句实际执行应该为：update test_table set col1= ('aa' and col2='bb') where id=1;
实际结果由 'aa' 与 col2='bb' 进行AND('与') 运算得出。
'与'运算规则：有假则假，即：'aa' 与 col2='bb' 任意一个不为true则结果就为0。

从官档得知既然非0为true，那为什么第三条更新语句结果为0？
update test_table set col1='c' and col2='黄' where id=3;
满足 'c' 非0，col2='黄'为true 两个条件。
其实mysql中视非0为true，指非0的数字，如果字符均视为false。
测试如下：

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+
1 row in set (0.04 sec)

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+
1 row in set (0.04 sec)

mysql> SELECT IF('a', 'true', 'false');
+--------------------------+
| IF('a', 'true', 'false') |
+--------------------------+
| false                    |
+--------------------------+
1 row in set (0.03 sec)

mysql> SELECT IF('1', 'true', 'false');
+--------------------------+
| IF('1', 'true', 'false') |
+--------------------------+
| true                     |
+--------------------------+
1 row in set (0.05 sec)

有了上面的结论之后可知，只有最后两个update语句满足同时为真的情形，故最终结果为1，其他均为0。

说了这么多，其实归根结底还是此条update语句语法书写错误导致的问题，正确的写法应该是：
update test_table set col1='xxx',col2='xxx' where id=xxx;
同时由于MySQL环境变量sql_mode未设置任何限制，导致只报warnings，未报Errors来终止sql的执行，更新了错误的数据到库表中。

如果把 AND 关键字换成 OR，那运算结果又不一样，'或'运算规则：有真则真。可自行测试。
生产环境执行sql时一定要仔细，做好sql审核，做好备份。