MySQL 误操作（update,delete忘加where条件）

悠悠 2024-02-18 17:16 22阅读 0赞

在应用 BUG或者 DBA误操作的情况下，会发生对全表进行更新：update delete 的情况。MySQL提供 sql\_safe\_updates 来限制次操作。 set sql\_safe\_updates = 1; 设置之后，会限制update delete 中不带 where 条件的SQL 执行，较严格。在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。

1.官方文档

首先我们来看一下官方文档(5.6)对该参数的解释：

<table style=""> 
 <tbody style="margin:0px; padding:0px; border:0px; font-size:13.3488px; vertical-align:baseline; background:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; font-size:13.3488px; vertical-align:top; background:transparent"> 
   <td style="padding:0px 5px; line-height:1.42857; vertical-align:baseline; border:0.1em outset rgb(128,128,128); margin:0px; font-size:12.6814px; background:transparent"> <a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_safe_updates" style="background:transparent; color:rgb(0,116,163); text-decoration:none; margin:0px; padding:0px; font-weight:400; outline:0px; font-size:12.6814px; vertical-align:baseline" rel="nofollow">sql_safe_updates</a></td> 
   <td style="padding:0px 5px; line-height:1.42857; vertical-align:baseline; border:0.1em outset rgb(128,128,128); margin:0px; font-size:12.6814px; background:transparent"> &nbsp;</td> 
   <td style="padding:0px 5px; line-height:1.42857; vertical-align:baseline; border:0.1em outset rgb(128,128,128); margin:0px; font-size:12.6814px; background:transparent"> &nbsp;</td> 
   <td style="padding:0px 5px; line-height:1.42857; vertical-align:baseline; border:0.1em outset rgb(128,128,128); margin:0px; font-size:12.6814px; background:transparent"> Yes</td> 
   <td style="padding:0px 5px; line-height:1.42857; vertical-align:baseline; border:0.1em outset rgb(128,128,128); margin:0px; font-size:12.6814px; background:transparent"> Both</td> 
   <td style="padding:0px 5px; line-height:1.42857; vertical-align:baseline; border:0.1em outset rgb(128,128,128); margin:0px; font-size:12.6814px; background:transparent"> Yes</td> 
  </tr> 
 </tbody> 
</table>

\#\#因为sql\_safe\_updates是SYSTEM VAR，所以我们无法直接在mysql启动命令行和控制文件中添加该参数，只能在等能mysql实例后执行 set global/session sql\_safe\_updates=1;来设置。

但是我们可以通过init\_connect参数来实现在控制文件中设置sql\_safe\_updates的目的，我们可以在控制文件中添加如下记录(注意init\_connect对于有super权限的用户是无效的)

init\_connect='SET SQL\_SAFE\_UPDATES=1'

[`sql_safe_updates`][sql_safe_updates]

If set to 1, MySQL aborts [`UPDATE`][UPDATE] or [`DELETE`][DELETE] statements that do not use a key in the `WHERE` clause or a `LIMIT` clause. (Specifically, [`UPDATE`][UPDATE] statements must have a `WHERE` clause that uses a key or a `LIMIT` clause, or both. [`DELETE`][DELETE] statements must have both.) This makes it possible to catch [`UPDATE`][UPDATE] or [`DELETE`][DELETE]statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

2. 总结  
如果设置了sql\_safe\_updates=1，那么update语句必须满足如下条件之一才能执行成功  
1)使用where子句,并且where子句中列必须为prefix索引列  
2)使用limit  
3)同时使用where子句和limit(此时where子句中列可以不是索引列)  
  
delete语句必须满足如下条件之一才能执行成功  
1)使用where子句,并且where子句中列必须为prefix索引列  
2)同时使用where子句和limit(此时where子句中列可以不是索引列)  
3. 实验

在应用 BUG或者 DBA误操作的情况下，会发生对全表进行更新：update delete 的情况。MySQL提供 sql\_safe\_updates 来限制次操作。

set sql_safe_updates = 1;

设置之后，会限制update delete 中不带 where 条件的SQL 执行，较严格。会对已有线上环境带来不利影响。对新系统、应用做严格审核，可以确保不会发生全表更新的问题。

CREATE TABLE working.test01 (id INT NOT NULL AUTO_INCREMENT,NAME VARCHAR(20),age INT,gmt_created DATETIME,PRIMARY KEY(id));
    
     insert into test01(name,age,gmt_created) values('xiaowang',2,now());
     insert into test01(name,age,gmt_created) values('huahua',5,now()); 
     insert into test01(name,age,gmt_created) values('gougou',9,now()); 
     insert into test01(name,age,gmt_created) values('heihei',12,now()); 
     insert into test01(name,age,gmt_created) values('baibai',134,now()); 
    
    # 过滤字段上没有索引
    update
    update test01 set name = 'xiaoxiao' where age = 2 ；
    ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
    # 全表更新
    update test01 set name = 'xiaoxiao';
    ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
    # 加入limit的更新
    update test01 set name = 'xia' limit 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 新增索引
    create index idx_age on test01(age);
    
    update test01 set name = 'xiaoxiao' where age = 2;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    update test01 set name = 'hhh' where age = 9 limit 10;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    alter table test01 drop index idx_age;
    create index idx_age_name on test01(age,name);
    
    
    update test01 set age= 100 where name = 'hhh';
    ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
    
    update test01 set age= 100 where name = 'hhh' limit 10;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

###### 由此，update 时，在没有 where 条件或者where 后不是索引字段时，必须使用 limit ；在有 where 条件时，为索引字段； ######

总结：

1.“攻”：如何防止这种情况发生

2.“守”：如果发生了怎么办

对于“攻”：

因为我们是MySQL，主要拿MySQL举例：

1.sql\_safe\_update：当设置为1时

对于update：如果谓词没有索引并且没有limit会被拒绝

对于delete：如果谓词永真或为空，或者谓词没有索引并且没有limit被拒绝

其实这两点对于oltp来说是很有意义的

2.在driver/proxy层面上进行过滤

可以嵌入规则引擎到driver（比如druid）或者proxy（比如cobar）中，这样更灵活，可配置.

3.超时自动kill，对于OLTP来说如果要死就早死早超生，也叫fail fast，如果当真一个大语句update/delete很久产生的后果也是很可怕的，真要是这样还不如直接kill掉（当然更优雅的是上面的方式，直接就根据规则拒绝掉）

4.流程自动/规范化

SQL上线流程，测试环境自动化规范化

对于“守”：

0.定期备份和有效性测试是必须的，这个是底线

1.可以创建延迟复制：

这个方法就很多了，pt-slave-delay/tungsten-replicator/blabla...

2.MySQL Flashback：（需要开启binlog\_format=row，其实如果开启ROW模式的话，对于OLTP的应用可以把max\_binlog\_cache\_size设置的小一些，限制影响范围）

[sql_safe_updates]: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_safe_updates
[UPDATE]: http://dev.mysql.com/doc/refman/5.6/en/update.html
[DELETE]: http://dev.mysql.com/doc/refman/5.6/en/delete.html