After adding the option -U to the mysql command, when issuing an UPDATE or DELETE without the WHERE or LIMIT keywords, the MySQL program will refuse to execute. Then, based on this setting provided by MySQL, we can easily realize how to prevent human misoperation of the MySQL database to the greatest extent. What? You do not believe? If you don't believe me, let's start with the help of MySQL, and let's take a look at how to prevent human misoperation of the MySQL database to the greatest extent based on the -u option of MySQL.

MySQL Help Instructions

[root@binghe~]# mysql --help|grep dummy      
 -U, --i-am-a-dummy Synonym for option --safe-updates, -U.
i-am-a-dummy      FALSE

After adding the option -U to the mysql command, when issuing an UPDATE or DELETE without the WHERE or LIMIT keywords, the MySQL program will refuse to execute.

specify -U login test

[root@binghe~]# mysql -uroot -proot -S /data/3306/mysql.sock -UWelcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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: It cannot be deleted without adding conditions, and the purpose is achieved.

Specify an alias

We can make the commands for operating MySQL into aliases to prevent others and DBA from misoperating the database. It is also very simple to make the commands for operating MySQL into aliases. Here, we directly give an example, as shown below.

[root@binghe~]# alias mysql='mysql -U'[root@binghe~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sockWelcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.24-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

After setting alias mysql='mysql -U' in the command line session, it can only be valid for the current session. After closing the command line of the current connection server, the session is invalid, and the alias setting will also be invalid. If you want to close the session terminal connecting to the server, the alias setting is still valid, or multiple sessions can use this alias to operate the database, we can add the command to set the alias to the /etc/profile system environment variable, as shown below.

[root@binghe~]# echo "alias mysql='mysql -U'" >>/etc/profile[root@binghe~]# . /etc/profile[root@binghe~]# tail -1 /etc/profilealias mysql='mysql -U'

In this way, when we exit the session terminal that is currently connected to the server, the MySQL alias setting is still valid. Every time we connect to the server, we don't need to reset the MySQL command alias in the current session, just use it directly.

Summarize

The MySQL program refuses to execute when an UPDATE or DELETE is issued without the WHERE or LIMIT keywords after the mysql command plus option -U.