MySQL 5.7&8.0开启sql_safe_updates安全模式的差异学习心得分享

文章目录[隐藏]

总结

不知道大家是否有过维护的数据库表业务数据被人或者因为程序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默认是开启安全模式的，否则你的删除操作就会出现类型如下的报错信息：

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

在实际通过workbench连接生产环境数据库的时候，是强烈不建议关闭的，避免人为导致全表删除或全表更新的操作；

总结

如果设置了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子句中列可以不是索引列)