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

          在应用 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)对该参数的解释：

sql_safe_updates  YesBothYes##因为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

If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETEstatements 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()); # 过滤字段上没有索引updateupdate 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: 0update test01 set name = 'hhh' where age = 9 limit 10;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0alter 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 columnupdate 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设置的小一些，限制影响范围）