MySQL的安全模式-sql_safe_updates

什么是安全模式

简而言之,为了避免犯错误，对update和delete做了些许限制，防止因程序bug或者手工误操作导致的整个表被更新或者删除的情况。(预防不了truncate table、drop table、rm...)

安全模式的开启与关闭

对齐mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

查看当前mysql的安全模式的状态，OFF表示关闭，ON表示开启。

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

开启安全模式

• 使用全局模式，但重启mysql服务后会还原为默认OFF状态

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

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

# 关闭安全模式：set global sql_safe_updates=0;	或者重启mysql服务

• 使用永久生效模式

由于该参数在my.cnf文件中添加后，会启动报错。只能走曲线救国的方式，通过启动加载脚本文件的方式来修改。

[root@db1 /root]# vi /etc/my.cnf
[mysqld]
# 指定一个自定义的脚本文件
init_file=/opt/init.sql

# 新建一个自定义脚本文件
[root@db1 /root]# vi /opt/init.sql
set global sql_safe_updates=1;

# 重启mysql服务后，安全模式依然是开启状态

SQL语句生效的条件

update、delete语句必须满足如下任一条件才能执行成功：

• 使用where子句,并且where子句中列必须为索引列
• 使用limit（含单独使用、与where或者常量一起）
• 同时使用where子句和limit(此时where子句中列可以不是索引列)

总结如下表：

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

操作演示

构造测试表和数据

CREATE TABLE `sql_safe_demo` (
	`id` BIGINT NOT NULL,
	`name`  VARCHAR(50) NOT NULL,
	`status` TINYINT NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
insert into sql_safe_demo values(1,'我是1号',0);
insert into sql_safe_demo values(2,'我是2号',0);
insert into sql_safe_demo values(3,'我是3号',0);
insert into sql_safe_demo values(4,'我是4号',1);
insert into sql_safe_demo values(5,'我是5号',1);
insert into sql_safe_demo values(6,'我是6号',1);

1. no where

-- update
mysql> update sql_safe_demo set status=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.

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

2. where nokey

-- update
mysql> update sql_safe_demo set status=1 where name = '我是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.

-- delete
mysql> delete from sql_safe_demo where name = '我是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.

3. where const

-- update
mysql> update sql_safe_demo set status=1 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.

-- delete
mysql> delete from sql_safe_demo where 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.

4. where key

-- update
mysql> update sql_safe_demo set status=1 where id=6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

-- delete
mysql> delete from sql_safe_demo where id=6;
Query OK, 1 row affected (0.00 sec)

5. limit

-- update
mysql> update sql_safe_demo set status=1 limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- delete
mysql> delete from sql_safe_demo limit 1;
Query OK, 1 row affected (0.00 sec)

6. where nokey+limit

-- update
mysql> update sql_safe_demo set status=0 where name='我是4号' limit 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- delete
mysql> delete from sql_safe_demo where name='我是4号' limit 1;
Query OK, 1 row affected (0.02 sec)

7. where key+limit

-- update
mysql> update sql_safe_demo set status=0 where id=5 limit 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- delete
mysql> delete from sql_safe_demo where id=5 limit 1;
Query OK, 1 row affected (0.01 sec)

8. where const+limit

-- update
mysql> update sql_safe_demo set status=1 where 1=1 limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- delete
mysql> delete from sql_safe_demo where 1=1 limit 1;
Query OK, 1 row affected (0.00 sec)

总结

开启安全模式后，需要对原业务进行测试。当正常功能出错时，还需按情况调整SQL或者改为业务层代码实现。