Shulou(Shulou.com)06/03 Report--

This article mainly explains the "mysql effectively prevent deletion of the library run method tutorial", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and study the "mysql effectively prevent deletion run method tutorial" it!

Catalogue

Safe Mode Settin

test

1. Update and delete without where

2. Delete of non-index key

3. Delete of the index key

Summary

You must have heard that some developers do not add where statements to delete or update statements due to personal errors, resulting in data confusion in the whole table.

Mysql security mode: mysql will report an error when it finds that delete or update statements do not add where or limit conditions. The entire sql will not be executed, effectively preventing the misdeletion of the table.

Safe Mode Settin

View the status in mysql with the following command:

Show variables like 'sql_safe_updates'

The default is OFF, which can be set to ON:

Set sql_safe_updates=1; / / Open

Set sql_safe_updates=0; / / close

After setting it to ON

Update statement: the update is rejected when the column (column) in the where condition has no index available and no limit restrictions. Updates are rejected when the where condition is constant and there is no limit limit.

Delete statement: delete is rejected when the ① where condition is constant, the ② or where condition is empty, and the column (column) in the ③ or where condition has no index available and no limit restriction.

test

Open safe mode for testing

1. Update and delete without where

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.001s2, delete of non-index key

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.002s3. Delete of the index key

Delete from t_user where group_id='123'

Delete from t_user where group_id='123' > Affected rows: 0 > time: 0s summary

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

Use the where clause, and the columns in the where clause must be prefix index columns

Use limit

Use both the where clause and limit (at this point the column in the where clause may not be an index column)

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

Use the where clause, and the columns in the where clause must be prefix index columns

Use both the where clause and limit (at this point the column in the where clause may not be an index column) to execute successfully.

Thank you for your reading, the above is the content of the "mysql effectively prevent deletion of the library run method tutorial", after the study of this article, I believe you have a deeper understanding of the problem of the mysql effective prevention of deletion of the library run method tutorial, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.