The usage of SQL _safe_updates in MySQL in the production environment is described in detail,

The usage of SQL _safe_updates in MySQL in the production environment is described in detail,

Preface

If the application BUG or DBA misoperations occur, the entire table will be updated: update delete. MySQL provides SQL _safe_updates to limit the number of operations.

set sql_safe_updates = 1;

After setting, SQL Execution without the where condition in update delete is restricted, which is stricter. This will adversely affect the existing online environment. Strict review of new systems and applications ensures that full table update is not performed.

Create table working. test01 (id int not null AUTO_INCREMENT, name varchar (20), age INT, gmt_created DATETIME, primary key (id); insert into test01 (name, age, gmt_created) values ('xiaowang ', 2, now (); insert into test01 (name, age, gmt_created) values ('shanghai', 5, now ()); insert into test01 (name, age, gmt_created) values ('gouuu', 9, now (); insert into test01 (name, age, gmt_created) values ('heiheihei ', 12, now (); insert into test01 (name, age, gmt_created) values ('baibai', 134, now ()); # The Filter field has no index updateupdate test01 set name = 'xiaoxiao' where age = 2; ERROR 1175 (HY000 ): you are using safe update mode and you tried to update a table without a WHERE that uses a KEY column # update the entire table test01 set name = 'xiaoxiao'; ERROR 1175 (HY000 ): you are using safe update mode and you tried to update a table without a WHERE that uses a KEY column # Add limit update test01 set name = 'CIA' limit 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # create index idx_age on test01 (age ); update test01 set name = 'xiaoxiao' where age = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 update test01 set name = 'hhh' where age = 9 limit 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 alter table test01 drop index idx_age; create index idx_age_name on test01 (age, name); update test01 set age = 100 where name = 'hhh '; ERROR 1175 (HY000 ): you are using safe update mode and you tried to update a table without a WHERE that uses a KEY columnupdate test01 set age = 100 where name = 'hhh' limit 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

Therefore, when there is no where condition or the where clause is not an index field, use limit; when there is a where condition, it is an index field.

Another problem was found recently in my work. mysql SQL _safe_updates does not support updating subqueries.

Considering that developers may accidentally update data, SQL _safe_updates = 1 must be set for MySQL instances in the online database to avoid update and delete without indexes.

As a result, one day of Development found that the following SQL statement could not be correctly executed:

update t1 set col2=1 where key1 in (select col2 from t2 where key2='ABcD');

The error is as follows:

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

That is to say, you cannot update the where condition that does not go through the index. I searched and found that it is indeed not feasible. Timely key1 and key2 are t1 and t2 indexes [I cannot change to a primary key]. The update of subquery is not supported.

Google once found that people also asked this question ..

Http://stackoverflow.com/questions/24314830/query-not-getting-executed-if-supplied-a-nested-sub-query

Solution:

1) modify the session-level parameters:set sql_safe_updates=0; Execute the update operation. Exit the terminal.

2) program processing: firstselect col2 from t2 where key2='ABcD' Obtain the data, process the results cyclically, and useupdate t1 set col2=1 where key1=？. We recommend that you use a program to modify the variable temporarily.

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.