欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库防止人为误操作的实例讲解

程序员文章站 2024-03-01 10:24:46
有不少开发人员在操作mysql数据库的时候都遇到过误操作的情况,例如更新数据库的时候update语句忘记加上where条件,就会造成极为悲剧的结果。本文就针对防止mysql...

有不少开发人员在操作mysql数据库的时候都遇到过误操作的情况,例如更新数据库的时候update语句忘记加上where条件,就会造成极为悲剧的结果。本文就针对防止mysql数据库误操作的方法做出如下详解:

1、mysql帮助说明

# mysql --help|grep dummy  
 -u, --i-am-a-dummy synonym for option --safe-updates, -u. 
i-am-a-dummy  false

在mysql命令加上选项-u后,当发出没有where或limit关键字的update或delete时,mysql程序就会拒绝执行

2、指定-u登录测试

# 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

提示:此处不加条件无法删除,目的达到。

3、做成别名防止dba误操作

# 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'

结论:在mysql命令加上选项-u后,当发出没有where或limit关键字的update或delete时,mysql程序拒绝执行