mysql 安全模式更新_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 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或者改为业务层代码实现。