Mysql database prevents examples of unremnous operations-MYSQL

Mysql database prevents examples of unremnous operations

Technical otaku 2021-12-27

Many developers have encountered misoperations when operating the MySQL database.For example, when updating the database, forget to add the where condition to the update statement, which will cause extremely tragic results.This article gives the following detailed explanations on the methods to prevent the misoperation of the MySQL database:

1, mysqlHelp Instructions

# 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, when UPDATE or DELETE without the WHERE or LIMIT keywords is issued, the mysql program will refuse to execute

2, specify-U login 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

Reminder: It cannot be deleted without adding conditions.The purpose is achieved.

3.Make 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 adding the option-U to the mysql command, when an UPDATE or DELETE without the WHERE or LIMIT keywords is issued, the mysql program refuses Execute

Technical otaku

Sought technology together