MySQL防止无where条件update操作

   生产环境中，DBA的粗心造成的数据错误最危险，为了防止无where条件的update语句出现错误，可使用如下方法。
1、加-U登录数据库

点击(此处)折叠或打开

1. [root@master ~]# mysql -uroot -pmysql -U
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Welcome to the MySQL monitor. Commands end with ; or \g.
4. Your MySQL connection id is 12
5. Server version: 5.7.19-log Source distribution
7. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15. mysql>
16. mysql> use test;
17. Reading table information for completion of table and column names
18. You can turn off this feature to get a quicker startup with -A
20. Database changed
21. mysql> show tables;
22. +----------------+
23. | Tables_in_test |
24. +----------------+
25. | t1 |
26. +----------------+
27. 1 row in set (0.00 sec)
29. mysql> show create table t1;
30. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
31. | Table | Create Table |
32. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
33. | t1 | CREATE TABLE `t1` (
34.   `admin_id` int(11) NOT NULL DEFAULT '0',
35.   `admin_name` varchar(200) NOT NULL,
36.   `password` varchar(200) NOT NULL,
37.   `role` int(11) NOT NULL DEFAULT '1',
38.   `last_login_time` datetime DEFAULT NULL,
39.   `last_login_ip` varchar(16) DEFAULT '',
40.   `admin_status` int(11) NOT NULL DEFAULT '1',
41.   `create_time` datetime DEFAULT NULL
42. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
43. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
44. 1 row in set (0.00 sec)
46. mysql> update t1 set admin_status=1234;
47. ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

此处可以看到数据库给出了提示，不允许没有where条件的update操作。2、根据这样的特性，对系统环境变量进行更改
vi /etc/profile
alias mysql='mysql -U'
source /etc/profile
3、正常命令登录MySQL，进行测试

点击(此处)折叠或打开

1. [root@master ~]# mysql -uroot -pmysql
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Welcome to the MySQL monitor. Commands end with ; or \g.
4. Your MySQL connection id is 14
5. Server version: 5.7.19-log Source distribution
7. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15. mysql> use test;
16. Reading table information for completion of table and column names
17. You can turn off this feature to get a quicker startup with -A
19. Database changed
20. mysql> update t1 set admin_status=1234;
21. ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column