前言
在應用 BUG或者 DBA誤操作的情況下，會發生對全表進行更新：update delete 的情況。MySQL提供 sql_safe_updates 來限制次操作。
set sql_safe_updates = 1;
設置之后，會限制update delete 中不帶 where 條件的SQL 執行，較嚴格。會對已有線上環境帶來不利影響。對新系統、應用做嚴格審核，可以確保不會發生全表更新的問題。
CREATE TABLE working.test01 (id INT NOT NULL AUTO_INCREMENT,NAME VARCHAR(20),age INT,gmt_created DATETIME,PRIMARY KEY(id));
insert into test01(name,age,gmt_created) values('xiaowang',2,now());
insert into test01(name,age,gmt_created) values('huahua',5,now());
insert into test01(name,age,gmt_created) values('gougou',9,now());
insert into test01(name,age,gmt_created) values('heihei',12,now());
insert into test01(name,age,gmt_created) values('baibai',134,now());
# 過濾字段上沒有索引
update
update test01 set name = 'xiaoxiao' where age = 2 ；
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
# 全表更新
update test01 set name = 'xiaoxiao';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
# 加入limit的更新
update test01 set name = 'xia' limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 新增索引
create index idx_age on test01(age);
update test01 set name = 'xiaoxiao' where age = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
update test01 set name = 'hhh' where age = 9 limit 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
alter table test01 drop index idx_age;
create index idx_age_name on test01(age,name);
update test01 set age= 100 where name = 'hhh';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
update test01 set age= 100 where name = 'hhh' limit 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
由此，update 時，在沒有 where 條件或者where 后不是索引字段時，必須使用 limit ；在有 where 條件時，為索引字段
最近在工作中又發現了一個問題，mysql sql_safe_updates 不支持子查詢的更新。
考慮到開發人員有時候不小心誤更新數據，要求線上庫的 MySQL 實例都設置 sql_safe_updates=1 來避免沒有索引的 update、delete。
結果有一天開發發現下面的一個SQL 沒法正確執行：
update t1 set col2=1 where key1 in (select col2 from t2 where key2='ABcD');
錯誤如下：
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條件進行更新。搜索了下發現，的確不行。及時 key1 和key2 分別是 t1、t2 的索引[我換成主鍵都不行] 。說明是不支持子查詢的update。
google 了一下發現人家也問過這個問題。。
http://stackoverflow.com/questions/24314830/query-not-getting-executed-if-supplied-a-nested-sub-query
最后解決方法：
1）修改 session 級別的參數： set sql_safe_updates=0; 執行 update 操作。退出終端。
2）程序處理：先 select col2 from t2 where key2='ABcD' 獲取數據，然后循環處理結果，并用 update t1 set col2=1 where key1=？ 來批量更新過。建議還是用程序處理，臨時修改變量不是長久之計。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- MySQL UPDATE更新語句精解
- Mysql聯表update數據的示例詳解
- 實例驗證MySQL|update字段為相同的值是否會記錄binlog
- mysql update語句的執行過程詳解
- MySQL select、insert、update批量操作語句代碼實例
- Mysql update多表聯合更新的方法小結
- MySQL執行update語句和原數據相同會再次執行嗎
- mysql事務select for update及數據的一致性處理講解
- Mysql Update批量更新的幾種方式
- MYSQL updatexml()函數報錯注入解析
- mysql中錯誤：1093-You can’t specify target table for update in FROM clause的解決方法
- mybatis執行批量更新batch update 的方法（oracle,mysql兩種）
- 記一次MySQL更新語句update的踩坑
