MySQL sql_safe_updates参数

sql_safe_updates 是 MySQL 中的一个系统变量，用于控制 MySQL 服务器是否允许在没有使用 KEY 或 LIMIT 子句的 UPDATE 或 DELETE 语句上执行更新或删除操作。当这个变量被设置为 ON 时，MySQL 会拒绝那些可能影响到表中大量行的 UPDATE 或 DELETE 语句，除非这些语句明确使用了 WHERE 子句中的 KEY（如主键或唯一索引）或者 LIMIT 子句来限制影响的行数。

这样做的目的是为了防止由于疏忽或错误编写的 SQL 语句而导致大量数据的意外丢失或修改。

如何设置 sql_safe_updates

你可以通过几种方式设置 sql_safe_updates：

全局级别：

你可以通过修改 MySQL 的配置文件（如 my.cnf 或 my.ini，取决于你的操作系统和 MySQL 版本）来永久设置这个变量。但是，请注意，直接在配置文件中设置 sql_safe_updates 可能不被所有 MySQL 版本支持，或者可能需要以不同的方式配置（如通过插件或其他系统变量）。
一种更常见的方法是使用 MySQL 的 SET GLOBAL 语句在运行时设置它，但这只会影响新的连接。例如：

SET GLOBAL sql_safe_updates = 1;

但是，请注意，直接设置全局变量可能需要管理员权限，并且这个更改不会影响已经存在的会话。

会话级别：

你可以通过在你的 MySQL 会话中执行以下 SQL 语句来设置 sql_safe_updates：

SET SESSION sql_safe_updates = 1;

或者登录时加上--safe-updates 

mysql -uroot -p --safe-updates 

这会影响当前会话中的后续操作，但不会影响到其他会话或全局设置。

注意事项

• 在启用 sql_safe_updates 后，如果你尝试执行一个没有 KEY 或 LIMIT 的 UPDATE 或 DELETE 语句，MySQL 将拒绝该操作并返回错误。

(root@localhost)[superdb]> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept  |          0 | PRIMARY  |            1 | deptno      | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.13 sec)

(root@localhost)[superdb]> update dept set loc='sz';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

• 并非所有 MySQL 部署都会默认启用 sql_safe_updates。它通常是由数据库管理员或开发者根据特定的安全要求来配置的。

• 在某些情况下，你可能需要临时禁用 sql_safe_updates 以执行特定的批量更新或删除操作。在这种情况下，你可以在会话级别设置 sql_safe_updates = 0，但请务必小心，确保你的 SQL 语句是安全的，不会意外地影响大量数据。

总之，sql_safe_updates 是一个有用的安全特性，可以帮助防止由于疏忽或错误导致的数据丢失。然而，它也要求开发者和数据库管理员更加注意他们的 SQL 语句，以确保它们的安全性和准确性。

官方解释

If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

当 sql_safe_updates 设置为 1 时。

• update 语句必须满足如下条件之一才能执行成功：
  • update 语句使用 where，并且 where 条件中必须有索引列；
  • update 语句使用 limit；
  • update 语句同时使用 where 和 limit，此时 where 条件中可以不带有索引列；

(root@localhost)[superdb]> update dept set loc='sz' limit 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+---------+
| deptno | dname      | loc     |
+--------+------------+---------+
|     10 | ACCOUNTING | sz      |
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> update dept set loc='NEW YORK' limit 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost)[superdb]> update dept set loc='NEW YORK' where deptno=10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> update dept set loc='NEW YORK' where deptno=10 limit 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

• delete 语句必须满足以下条件能执行成功：
  • delete 语句同时使用 where 条件中带有索引列
  • delete 语句同时使用 where 条件中带有索引列 及 limit
  • delete 语句同时使用 where 和 limit，此时 where 条件中可以不带有索引列；

(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.01 sec)

-- 同时使用 where 和 limit，此时 where 条件中可以有索引列
(root@localhost)[superdb]> delete from dept where deptno=50 limit 1;
Query OK, 1 row affected (0.00 sec)

(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.00 sec)

-- 仅使用 where条件中是索引列
(root@localhost)[superdb]> delete from dept where deptno=50;
Query OK, 1 row affected (0.01 sec)

(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.00 sec)

-- dname不是索引列，因此无法删除操作
(root@localhost)[superdb]> delete from dept where dname='sz';
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 和 limit，此时 where 条件中没有索引列
(root@localhost)[superdb]> delete from dept where dname='sz' limit 1;
Query OK, 1 row affected (0.05 sec)

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept  |          0 | PRIMARY  |            1 | deptno      | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.13 sec)

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。