【MySQL随手记】一个踩坑记录：在安全更新模式下进行数据的修改与删除

目录

前言

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