What is safe mode

In mysql, if the where condition is not added to update and delete, all data will be modified. It's not only the developers who are new to MySQL that will encounter this problem, but also the engineers who have some experience will inevitably forget to write where conditions. In order to avoid all data modification and deletion caused by errors, MySQL security mode can be opened.

Safety mode on and off

After connecting to the database, check the status of the current mysql security mode

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.00 sec)

The above query command instance indicates that mysql is currently open in safe mode.
Set SQL < safe > updates = 1; / / security mode is on
Set SQL < safe > updates = 0; / / security mode is off

In the update operation: when there is no index available for the column in the where condition and there is no limit limit, the update will be rejected. When the where condition is constant and there is no limit limit, updates are rejected.

In the delete operation: when ① where condition is constant, ② or where condition is empty, ③ or where condition has no index available and no limit, the deletion is rejected.

Security mode UPDATE operation instance

1. update without where condition

mysql> update users 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

Operation failed, prompt where condition is needed as primary key.

2. update without where condition but with limit

mysql> update users set status=1 limit 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The operation is successful. Although there is no where condition, the limit limit is added.

3. Update using non indexed fields as criteria

mysql> update users set status=1 where reg_time>'2018-01-01 00:00:00';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

The operation failed because the condition field is not an index field and the limit limit was not added.

4. Use non index field as condition and add limit to update

mysql> update users set status=1 where reg_time>'2018-01-01 00:00:00' limit 10;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 10  Changed: 0  Warnings: 0

The operation is successful. Although the condition field is not an index field, the limit limit is added, so the operation is successful.

5. Use index field as condition and do not add limit to update

mysql> update users set status=1 where phone_num='13800138000';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The operation is successful. Although the limit limit is not added, the condition field is an index field, so the operation is successful.

Security mode DELETE operation instance

1. delete without where condition

mysql> delete from users;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

The operation failed. There is no where condition, not directly.

2. Conditional deletion without index key

mysql> delete from users where reg_time='2018-06-28 17:35:37';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

The operation failed because the reg? Time field is not an index key.

3. Non index key as condition and add limit to delete

mysql> delete from users where reg_time='2018-06-28 17:35:37' limit 1;
Query OK, 1 row affected (0.00 sec)

The operation is successful, even if it is not an index key, because limit is added.

4. Use index keys and delete without limit.

mysql> delete from users where user_id=100000;
Query OK, 1 row affected (0.01 sec)

The operation succeeded because user? ID is the index key.

5. Add limit but delete without using where condition.

mysql> delete from users limit 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

The operation failed because the where retrieval condition was not added.

summary

If you set SQL [safe] updates = 1, the update statement must meet one of the following conditions to execute successfully

1) Use the where clause and the column in the where clause must be a prefix index column
2) Use limit
3) Use both where clause and limit (in this case, the column in where clause can not be index column)

The delete statement must meet one of the following conditions to execute successfully
1) Use the where clause and the column in the where clause must be a prefix index column
2) Use both where clause and limit (in this case, the column in where clause can not be index column)