快捷導(dǎo)航

MySQL中參數(shù)sql_safe_updates在生產(chǎn)環(huán)境的使用詳解

更新時(shí)間：2017年11月27日 09:51:01 作者：笱局長(zhǎng)

這篇文章主要給大家介紹了關(guān)于MySQL中參數(shù)sql_safe_updates在生產(chǎn)環(huán)境使用的相關(guān)資料，并給大家分享了解決mysql sql_safe_updates不支持子查詢(xún)更新的方法，分享出來(lái)供大家參考學(xué)習(xí)，需要的朋友們下面來(lái)一起看看吧。

前言

在應(yīng)用 BUG或者 DBA誤操作的情況下，會(huì)發(fā)生對(duì)全表進(jìn)行更新：update delete 的情況。MySQL提供 sql_safe_updates 來(lái)限制次操作。

set sql_safe_updates = 1;

設(shè)置之后，會(huì)限制update delete 中不帶 where 條件的SQL 執(zhí)行，較嚴(yán)格。會(huì)對(duì)已有線(xiàn)上環(huán)境帶來(lái)不利影響。對(duì)新系統(tǒng)、應(yīng)用做嚴(yán)格審核，可以確保不會(huì)發(fā)生全表更新的問(wèn)題。

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()); 

# 過(guò)濾字段上沒(méi)有索引
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 時(shí)，在沒(méi)有 where 條件或者where 后不是索引字段時(shí)，必須使用 limit ；在有 where 條件時(shí)，為索引字段

最近在工作中又發(fā)現(xiàn)了一個(gè)問(wèn)題，mysql sql_safe_updates 不支持子查詢(xún)的更新。

考慮到開(kāi)發(fā)人員有時(shí)候不小心誤更新數(shù)據(jù)，要求線(xiàn)上庫(kù)的 MySQL 實(shí)例都設(shè)置 sql_safe_updates=1 來(lái)避免沒(méi)有索引的 update、delete。

結(jié)果有一天開(kāi)發(fā)發(fā)現(xiàn)下面的一個(gè)SQL 沒(méi)法正確執(zhí)行：

update t1 set col2=1 where key1 in (select col2 from t2 where key2='ABcD');

錯(cuò)誤如下：

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

也就是說(shuō)沒(méi)法對(duì)沒(méi)有走到索引的where條件進(jìn)行更新。搜索了下發(fā)現(xiàn)，的確不行。及時(shí) key1 和key2 分別是 t1、t2 的索引[我換成主鍵都不行] 。說(shuō)明是不支持子查詢(xún)的update。

google 了一下發(fā)現(xiàn)人家也問(wèn)過(guò)這個(gè)問(wèn)題。。

http://stackoverflow.com/questions/24314830/query-not-getting-executed-if-supplied-a-nested-sub-query

最后解決方法：

1）修改 session 級(jí)別的參數(shù)： set sql_safe_updates=0; 執(zhí)行 update 操作。退出終端。

2）程序處理：先 select col2 from t2 where key2='ABcD' 獲取數(shù)據(jù)，然后循環(huán)處理結(jié)果，并用 update t1 set col2=1 where key1=？ 來(lái)批量更新過(guò)。建議還是用程序處理，臨時(shí)修改變量不是長(zhǎng)久之計(jì)。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章: