How to prevent human error in MySQL database _ MySQL

Examples of preventing human error in the MySQL database explain that many developers have encountered misoperations when operating the MySQL database. for example, when updating the database, the where condition is not added to the update statement, this will cause extremely tragic results. This article provides the following details on how to prevent MySQL database misoperation:

1. mysql help

# mysql --help|grep dummy   -U, --i-am-a-dummy Synonym for option --safe-updates, -U. i-am-a-dummy  FALSE

After the option-U is added to the mysql command, the mysql program will refuse to execute the UPDATE or DELETE statement without the WHERE or LIMIT keyword.

2. specify-U for logon test

# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 14 Server version: 5.5.32-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '/h' for help. Type '/c' to clear the current input statement. mysql> delete from oldboy.student; 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> quit Bye

Tip: you cannot delete it without any conditions. The goal is achieved.

3. create an alias to prevent DBA misoperation

# alias mysql='mysql -U' # mysql -uroot -poldboy123 -S /data/3306/mysql.sock Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 15 Server version: 5.5.32-log MySQL Community Server (GPL) Type 'help;' or '/h' for help. Type '/c' to clear the current input statement. mysql> delete from oldboy.student; 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 oldboy.student where Sno=5; Query OK, 1 row affected (0.02 sec) mysql> quit Bye # echo "alias mysql='mysql -U'" >>/etc/profile # . /etc/profile # tail -1 /etc/profile alias mysql='mysql -U'

Conclusion:After the option-U is added to the mysql command, the mysql program rejects execution when an UPDATE or DELETE statement without the WHERE or LIMIT keyword is sent.