• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL 5.7&8.0开启sql_safe_updates安全模式的差异

MySQL SEian.G 1个月前 (06-28) 58次浏览 已收录 0个评论
文章目录[隐藏]

不知道大家是否有过维护的数据库表业务数据被人或者因为程序bug导致全表更新,全表删除的痛苦经历,恢复业务数据的过程真的太痛苦了,尤其与交易和钱相关的数据,必须恢复成和原来一模一样,那能不能在数据库层面架起最后一道安全堡垒,拒绝全表更新,全表删除的非法操作呢,答案是有的,在mysql中sql_safe_updates可以完美解决这个问题;

MySQL数据库是可以开启安全模式,不过默认情况下,安全模式不开启的,下面就来说说什么是mysql的安全模式,如下是sql_safe_updates参数的官方解释:

If this variable is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. This makes it possible to catch UPDATE and DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is OFF.

sql_safe_updates默认是不开启的

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | OFF |
+------------------+-------+
1 row in set (0.01 sec)

现在就开启这个参数,如果要永久生效,需要将参数添加到数据库配置文件(my.cnf)中

mysql> set global sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

注意:需要重新连接一下数据库,才会生效;

下面就来给大家演示一下实际效果,以及在MySQL 5.7以及8.0版本在开启该参数之后进行更新和删除操作的差异;

mysql> CREATE TABLE `t_test1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入语句
insert into t_test1 values(NULL,'test1',21);
insert into t_test1 values(NULL,'test2',22);
insert into t_test1 values(NULL,'test3',23);
insert into t_test1 values(NULL,'test4',24);
insert into t_test1 values(NULL,'test5',25);
insert into t_test1 values(NULL,'test6',26);
insert into t_test1 values(NULL,'test7',27);
insert into t_test1 values(NULL,'test8',28);

测试一下全表删除

mysql> delete from t_test1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

mysql> delete from t_test1 where 1=1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

从上面的结果看,全部被数据库安全策略拦截了 再来测试一下更新

mysql> update t_test1 set name='test';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

mysql> update t_test1 set name='test' where 1=1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

不带条件的更新也被拦截,那测试一下正常带条件的更新和删除看看效果

mysql> update t_test1 set name='test' where name='test1';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

mysql> delete from t_test1 where name='test2';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

为什么会这样呢,正常更新或者删除一条记录也会被mysql数据库安全策略拦截了呢?

下面针对update操作以及delete操作不同场景下的报错以及执行条件测试:

1、update操作

a、报错条件:不带where、带where无索引、where条件为常量

(1)不带where:update t_test1 set name=’test’;

(2)带where无索引:update t_test1 set name=’test’ where name=’test1′;

(3)where条件为常量:update t_test1 set name=’test’ where 1=1;

b、执行条件:带where带索引、不带where+带limit、带where无索引+limit、带where有索引+limit、where条件为常量+limit

(1)带where带索引:update t_test1 set name=’test’ where age=21;

(2)不带where+带limit: update t_test1 set name=’test’ limit 1;

(3)带where无索引+limit:update t_test1 set name=’test’ where name=’test1′ limit 1;

(4)带where有索引+limit:update t_test1 set name=’test’ where age=21 limit 1;

(5)where条件为常量+limit:update t_test1 set name=’test’ where 1=1 limit 1;

2、delete操作

相对于update,delelte的限制会更为严格;并且在5.7和8.0版本存在一点差异。

a、报错条件:不带where、带where无索引、不带where+带limit、where条件为常量、where条件为常量+limit

不带where:delete from t_test1;

带where无索引:delete from t_test1 where name=’test’;

where条件为常量:delete from t_test1 where 1=1;

b、执行条件:带where带索引、带where无索引+limit、带where有索引+limt

带where带索引:delete from t_test1 where age=22;

不带where+带limit:delete from t_test1 limit 1; (5.7版本执行会报错,8.0版本是可以执行成功的)

带where无索引+limit:delete from t_test1 where name=’test’ limit 1;

带where有索引+limit:delete from t_test1 where age=22 limit 1;

where条件为常量+limit:delete from t_test1 where 1=1 limit 1;(5.7版本执行会报错,8.0版本是可以执行成功的)

大家重点关注上面粉色的条件(delete from t_test1 limit 1)和(delete from t_test1 where 1=1 limit 1;)这两种在5.7和8.0的执行情况是不一样的,在5.7版本,满足报错条件,会执行失败;而在8.0版本,满足执行调整,可以执行成功;


不知大家在使用MySQL Workbench操作数据库的时候是否注意到,Workbench默认是开启安全模式的,否则你的删除操作就会出现类型如下的报错信息:
MySQL 5.7&8.0开启sql_safe_updates安全模式的差异

从具体的报错信息中,也是可以看到原因以及解决方案:Edit–>Perferences–>SQL Editor

在实际通过workbench连接生产环境数据库的时候,是强烈不建议关闭的,避免人为导致全表删除或全表更新的操作;
MySQL 5.7&8.0开启sql_safe_updates安全模式的差异

总结

如果设置了sql_safe_updates=1,

update语句必须满足如下条件之一才能执行成功

1)使用where子句,并且where子句中列必须为索引列

2)使用limit

3)同时使用where子句和limit(此时where子句中列可以不是索引列)

delete语句,5.7和8.0版本有些差异,必须满足如下条件之一才能执行成功

5.7版本:

1)使用where子句,并且where子句中列必须为索引列

2)同时使用where子句和limit(此时where子句中列可以不是索引列)

8.0版本:

1)使用where子句,并且where子句中列必须为索引列

2)使用limit

3)同时使用where子句和limit(此时where子句中列可以不是索引列)

MySQL 5.7&8.0开启sql_safe_updates安全模式的差异


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL 5.7&8.0开启sql_safe_updates安全模式的差异
喜欢 (1)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址