当前位置: 首页 > 工具软件 > updates > 使用案例 >

MySQL安全更新之safe_updates介绍说明

鲍建业
2023-12-01

对于研发而言,线上数据库update和delete是非常频繁的,因而经常出现update和delete时,后面忘记添加where条件的限制,导致整个表误删除,或者是误更新,造成很多灾难是非常常见的事情,如何彻底解决这样的人为的数据库故障或者减少这种人为的故障了,那就是从根本上控制好mysql数据库的权限,禁止给研发人员update和delete数据库的权限,严格SQL语句上线之前的审核,减少人为的操作mysql库的故障。然而这种数据库的权限的把控往往是很难严格意义上把控好的。只能是减少类似这样的故障的发生。
下面咱们简单介绍下client端mysql的safe_updates 参数的使用,它从一定程度上还是可以避免上面的update和delete时,后面忘记添加where条件而带来的悲剧。

sql_safe_updates具体的使用方法咱们可以man 下mysql

man mysql|grep sql_safe_updates
           SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

同时也可以把参数写入到/etc/my.cnf配置文件
在my.cnf配置文件开启参数safe-updates就行。

[root@app1 logs]# grep  -B 5 safe-updates /etc/my.cnf
[mysql]
auto-rehash
prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
safe-updates

查看当前mysql session会话下的sql_safe_updates

root@localhost [(none)]>select @@sql_safe_updates;
+--------------------+
| @@sql_safe_updates |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

查看全局的配置参数sql_safe_updates

root@localhost [(none)]>select @@global.sql_safe_updates;
+---------------------------+
| @@global.sql_safe_updates |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

创建测试表并且插入数据测试

CREATE TABLE `zx_scores` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `titles` char(15) NOT NULL,
  `icon` smallint(6) unsigned DEFAULT '0',
  `integral` int(10) NOT NULL DEFAULT '0',
  `isdefault` tinyint(1) unsigned NOT NULL DEFAULT '0',
`create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`day` date NOT NULL DEFAULT '0000-00-00' COMMENT '日期',
  PRIMARY KEY (`id`),
  KEY `integral` (`integral`)
) ENGINE=Innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

给表zx_sorces中的字段titles字段添加一个索引A


root@localhost [test02]>create index A on zx_scores(titles);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看该表存在的索引

root@localhost [test02]>show index from zx_scores;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| zx_scores |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| zx_scores |          1 | integral |            1 | integral    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| zx_scores |          1 | A        |            1 | titles      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

通过查看表结构也是可以看到索引的

root@localhost [test02]>desc zx_scores;
+-------------+----------------------+------+-----+------------+----------------+
| Field       | Type                 | Null | Key | Default    | Extra          |
+-------------+----------------------+------+-----+------------+----------------+
| id          | int(11) unsigned     | NO   | PRI | NULL       | auto_increment |
| titles      | char(15)             | NO   | MUL | NULL       |                |
| icon        | smallint(6) unsigned | YES  |     | 0          |                |
| integral    | int(10)              | NO   | MUL | 0          |                |
| isdefault   | tinyint(1) unsigned  | NO   |     | 0          |                |
| create_time | varchar(20)          | NO   |     | NULL       |                |
| day         | date                 | NO   |     | 0000-00-00 |                |
+-------------+----------------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)

查看当前sql的模式,0代表sql没有开启安全模式,1代表sql开启了安全模式


root@localhost [test02]>select @@SQL_SAFE_UPDATES;
+--------------------+
| @@SQL_SAFE_UPDATES |
+--------------------+
|                 0  |
+--------------------+
1 row in set (0.00 sec)

设置sql为安全模式:


root@localhost [test02]>set global sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

退出当前的MySQL的session会话层,重新登录mysql查看当前的sql_safe_updates为1,代表开启了sql的安全模式

root@localhost [test02]>select @@SQL_SAFE_UPDATES;
+--------------------+
| @@SQL_SAFE_UPDATES |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

给表中插入测试数据:

insert into zx_scores values(1,'列兵',1,0,1,now(),curdate());
insert into zx_scores values(2,'班长',2,1000,1,now(),curdate());
insert into zx_scores values(3,'少尉',3,2000,1,now(),curdate());
insert into zx_scores values(4,'中尉',4,3000,1,now(),curdate());
insert into zx_scores values(5,'上尉',5,4000,1,now(),curdate());
insert into zx_scores values(6,'少校',6,5000,1,now(),curdate());
insert into zx_scores values(7,'中校',7,6000,1,now(),curdate());
insert into zx_scores values(8,'上校',8,7000,1,now(),curdate());
insert into zx_scores values(9,'少将',9,12000,1,now(),curdate());
insert into zx_scores values(10,'中将',10,17000,1,now(),curdate());
insert into zx_scores values(11,'上将',11,22000,1,now(),curdate());
insert into zx_scores values(12,'大将',12,27000,1,now(),curdate());

演示一:

root@localhost [test02]>update zx_scores set titles='班长' where icon=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. 
root@localhost [test02]>

简单翻译一下:你正在使用 安全更新模式(safe upate mode)并且你在尝试 update 一个表时 没有用带有键的列 作为where条件。

初学者在修改一个值时可能会用以下语句:

update table_name set column='new_value'
而这正是很多人常犯的错误。因为他没有限定条件,会将表中所有的记录都修改一遍。
为了防止这种错误出现,我们可以开启安全更新模式(safe update mode):
set [global] SQL_SAFE_UPDATES = 1;

在update操作中:

1.当update table 时,后面没有where条件,是会拒绝更新的。

root@localhost [test02]>update zx_scores set titles='班长' ;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
root@localhost [test02]>

2.当where条件中列(column)没有索引可用且无limit限制时会拒绝更新。

root@localhost [test02]>update zx_scores set titles='班长' where icon=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. 
root@localhost [test02]>

一旦加上limit限制是可以更新的:

root@localhost [test02]>update zx_scores set titles='班长' where icon=1 limit 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.当where条件为常量且无limit限制时会拒绝更新。

root@localhost [test02]>update zx_scores set integral='1000' 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. 
root@localhost [test02]>

例如:update zx_scores set titles='班长' where icon=1;是会拒绝跟新的

一旦加上limit限制是可以更新的:


root@localhost [test02]>update zx_scores set integral='1000' where icon=1 limit 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

当where条件为常量且有limit限制时是可以更新的:

root@localhost [test02]>update zx_scores set titles='少将' where 1=1 limit 2;
Query OK, 1 row affected (0.10 sec)
Rows matched: 2  Changed: 1  Warnings: 0

在delete操作中:

1.当①where条件为常量,且无limit限制时拒绝删除。

root@localhost [test02]>delete from zx_scores 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. 
root@localhost [test02]>

当①where条件为常量,且有limit限制是可以删除的。

root@localhost [test02]>delete from zx_scores where 1=1 limit 5;
Query OK, 5 rows affected (0.06 sec)

2.当②或where条件为空,且无limit限制时拒绝删除

root@localhost [test02]>delete from zx_scores;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
root@localhost [test02]>

3.当③或where条件中 列(column)没有索引可用且无limit限制时拒绝删除。

root@localhost [test02]>delete from zx_scores where icon =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. 
root@localhost [test02]>

但是titles字段上创建了索引A,所以可以删除

root@localhost [test02]>delete from zx_scores where titles='班长';
Query OK, 2 rows affected (0.03 sec)
root@localhost [test02]>

需要注意的是:

update操作中,where可以为常量 ,where条件中列(column)可以没有索引,但是需要有limit限制。
然而delete要严格一些:where不能为常量,且where条件中列(column)不能没有索引!

 类似资料: