Mysql uses the SQL _safe_updates parameter to limit the update/delete range,

Mysql uses the SQL _safe_updates parameter to limit the update/delete range,

Preface

We should all know that we have encountered many cases in mysql O & M where data is mistakenly updated or deleted due to an update/delete condition error. To avoid similar problems, you can use the SQL _safe_updates parameter to restrict update/delete. After this parameter is set to on, you can prevent the entire table from being updated or deleted due to a program bug or DBA's Manual misoperation. I won't talk much about it below. Let's take a look at the detailed introduction.

Pay attention to the following points when setting this parameter:

A. Make sure that all updates and deletions in the program comply with the restrictions of SQL _safe_updates. Otherwise, the program reports an error.

B, 5.0, and 5.1 are session-level, and 5.6 are global and session-level. Databases of lower versions can only be set to attach when a session is created by a program.set sql_safe_updates=on；High-version databases can directlyset global set sql_safe_updates=onAfter the configuration is complete, the program will take effect after reconnection.

Restrictions:

Example Table Structure:

CREATE TABLE `delay_monitor` ( `id` int(11) NOT NULL, `Ftime` datetime DEFAULT NULL, `Fgtid` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin​

1. update

A. Error Reporting condition: Without where, without where, and where is a constant

Without where:update delay_monitor set Ftime=now()；

No index with where:update delay_monitor set Ftime=now() where Fgtid='test'；

The where condition is a constant:update delay_monitor set Ftime=now() where 1;

B. Execution condition: contains a where index, no where + limit, where index, limit, where index, limit, and where index. The where condition is a constant + limit.

With where and index:update delay_monitor set Ftime=now() where id=2；

Without where + with limit:update delay_monitor set Ftime=now()  limit 1；

With where, no index + limit:update delay_monitor set Ftime=now() where Fgtid='test' limit 1；

Index with where + limit:update delay_monitor set Ftime=now() where id =2 limit1；

The where condition is constant + limit:update delay_monitor set Ftime=now() where 1 limit 1;

2. delete

Compared with update, delelte is more restrictive. If the where condition is a constant or empty, the delelte will not be executed.

A. error condition: No where, no where + limit, where, and where are constants + limit

Without where:delete delay_monitor set Ftime=now()；

No index with where:delete delay_monitor set Ftime=now() where Fgtid='test'；

Without where + with limit:delete delay_monitor set Ftime=now()  limit 1；

The where condition is a constant:delete delay_monitor set Ftime=now() where 1;

The where condition is constant + limit:delete delay_monitor set Ftime=now() where 1 limit 1;

B. Execution condition: With the where index, where index, limit, where index, and limt

With where and index:delete delay_monitor set Ftime=now() where id=2；

With where, no index + limit:delete delay_monitor set Ftime=now() where Fgtid='test' limit 1；

Index with where + limit:delete delay_monitor set Ftime=now() where id =2 limit1；

The following table is a summary: key represents all, and const represents constants.

Operation	No where	Where key	Where nokey	Limit	Where nokey + limit	Where key + limit	Where const	Where const + limit
Delete	NO	YES	NO	NO	YES	YES	NO	NO
Update	NO	YES	NO	YES	YES	YES	NO	YES

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.