How can MySQL effectively prevent database deletion and running away?

You must have heard that some developers, due to personal mistakes, did not add a where clause when deleting or updating statements, resulting in confusion in the entire table data.

MySQL safe mode: MySQL will report an error when it finds that the delete or update statement does not have a where or limit condition. The entire SQL statement will not be executed, effectively preventing the accidental deletion of tables.

Safe Mode Settings

Check the status in mysql using the following command:

 show variables like 'sql_safe_updates';

The default state is OFF, just set the state to ON:

• set sql_safe_updates=1; //Open
• set sql_safe_updates=0; //Close

After setting to ON

• Update statement : If there is no index available for the column in the where condition and there is no limit restriction, the update will be rejected. If the where condition is a constant and there is no limit, the update will be rejected.
• Delete statement: ①The where condition is a constant, ②or the where condition is empty, ③or the column in the where condition has no index available and no limit restriction, the deletion is rejected.

test

Test in safe mode

1. Update and delete without where clause

delete from t_user

delete from t_user
> 1175 - You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
> Time: 0.001s

update t_user set name='123'

update t_user set name='123'
> 1175 - You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
> Time: 0.001s

2. Delete of non-index keys

delete from t_user where name='123'

delete from t_user where name='123'
> 1175 - You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
> Time: 0.007s

If the where condition of delete is not an index key, you must add a limit.

delete from t_user where name='123' limit 1

delete from t_user where name='123' limit 1
> Affected rows: 0
> Time: 0.002s

3. Delete index key

delete from t_user where group_id='123'

delete from t_user where group_id='123'
> Affected rows: 0
> Time: 0s

Summarize

If sql_safe_updates=1 is set, the update statement must meet one of the following conditions to be executed successfully

• Use the where clause, and the column in the where clause must be the prefix index column
• Using limit
• Use the where clause and limit at the same time (the column in the where clause does not need to be an index column)

The delete statement must meet one of the following conditions to be executed successfully

• Use the where clause, and the column in the where clause must be the prefix index column
• The execution can be successful only if the where clause and limit (the column in the where clause does not need to be an index column) are used at the same time.

This is the end of this article about how to effectively prevent MySQL from deleting databases. For more information about preventing MySQL from deleting databases, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!