mysql database can be opened in the security mode, but by default, the security mode is not opened. What is the security mode of mysql

Do not know whether the partners have maintained the database table business data, or because the program bug leads to the update of the entire table, and the painful experience of the whole table deletion, the recovery of business data is really a delicate activity, especially the data related to transaction and money, must be restored to the same as the original one, can it set up the last security fortress on the database level, and refuse to update the whole table? , the illegal operation of deleting the whole table. The answer is yes. In mysql, SQL [safe] updates can perfectly solve this problem. Let's show you the actual effect

SQL safe updates is not enabled by default

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

Turn on this parameter now. If you want to make it permanent, you need to add it to the database configuration file (my.cnf)

mysql> set global sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

You need to reconnect the database for it to take effect

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

Let's start to test with the meter

mysql> create table t_test10 (id char(10),name char(20), primary key(id));
Query OK, 0 rows affected (0.29 sec)

//Insert statement
insert into t_test10 values('1','test1');
insert into t_test10 values('2','test2');
insert into t_test10 values('3','test3');
insert into t_test10 values('4','test4');
insert into t_test10 values('5','test5');
insert into t_test10 values('6','test6');
insert into t_test10 values('7','test7');
insert into t_test10 values('8','test8');

mysql> select * from t_test10;
+----+-------+
| id | name  |
+----+-------+
| 1  | test1 |
| 2  | test2 |
| 3  | test3 |
| 4  | test4 |
| 5  | test5 |
| 6  | test6 |
| 7  | test7 |
| 8  | test8 |
+----+-------+
8 rows in set (0.00 sec)

Test full table deletion

mysql> delete from t_test10;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
mysql> delete from t_test10 where 1=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. 

From the above results, it is blocked by the database security policy
Test the update again

mysql> update t_test10 set name='test';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
mysql> update t_test10 set name='test' where 1=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 update without conditions is also blocked. Then test the normal update and deletion with conditions to see the effect

mysql> update t_test10 set name='test' where name='test1';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
mysql> delete from t_test10 where name='test2';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

Why does this happen? The normal update or deletion of a record will also be blocked by mysql database security policy, because the name column has no index

mysql> show create table t_test10\G;
*************************** 1. row ***************************
       Table: t_test10
Create Table: CREATE TABLE `t_test10` (
  `id` char(10) NOT NULL,
  `name` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

//Index the name column
mysql> alter table t_test10 add index idx_t_test10_name (name);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_test10\G;
*************************** 1. row ***************************
       Table: t_test10
Create Table: CREATE TABLE `t_test10` (
  `id` char(10) NOT NULL,
  `name` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t_test10_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Retest the normal delete and update statements

mysql> update t_test10 set name='test' where name='test1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t_test10 where name='test2';
Query OK, 1 row affected (0.01 sec)

All of them are successful. After SQL safe updates security mode is enabled, two conditions must be met to delete or update them normally

1. Delete and update must be filtered with conditions or limit
2. Conditions must be indexed

My favorite classmates can focus on my db_arch (Mysql database operation and architecture design).