sql_safe_updates is a system variable in MySQL that controls whether or not the MySQL server allows update or delete operations to be performed on UPDATE or DELETE statements that do not use KEY or LIMIT clauses. When this variable is set ON, MySQL rejects UPDATE or DELETE statements that may affect a large number of rows in a table unless they explicitly use a KEY (such as a primary key or unique index) in a WHERE clause or a LIMIT clause to limit the number of rows affected.
The purpose of this is to prevent the accidental loss or modification of large amounts of data due to inadvertent or incorrectly written SQL statements.
How to set sql_safe_updates
You can set sql_safe_updates in several ways:
Global level:
You can set this variable permanently by modifying the MySQL configuration file (e.g. or , depending on your operating system and MySQL version). Note, however, that setting sql_safe_updates directly in the configuration file may not be supported by all MySQL versions, or may need to be configured differently (e.g., via a plugin or other system variable).
A more common approach is to set it at runtime using MySQL's SET GLOBAL statement, but this only affects new connections. For example:
SET GLOBAL sql_safe_updates = 1;
Note, however, that setting global variables directly may require administrator privileges, and that this change will not affect already existing sessions.
Session Level:
You can set sql_safe_updates by executing the following SQL statement in your MySQL session:
SET SESSION sql_safe_updates = 1; Or log in with--safe-updates mysql -uroot -p --safe-updates
This affects subsequent operations in the current session, but does not affect other sessions or global settings.
caveat
With sql_safe_updates enabled, if you try to execute an UPDATE or DELETE statement without a KEY or LIMIT, MySQL will reject the operation and return an error.
(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.
- Not all MySQL deployments enable sql_safe_updates by default. it is usually configured by the database administrator or developer based on specific security requirements.
- In some cases, you may need to temporarily disable sql_safe_updates to perform specific bulk update or delete operations. In this case, you can set sql_safe_updates = 0 at the session level, but be careful to make sure that your SQL statements are safe and don't accidentally affect large amounts of data.
In conclusion, sql_safe_updates is a useful security feature that can help prevent data loss due to negligence or error. However, it also requires developers and database administrators to pay more attention to their SQL statements to ensure their security and accuracy.
official explanation
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.
When sql_safe_updates is set to 1.
- The update statement must meet one of the following conditions to succeed:
- The update statement uses where, and the where condition must have an indexed column;
- The update statement uses limit;
- The update statement uses both where and limit, when the where condition can be used without an indexed column;
(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)
- The following conditions must be met for the delete statement to succeed:
- The delete statement is used with indexed columns in the where condition.
- The delete statement uses both where conditions with indexed columns and limit
- The delete statement uses both where and limit, and the where condition can be used without an indexed column;
(root@localhost)[superdb]> insert into dept values(50,'sz','hk'); Query OK, 1 row affected (0.01 sec) -- simultaneous use where cap (a poem) limit，this time where Conditions can have indexed columns (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) -- using only whereThe condition is an indexed column (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) -- dnameNot an indexed column，Therefore, the deletion operation is not possible (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. -- simultaneous use where cap (a poem) limit，this time where No indexed columns in the condition (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)
If the where condition has an indexed column, but the optimizer ends up scanning the full table instead of the index, you can use force index([index_name]) to tell the optimizer which index to use, thus avoiding the pitfalls of having to lock the full table.
To this point this article on MySQL sql_safe_updates parameter is introduced to this, more related mysql sql_safe_updates parameter content please search for my previous articles or continue to browse the following related articles I hope you will support me more in the future!