mysql sqlsafeupdates_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 条件时，为索引字段

注意：本文来自云栖团队博客。本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证，请您自行验证核实并承担相关的风险与后果！

CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场，本站不承担此类作品侵权行为的直接责任及连带责任。您有版权、意见、投诉等问题，请通过[eMail]联系我们处理，如需商业授权请联系原作者/原网站。