MySQL参数之safe_updates

GrowthDBA 2021-08-19

562

“

上回书说到，UPDATE操作不加WHERE条件会导致全表数据污染，DELETE操作不加WHERE条件会把全表数据删除。但是，人非圣贤，孰能无过。所以，MySQL为我们提供了一个安全更新数据的参数——safe_updates。

”

--help中的描述

# mysql --help | grep 'safe-updates'
  -U, --safe-updates Only allow UPDATE and DELETE that uses keys.
  -U, --i-am-a-dummy Synonym for option --safe-updates, -U.
  --select-limit=# Automatic limit for SELECT when using --safe-updates.
                      --safe-updates.
safe-updates                      FALSE

从上面的输出我们可以看出以下几点：

safe_updates是MySQL客户端参数，而非MySQL服务端；
safe_updates，只允许使用索引的UPDATE和DELETE操作；
safe_updates的同义词是i-am-a-dummy；
safe_updates参数开启后，配合使用select-limit，可以限制SELECT操作返回的数据行数；
safe-updates FALSE，表示目前该参数暂未开启。

正确启用safe_updates参数

首先确认一下参数启用状态：

[root@localhost][(none)]> SHOW VARIABLES LIKE 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

参数未启用。我们现在启用：

[root@localhost][(none)]> SET sql_safe_updates=ON;
Query OK, 0 rows affected (0.00 sec)

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

[root@localhost][(none)]> SHOW VARIABLES LIKE 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.01 sec)

SET sql_safe_updates=ON;和SET sql_safe_updates=1;都为启用参数的方法，但是需要注意：这种开始方式只会对当前这个Session（即我们现在连接的这个交互页面）生效，当我们退出客户端再重新连接或有新Session连接数据库的时候，已经开启的状态会失效。所以我们在当前Session生效后，还需要指定一下全局生效：

[root@localhost][(none)]> SET GLOBAL sql_safe_updates=ON;
Query OK, 0 rows affected (0.00 sec)

这样我们退出当前客户端重新连接或新启一个新连接，这个参数就是生效状态了。

但是还有一个情况，这个参数的开启状态会失效，那就是重启MySQL实例，所以，要想保证参数永久生效，终极大招就是修改MySQL配置文件my.cnf：

vim usr/local/mysql3307/etc/my.cnf
[client]
safe-updates
...

因为我们是客户端参数，所以将safe-updates添加在[client]下，这样一来，我们的这个参数就是永久开启的状态了。

小提示

① 这里有一个问题需要注意。细心的你可能会发现，我们通过客户端交互命令开启safe_updates参数的时候，使用的命令中，参数名为：sql_safe_updates，但是在调整my.cnf配置文件时，添加的参数名为：safe-updates，如果我们将参数文件中safe-updates配置成sql_safe_updates，重启MySQL实例时会报“不能识别参数”的错误。所以，当你在开启配置该参数时，一定要特别注意。

② 还有一个点就是，所有参数值为ON或OFF的，通过SET命令开启参数时，都可以指定1或0来代替ON或OFF。

验证safe_updates参数

首先准备一张测试表，表结构和数据量如下：

[root@localhost][employees]> SHOW VARIABLES LIKE 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.00 sec)

[root@localhost][employees]> SHOW CREATE TABLE `employees_test`\G
*************************** 1. row ***************************
       Table: employees_test
Create Table: CREATE TABLE `employees_test` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test`;
+----------+
| COUNT(*) |
+----------+
| 300024   |
+----------+
1 row in set (0.09 sec)

测试1：不加WHERE条件的DELETE操作：

[root@localhost][employees]> DELETE FROM `employees_test`;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

结果：执行失败，safe_updates安全更新保护生效。

测试2：不加WHERE条件的UPDATE操作：

[root@localhost][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

结果：执行失败，safe_updates安全更新保护生效。

测试3：加WHERE条件恒真情况下的DELETE操作：

[root@localhost][employees]> DELETE FROM `employees_test` 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.

结果：执行失败，safe_updates安全更新保护生效。

测试4：加WHERE条件恒真情况下的UPDATE操作：

[root@localhost][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' 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.

结果：执行失败，safe_updates安全更新保护生效。

为了后续测试的顺利进行，我们为测试表的birth_date字段添加一个索引：

[root@localhost][employees]> ALTER TABLE `employees_test` ADD INDEX `idx_birth_date`(`birth_date`);
Query OK, 0 rows affected (1.81 sec)
Records: 0 Duplicates: 0 Warnings: 0

[root@localhost][employees]> DESC `employees_test`;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   | MUL | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

测试5：对WHERE条件后面跟有索引字段的DELETE操作：

[root@localhost][employees]> DELETE FROM `employees_test` WHERE `birth_date`='1952-02-01';
Query OK, 6 rows affected (0.00 sec)

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test`;
+----------+
| COUNT(*) |
+----------+
| 300018   |
+----------+
1 row in set (0.09 sec)

结果：执行成功。

测试6：对WHERE条件后面跟有索引字段且恒真的DELETE操作：

[root@localhost][employees]> DELETE FROM `employees_test` WHERE `birth_date`='1952-02-02' AND 1=1;
Query OK, 70 rows affected (0.01 sec)

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test`;
+----------+
| COUNT(*) |
+----------+
| 299948   |
+----------+
1 row in set (0.08 sec)

结果：执行成功。

测试7：对WHERE条件后面跟有索引字段且恒真且限制操作行数的DELETE操作：

[root@localhost][employees]> DELETE FROM `employees_test` WHERE `birth_date`='1952-02-03' AND 1=1 LIMIT 3;
Query OK, 3 rows affected (0.00 sec)

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test`;
+----------+
| COUNT(*) |
+----------+
| 299945   |
+----------+
1 row in set (0.09 sec)

结果：执行成功。

测试8：针对上面测试5、6、7WHERE条件的UPDATE操作：

[root@localhost][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' WHERE `birth_date`='1952-02-05';
Query OK, 58 rows affected (0.01 sec)
Rows matched: 58 Changed: 58 Warnings: 0

[root@localhost][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' WHERE `birth_date`='1952-02-06' AND 1=1;
Query OK, 72 rows affected (0.01 sec)
Rows matched: 72 Changed: 72 Warnings: 0

[root@localhost][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' WHERE `birth_date`='1952-02-07' AND 1=1 LIMIT 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test` WHERE `birth_date` IN ('1952-02-05','1952-02-06','1952-02-07') AND `first_name`='三' AND `last_name`='张';
+----------+
| COUNT(*) |
+----------+
| 133      |
+----------+
1 row in set (0.04 sec)

结果：执行成功。

测试9：对WHERE条件后面跟无索引字段的DELETE操作：

[root@localhost][employees]> DELETE FROM `employees_test` WHERE `hire_date`='1985-01-01';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

结果：执行失败，safe_updates安全更新保护生效。

测试10：对WHERE条件后面跟无索引字段且恒真的DELETE操作：

[root@localhost][employees]> DELETE FROM `employees_test` WHERE `hire_date`='1985-01-01' AND 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.

结果：执行失败，safe_updates安全更新保护生效。

测试11：对WHERE条件后面跟无索引字段且恒真且限制操作行数的DELETE操作：

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test`;
+----------+
| COUNT(*) |
+----------+
| 299945   |
+----------+
1 row in set (0.07 sec)

[root@localhost][employees]> DELETE FROM `employees_test` WHERE `hire_date`='1985-01-01' AND 1=1 LIMIT 3;
Query OK, 3 rows affected (0.11 sec)

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test`;
+----------+
| COUNT(*) |
+----------+
| 299942   |
+----------+
1 row in set (0.08 sec)

结果：执行成功。

测试12：针对上面测试9、10、11WHERE条件的UPDATE操作：

[root@localhost][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' WHERE `hire_date`='1985-01-01';
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][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' WHERE `hire_date`='1985-01-01' AND 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][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' WHERE `hire_date`='1985-01-01' AND 1=1 LIMIT 3;
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3 Changed: 3 Warnings: 0

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test` WHERE `hire_date`='1985-01-01' AND `first_name`='三' AND `last_name`='张';
+----------+
| COUNT(*) |
+----------+
| 3        |
+----------+
1 row in set (0.16 sec)

结果：只有加限制操作行数的UPDATE执行成功，其余条件执行失败，safe_updates安全更新保护生效。

测试13：将有索引和无索引字段都作为WHERE条件的UPDATE操作：

[root@localhost][employees]> UPDATE `employees_test` SET `first_name`='三',`last_name`='张' WHERE `birth_date`='1952-02-03' AND `hire_date`<='1987-12-07' AND 1=1 LIMIT 5;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test` WHERE `birth_date`='1952-02-03' AND `hire_date`<='1987-12-07' AND 1=1 LIMIT 5;
+----------+
| COUNT(*) |
+----------+
| 5        |
+----------+
1 row in set (0.00 sec)

结果：执行成功。

测试14：将有索引和无索引字段都作为WHERE条件的DELETE语句：

[root@localhost][employees]> DELETE FROM `employees_test` WHERE `birth_date`='1952-02-03' AND `hire_date`<='1987-12-07' AND 1=1 LIMIT 5;
Query OK, 5 rows affected (0.01 sec)

[root@localhost][employees]> SELECT COUNT(*) FROM `employees_test` WHERE `birth_date`='1952-02-03' AND `hire_date`<='1987-12-07';
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

结果：执行成功。

结论

通过查看上述对启用safe_updates参数后14种测试场景的结果，我们可以汇总整理出以下结论：

不加WHERE条件的UPDATE、DELETE语句不允许执行；
只有WHERE 1=1恒真条件的UPDATE、DELETE语句同样也不允许执行；
有索引的字段可以单独、直接作为WHERE条件进行UPDATE、DELETE，同时WHERE后面跟1=1、LIMIT，均可以正常执行；
无索引的字段不能单独作为WHERE条件进行UPDATE、DELETE，必须配合LIMIT一起使用才能完成相关操作请求；
当WHERE条件中同时存在有索引字段、无索引字段时，UPDATE、DELETE操作均可以成功执行，同时WHERE后面跟1=1、LIMIT，均可以正常执行。

还有更多的测试场景我暂时没有想到，大家感兴趣的话可以自行测试。通过本文对safe_updates的测试、使用方法的梳理和总结，希望能给大家日后工作学习带来一定帮助，谢谢大家。

end

mysql

文章转载自GrowthDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL参数之safe_updates

评论