mysql 参数sql_safe_updates限制update/delete

鉴于以前出现的数据大表误更新和全表误删除操作。影响服务使用和数据安全。

为了防止线上业务出现以下3种情况影响线上服务的正常使用和不小心全表数据删除:
1:没有加where条件的全表更新操作 ;
2:加了where 条件字段，但是where 字段 没有走索引的表更新 ;
3:全表delete 没有加where 条件 或者where 条件没有 走索引。
以上3种情况现在都不能正常进行操作。

如果sql执行不符合要求，则会抛出异常：

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> select  host  from  user  where  user='root';

+-----------+
| host      |
+-----------+
| localhost |
+-----------+
1 row in set (0.00 sec)


mysql> update  user  set  host='%'  where  user='root';


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


错误提示：正在使用安全更新模式，尝试更新表没有使用键列的where条件；


 
原因是：mysql有个叫SQL_SAFE_UPDATES的变量，为了数据库更新操作的安全性，此值默认为1，所以才会出现更新失败的情况。


查看变量设置：


mysql> show variables like 'sql_safe%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.06 sec)




下面是SQL_SAFE_UPDATES变量为0和1时的取值说明：


 SQL_SAFE_UPDATES有两个取值0和1， 或ON 和OFF;


 


SQL_SAFE_UPDATES = 1，ON时，不带where和limit条件的update和delete操作语句是无法执行的，即使是有where和limit条件但不带key column的update和delete也不能执行。 


 


SQL_SAFE_UPDATES =0，OFF时，update和delete操作将会顺利执行。那么很显然，此变量的默认值是1。 




所以，出现1175错误的时候，可以先设置SQL_SAFE_UPDATES的值为0 OFF，然后再执行更新;










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


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




修改变量之后，再次执行sql语句：


mysql> update  user  set  host='%'  where  user='root' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


更改只在当前生效，退出mysql，再次登录后恢复为默认。




如果想要对所有会话生效的话，可以执行：
set global sql_safe_updates=off;


设定global级别的方法，global对所有会话生效，不过已经建立的会话需要重新连接才生效！






如果想要永久生效的话，需要在[mysqld]添加如下设置：

[mysqld]

init_connect='SET SQL_SAFE_UPDATES=0'

限制规范：

示例表结构：

1 2 3 4 5 6

CREATE TABLE `delay_monitor` (  `id` int(11) NOT NULL,  `Ftime` datetime DEFAULT NULL,  `Fgtid` varchar(128) NOT NULL DEFAULT '',  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin​

1、update

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

     不带where：update delay_monitor set Ftime=now()；

      带where无索引：update delay_monitor set Ftime=now() where Fgtid='test'；

      where条件为常量：update delay_monitor set Ftime=now() where 1;

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

       带where带索引：update delay_monitor set Ftime=now() where id=2；

       不带where+带limit： update delay_monitor set Ftime=now()  limit 1；

       带where无索引+limit：update delay_monitor set Ftime=now() where Fgtid='test' limit 1；

       带where有索引+limit：update delay_monitor set Ftime=now() where id =2 limit1；

        where条件为常量+limit：update delay_monitor set Ftime=now() where 1 limit 1;

 2、delete

 相对于update，delelte的限制会更为严格；where条件为常量或者为空,将不予执行。

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

      不带where：delete delay_monitor set Ftime=now()；

      带where无索引：delete delay_monitor set Ftime=now() where Fgtid='test'；

      不带where+带limit： delete delay_monitor set Ftime=now()  limit 1；

      where条件为常量：delete delay_monitor set Ftime=now() where 1;

      where条件为常量+limit：delete delay_monitor set Ftime=now() where 1 limit 1;

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

      带where带索引：delete delay_monitor set Ftime=now() where id=2；

      带where无索引+limit：delete delay_monitor set Ftime=now() where Fgtid='test' limit 1；

      带where有索引+limit：delete delay_monitor set Ftime=now() where id =2 limit1；

总结如下表：key表示所有、const表示常量

操作	no where	where key	where nokey	limit	where nokey+limit	where key+limit	where const	where const+limit
delete	NO	YES	NO	NO	YES	YES	NO	NO
update	NO	YES	NO	YES	YES	YES	NO	YES