一条没有条件的UPDATE的分析

上周同事有条update SQL没有加条件就执行了，在DBA大佬的及时抢救下没有酿成事故。那条SQL比较有趣，简单分析一下。

分析过程

原表的结构：

desc update_test;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| status  | int(11)          | NO   |     | NULL    |                |
| user_id | bigint(20)       | NO   |     | NULL    |                |
| rule_id | tinyint(4)       | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

表中的数据：

select * from update_test;
+----+--------+---------+---------+
| id | status | user_id | rule_id |
+----+--------+---------+---------+
|  1 |      2 |   10001 |       1 |
|  2 |      1 |   10002 |     100 |
|  3 |      3 |   10003 |     100 |
|  4 |      4 |   10004 |     100 |
|  5 |      1 |   10005 |     100 |
|  6 |      2 |   10006 |       2 |
|  7 |      3 |   10007 |     100 |
|  8 |      2 |   10008 |       1 |
|  9 |      4 |   10009 |     100 |
| 10 |      1 |   10010 |       1 |
+----+--------+---------+---------+

执行的update SQL：

update
    update_test
set
    status = 10
    and status in (2, 3)
    and rule_id != 100
    and user_id in (
        10001,
        10002,
        10003,
        10004,
        10005
);
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10  Changed: 10  Warnings: 0

更新的结果：

mysql> select * from update_test;
+----+--------+---------+---------+
| id | status | user_id | rule_id |
+----+--------+---------+---------+
|  1 |      1 |   10001 |       1 |
|  2 |      0 |   10002 |     100 |
|  3 |      0 |   10003 |     100 |
|  4 |      0 |   10004 |     100 |
|  5 |      0 |   10005 |     100 |
|  6 |      0 |   10006 |       2 |
|  7 |      0 |   10007 |     100 |
|  8 |      0 |   10008 |       1 |
|  9 |      0 |   10009 |     100 |
| 10 |      0 |   10010 |       1 |
+----+--------+---------+---------+
10 rows in set (0.01 sec)

update语句如果需要更新多个字段，被更新的值需要用逗号分隔，而不是and。从更新结果看到，status字段全表被更新为1或者0，推断MySQL解析器把 and 连接的条件做了 与或运算 从而得到了bool值(true为1， false为0)。用sqlparser进行试验，结果成立。

package main

import (
    "fmt"
    "github.com/xwb1989/sqlparser"
)

func main() {
    sql := `update update_test set status = 10 and rule_id != 100 and role_id in (2,3);`
    stmt, _ := sqlparser.Parse(sql)

    //fmt.Printf("%#v\n", stmt)
    u := stmt.(*sqlparser.Update)
    fmt.Println("field: ", u.Exprs[0].Name.Name, "\nexpr :", sqlparser.String(u.Exprs[0].Expr))
}

从结果中可以看到，status被设置为expr里面的值。

field:  status 
expr : 10 and rule_id != 100 and role_id in (2, 3)

update语句中含有in条件，猜想 in 被解析成或运算执行的，观察这条被更新为1的结果和其原来的数据可以得出结论。

select * from update_test;
+----+--------+---------+---------+
| id | status | user_id | rule_id |
+----+--------+---------+---------+
|  1 |      2 |   10001 |       1 |  --- 原数据   更新条件为(status=2 && rule_id!= 100 && user_id=10001) 此记录均满足，猜想成立
+----+--------+---------+---------+
 
 
mysql> select * from update_test;
+----+--------+---------+---------+
| id | status | user_id | rule_id |
+----+--------+---------+---------+
|  1 |      1 |   10001 |       1 |  --- update之后的数据
+----+--------+---------+---------+

有的同学可能要说了，MySQL是有sql_safe_updates 配置的，默认关闭，只要打开，那么不加条件的update语句就无法执行，就不会出现这样的问题了，一劳永逸！

show variables like "sql_safe_updates";  -- 查看变量
set sql_safe_updates = 1;                -- session级别打开

这样其实是不行的，因为业务千奇百怪，有的场景需要不带条件的update, 而且如果开了，估计有的ORM就直接用不了了吧，到时候开发就该吐槽DBA了...

这是本人的想法，笔者又去问了一位资深数据库从业人员，那位大佬说的话非常有哲理，瞬间上升了一个维度：技术是用来保障服务的，而不是限制用户的，如果出现了全表更新，用flashback修复。

总结

想用人眼兜底所有的风险终究是不靠谱的。像这种有风险的操作应该走平台，让平台承担备份和提醒的工作~