Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL parameter sql_safe_updates in the production environment how to use, has a certain reference value, interested friends can refer to, I hope you read this article after a great harvest, the following let Xiaobian take you to understand.

preface

In the case of BUG application or DBA error operation, an update to the whole table: update delete will occur. MySQL provides sql_safe_updates to limit secondary operations.

set sql_safe_updates = 1;

After setting, SQL execution without where condition in update delete will be restricted, which is stricter. It will adversely affect the existing online environment. Strict auditing of new systems and applications ensures that full table updates do not occur.

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 ('huahua',5,now()); insert into test01 (name,age,gmt_created) values ('gougou',9,now()); insert into test01 (name,age,gmt_created) values ('heihei',12,now()); insert into test01 (name,age,gmt_created) values ('baibai', 134,now()); #No index on filter field updateupdate test01 set name = ' xiao', 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#FULL TABLE UPDATE test01 set name ='xiao '; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column#added to limit's update test01 set name ='xia ' limit 1; Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0#newaddingindexcreate 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: 0update test01 set name = 'hhh' where age = 9 limit 10;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0alter 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 updating, limit must be used when there is no where condition or after where is not an index field; when there is a where condition, it is an index field.

Another problem recently discovered at work is that mysql sql_safe_updates does not support updating subqueries.

Considering that developers sometimes accidentally update data by mistake, it is required that MySQL instances of online libraries set sql_safe_updates=1 to avoid update and delete without indexes.

One day, developers discovered that one of the following SQL couldn't be executed correctly:

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

This means that there is no way to update where conditions that do not go to the index. After searching for it, he found that it really didn't work. Key1 and key2 are the indexes of t1 and t2 respectively. Description is an update that does not support subqueries.

Googled it and found that people have asked this question before.

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

Final Solution:

1) Modify session level parameters: set sql_safe_updates=0; perform update operation. Exit terminal.

2) Program processing: first select col2 from t2 where key2='ABcD' to obtain data, then loop through the results and use update t1 to set col2=1 where key1=? to batch updates. It is recommended to use procedures to deal with temporary modification of variables is not a long-term solution.

Thank you for reading this article carefully. I hope that Xiaobian's "MySQL parameter sql_safe_updates how to use it in the production environment" will help everyone. At the same time, I hope that everyone will support it a lot. Pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.