MySQL参数sql_safe_updates在生产环境的使用

在应用 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 条件时，为索引字段