[MySQL分享]--sql_safe_updates小知识ERROR 1175 (HY000)

来源：互联网发布：java ioc简书编辑：程序博客网时间：2024/05/17 08:41

Error 1175 Safe Updtes Mode

错误提示如下:
ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
错误的原因是启用了MySQL的Safe Updtes Mode，它的作用是如果执行Update和Delete操作的时候，
没有带键限制的where语句或limit语句，sql操作不会执行。详细的介绍可参考MySQL Tips: safe-updates。
http://dev.mysql.com/doc/refman/5.5/en/mysql-tips.html#safe-updates
可使用SET sql_safe_updates=0;来暂时禁用Safe Updates Mode。

这个参数在mysqld下报错
#mysql -S /tmp/mysql3307.sock -uroot -p
mysql: unknown variable 'sql_safe_updates=1'

2015-09-28 17:41:56 21240 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'SQL_SAFE_UPDATES=1'
2015-09-28 17:44:46 22005 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'sql_safe_updates=1'

1.mysql带 --safe-updates 参数
[#14(cieway_idc(10.12.0.13))#root@DevelopServer~]#mysql -S /tmp/mysql3307.sock -uroot -p --safe-updates
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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.

(casystem)root@localhost [(none)]>
(casystem)root@localhost [(none)]>
(casystem)root@localhost [(none)]> use test;
Database changed
(casystem)root@localhost [test]> update T_TMP_TEST set CREATETIME=now();
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
(casystem)root@localhost [test]>

2.set sql_safe_updates=1;和set global sql_safe_updates=1;

(test)root@localhost [test]> set sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

(test)root@localhost [test]> show global variables like '%sql_safe%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | OFF |
+------------------+-------+
1 row in set (0.00 sec)

(test)root@localhost [test]> show variables like '%sql_safe%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
1 row in set (0.00 sec)

(test)root@localhost [test]> update T_TMP_TEST set CREATETIME=now();
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
(test)root@localhost [test]> update T_TMP_TEST set CREATETIME=now() where 1=1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
(test)root@localhost [test]> update T_TMP_TEST set CREATETIME=now() where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

(test)root@localhost [test]> update T_TMP_TEST set CREATETIME=now() where id>=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

(test)root@localhost [test]> update T_TMP_TEST set CREATETIME=now() where id>=0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

(test)root@localhost [test]>

(test)root@localhost [test]> delete from T_TMP_TEST;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
(test)root@localhost [test]> delete from T_TMP_TEST where 1=1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
(test)root@localhost [test]> delete from T_TMP_TEST where id=1;
Query OK, 0 rows affected (0.00 sec)

(test)root@localhost [test]>

0 0