目錄

前言

1 MySQL練習的數據準備

2 Error Code:1175與安全更新模式

2.1 Error Code:1175

2.2 安全更新模式

3? 安全更新模式下的update和delete練習記錄

3.1 新問題1：Error Code: 1093

3.2 新問題2：要刪除的記錄在其他表中無關聯記錄

4 體驗一下安全更新模式的好處

附錄：建庫、建表和添加數據代碼

---

前言

最近我入坑了MySQL，跟著教程用裝在windows電腦上的Navicat for MySQL一步步操作，一切都很順利。為了緩解對軟件界面的審美疲勞，我用另一臺ubuntu電腦上的MySQL Workbench做鞏固練習。體驗下來，Workbench除了不支持中文，用起來還是很舒服的，而且還完全免費。然而，在做一些數據的修改（update）和刪除（delete）時，遇到了Error Code: 1175的問題。在解決問題的過程仍有一些困惑，還請各路大神指教。

1 MySQL練習的數據準備

我建了一個數據庫“college（大學）”，數據庫里包含三張數據表：department（院系）、teacher（教師）、achievement（成果）。建庫、建表以及添加數據的代碼見附錄。

這里說明一下各表的關聯關系，department表的主鍵是dep_id；teacher表的主鍵是tea_id，外鍵是dep_id，引用department表的主鍵dep_id；achievement表的主鍵是tea_id。因此，department具有主外鍵聯系，teacher和achievement依靠兩個id主鍵聯系，department和achievement沒有直接聯系，需要以teacher為中介。

下圖中achievement表里面的teacher_tea_id和外鍵fk_achievement_teacher1_idx都是系統添加的。這個關系圖的生成是在做完練習之后，在聯合查詢時用where語句構建了achievement和teacher表主鍵之間的關系，因此系統生成了這樣的外鍵。（在做查詢前畫這個圖是沒有的）

三張數據表的內容展示在這里：

2 Error Code:1175與安全更新模式

2.1 Error Code:1175

在練習中，我想實現這樣一個需求：將“張三”的職稱改為“研究員”，我照著教程依葫蘆畫瓢寫了這樣一個語句：

update teacher set title='研究員' where name='張三';

居然沒有執行成功，Workbench報出這樣一條錯誤提示：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.? To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

提示我現在使用的是安全更新模式（safe update mode），該模式下不允許在where子句中用不是“鍵(key)”的列作為條件，想要取消安全模式，需要在SQL Editor的首選項中關掉該模式并重新連接數據庫。

2.2 安全更新模式

把安全更新模式關掉當然是很簡單的，但是任何事物的存在都有其理由。去網上查了一下，對于這個模式作用一般都是下面的說法：

在mysql中，如果在update和delete沒有加上where條件，數據將會全部修改。不只是初識mysql的開發者會遇到這個問題，工作有一定經驗的工程師難免也會忘記寫入where條件。為了避免失誤造成的數據全部修改和刪除，可開啟mysql的安全模式。

感覺蠻有道理的，但是我們的update操作是有where的。從報錯信息中，我們看到在where子句內部還有一層限制，那就是必須以充當“鍵”的列作為條件。

對于安全更新模式的開啟與關閉，在Workbench中有兩種方法，一是用SQL語句，二是用客戶端的設置頁面。

先來看語句，查看安全模式是否開啟、開啟安全模式和關閉安全模式的語句都是比較簡單的：

# 查看是否開啟 show variables like 'SQL_SAFE_UPDATES'; # 開啟 SET SQL_SAFE_UPDATES = 1; # 關閉 SET SQL_SAFE_UPDATES = 0;

在Workbench客戶端中的設置方法也不難，按鍵過程是：Edit -> Preferences -> SQL Editor -> 勾選/取消勾選 "Safe Updates"，然后重新鏈接數據庫。

3? 安全更新模式下的update和delete練習記錄

作為嚴謹的技術學習者，當然不能一關了之，還是要琢磨一下如何在該模式下實現符合需求的update和delete操作。這里真正要思考的問題時如何完全依靠數據表的主外鍵關聯來完成指定條件下數據的修改與刪除。在這個過程中，又遇到了新問題。

3.1 新問題1：Error Code: 1093

繼續實現上面那個需求：將“張三”的職稱改為“研究員”。我這個技術小白傻不拉幾地模仿之前學的子查詢的語句寫了一條更新語句：

# 被模仿的子查詢語句 select * from teacher where tea_id=(select tea_id from teacher where name='張三');# 依葫蘆畫瓢寫的更新語句 update teacher set title='研究員' where tea_id=(select tea_id from teacher where name='張三');

上面的語句執行起來沒有問題，下面的語句執行出錯，報出代碼為1093的錯誤：

Error Code: 1093. You can't specify target table 'teacher' for update in FROM clause

說是不能在FROM子句中為update操作指定目標表“teacher”。想想也是，所謂“子查詢”就是查詢里面套著查詢，子查詢操作確實不該套在更新語句語句里面。

不過，查詢還是要查詢，得不到張三的id怎么通過“鍵”來設定數據修改的條件呢？此時，我突然想到，查詢出來的結果不也是一個表嗎？把查詢得到的表和要修改的數據表進行連接(join)不就行了嗎？于是，我又寫了下面的語句：

# 將查詢得到的 虛擬表 和 要修改的表 進行內連接 update teacher t inner join (select tea_id from teacher where name='張三') t2 set title='研究員' where t.tea_id=t2.tea_id;# 寫成如下形式效果是一樣的 update teacher t, (select tea_id from teacher where name='張三') t2 set title='研究員' where t.tea_id=t2.tea_id;

運行上面的語句，輸出框終于從紅色的叉變成了綠色的勾，修改生效了！

刪除同樣可以用連接(join)的方式實現，比如這樣一條刪除需求：刪除“ 數學系”相關的全部記錄，這意味著不僅要在要在department表中刪除數學系，還要在teacher表和achievement表中刪除與之關聯的記錄，包括數學系教師及其科研成果。非安全模式和安全模式下的刪除語句如下：

# 這種寫法在 不打開安全模式 的情況下是可行的 delete d,t,a from department d, teacher t, achievement a where d.dep_name='數學系' and t.tea_id=a.tea_id;# 安全模式下可以用 連接 的方式實現 delete d,t,a from department d, teacher t, achievement a, (select dep_id from department d where dep_name='數學系') d2 where d.dep_id=d2.dep_id and t.dep_id=d.dep_id and t.tea_id=a.tea_id;

可以看到，院系表中的“數學系”不見了，教師表中的“王五”也被刪掉了，成果表中也少了id為21020的記錄。

3.2 新問題2：要刪除的記錄在其他表中無關聯記錄

刪完“數學系”的相關記錄后還想把“機電系”的也刪掉。當然，作為一個練習，我們知道機電系在其他表中沒有關聯記錄，即沒有老師，當然也沒有成果。為了模擬實際應用場景，我們假裝不知道這個情況，仍然沿用上面的刪除語句：

delete d,t,a from department d, teacher t, achievement a , (select dep_id from department d where dep_name='機電系') d2 where d.dep_id=d2.dep_id and t.dep_id=d.dep_id and t.tea_id=a.tea_id;

語句執行是成功的，沒有報錯。但是 0 row(s) affected，也就是根本沒有刪除成功！

仔細想了想，我得出這樣的理解，不知道對不對。這里的where子句需要指明t,d,a,d2四表的關聯關系，四個表需要三個等號（=）。由于t表中沒有“機電系”老師的信息，那么t表和虛擬表d2其實是無法構成關聯的，雖然語句中寫的是t和d兩個表主鍵的關聯關系（t.dep_id=d.dep_id），但是d又要與d2有關聯，因此t和d2應該有經由d傳遞而來的間接聯系，然而并沒有，這就不合邏輯了。如此一來，整個where語句的條件無法被滿足，刪除也就不會執行了。如果我的理解是錯的，還請看到此文的大神給予指正，小弟感激不盡。

為了解決這個問題，以一個剛入門小白的知識儲備，只能是先查一下要刪除記錄的數據表是否有關聯的記錄，然后在刪除語句中加入需要涉及到的數據表。如果看到此文的大佬有更好的解決方法，也請給小弟指點迷津。

這里采用左連接的方式查詢各表的主鍵，因為刪除條件來自院系表（d），所以主表是d表。查詢的目的是看t和a能否匹配出數據，哪個表匹配不出數據（得出null），刪除時就不涉及哪個表。

select d.dep_id, t.tea_id t_id, a.tea_id a_id from department d left join teacher t on d.dep_id=t.dep_id left join achievement a on a.tea_id=t.tea_id where dep_name='機電系';

根據上面查詢得出的結果，刪除操作應該只涉及department表。

delete d from department d, (select dep_id from department d where dep_name='機電系') d2 where d.dep_id=d2.dep_id;

終于刪掉了。

4 體驗一下安全更新模式的好處

在這里體會一下在關閉安全更新模式的情況下可能出現的問題。如上文引用的一般說法，忘記加where條件的可能性估計不大，但是在表比較多的情況下，where子句中漏寫表的關聯關系是有可能的，這也會造成一些可怕的情況。

如果where子句中漏寫了一個關聯關系，仍然會報出1175錯誤，還拿上面多表刪除的語句作為例子，比如這次要刪的“計算機系”：

# teacher表 和 achievement表 的關聯關系沒有寫 delete d,t,a from department d, teacher t, achievement a inner join (select dep_id from department d where dep_name='計算機系') d2 where d.dep_id=d2.dep_id and t.dep_id=d.dep_id;

報出 Error Code:1175 的錯誤：

下面看看把安全模式關掉會怎么樣，首先執行關閉語句，再查看一下：

# 關掉安全更新模式 SET SQL_SAFE_UPDATES = 0; # 查看一下 show variables like 'SQL_SAFE_UPDATES';

現在已經關掉了。再執行上面where子句沒寫全關聯關系的刪除語句，可怕的情況出現了，下面是執行完之后各個數據表的情況：

department表是正常的，刪之前只剩下中文系和計算機系，刪之后應該只剩下中文系。teacher表也是對的，刪除后只剩下兩位中文系老師。出問題的是achievement表，居然被刪空了！！！其原因就在于where子句中漏寫了a表和t表的關聯關系，如果安全更新模式是開啟的，就能避免這種災難的發生。

附錄：建庫、建表和添加數據代碼

# 創建 數據庫 大學 并 使用 CREATE DATABASE college /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */; USE college;# 創建 數據表 院系 并 添加 數據 CREATE TABLE department (dep_id varchar(2) NOT NULL,dep_name varchar(20) DEFAULT NULL,PRIMARY KEY (dep_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO department VALUES('10', '中文系'); INSERT INTO department VALUES('20', '數學系'); INSERT INTO department VALUES('30', '計算機系'); INSERT INTO department VALUES('31', '機電系');# 創建 數據表 教師 并 添加 數據 CREATE TABLE teacher (tea_id varchar(5) NOT NULL,`name` varchar(10) DEFAULT NULL,gender char(3) DEFAULT NULL,title varchar(10) DEFAULT NULL,dep_id varchar(4) DEFAULT NULL,PRIMARY KEY (tea_id),CONSTRAINT fk_tea_dep FOREIGN KEY (dep_id) REFERENCES department (dep_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO teacher VALUES ('21010', '張三', '男', '教授', '10'); INSERT INTO teacher VALUES ('21011', '李四', '女', '副教授','10'); INSERT INTO teacher VALUES ('21020', '王五', '男', '講師', '20'); INSERT INTO teacher VALUES ('21030', '趙六', '男', '助教', '30');# 創建 數據表 科研成果 并 添加 數據 CREATE TABLE achievement (tea_id varchar(5) NOT NULL,essay_num int(11) DEFAULT NULL,monograph_num int(11) DEFAULT NULL,patent_num int(11) DEFAULT NULL,PRIMARY KEY (tea_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO achievement VALUES ('21010', 10, 2, 0); INSERT INTO achievement VALUES ('21011', 8, 4, 0); INSERT INTO achievement VALUES ('21020', 5, 1, 1); INSERT INTO achievement VALUES ('21030', 6, 0, 2);

總結

以上是生活随笔為你收集整理的【MySQL随手记】一个踩坑记录：在安全更新模式下进行数据的修改与删除的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。