Detailed explanation of how to limit the update/delete range using the mysql parameter sql_safe_updates

Preface

As you all know, we have encountered many cases in MySQL operation and maintenance where data was mistakenly updated or deleted due to incorrect update/delete conditions. To avoid similar problems, you can use the sql_safe_updates parameter to restrict update/delete. When this parameter is set to on, it can prevent the entire table from being updated or deleted due to program bugs or DBA manual errors. Without further ado, let’s take a look at the detailed introduction.

There are a few things to note when setting this parameter:

a. Before setting, you need to confirm that all updates and deletes in the program comply with the restrictions of sql_safe_updates, otherwise the program will report an error.

b. 5.0 and 5.1 are both session-level, 5.6 is global&session-level; for lower-version databases, you can only set sql_safe_updates=on； for higher-version databases, you can directly set global set sql_safe_updates=on , and after the setting is completed, let the program reconnect to take effect.

Restriction Specifications:

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 conditions: without where, with where but no index, where condition is a constant

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

With where and no index: update delay_monitor set Ftime=now() where Fgtid='test'；

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

b. Execution conditions: with where and index, without where + limit, with where and no index + limit, with where and index + limit, where condition is 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；

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

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

2. delete

Compared with update, delete has stricter restrictions; if the where condition is a constant or empty, it will not be executed.

a. Error conditions: without where, with where but no index, without where + with limit, where condition is a constant, where condition is a constant + limit

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

With where and no index: delete delay_monitor set Ftime=now() where Fgtid='test'；

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

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

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

b. Execution conditions: with where and index, with where without index + limit, with where with index + limit

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；

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

The summary is as follows: key means all, const means constant

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.