什么是安全模式
简而言之,为了避免犯错误,对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 wherewhere nokeywhere constwhere keylimitwhere nokey+limitwhere key+limitwhere const+limitupdateNONONOYESYESYESYESYES
deleteNONONOYESYESYESYESYES
操作演示
构造测试表和数据
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);
复制代码
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.
复制代码
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.
复制代码
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.
复制代码
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)
复制代码
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)
复制代码
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)
复制代码
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)
复制代码
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或者改为业务层代码实现。