子查询我相信大部分人都写过,但是昨天遇到一个比较坑的问题,由于有较好的备份,很短时间就恢复了误操作数据,但是这个问题值得分享。
首先建立如下测试表:
CREATE TABLE course
(
student_id
INT(11),
course
VARCHAR(20)
);
INSERT INTO course
VALUES ('1', '测试1');
INSERT INTO course
VALUES ('2', '测试2');
INSERT INTO course
VALUES ('3', '测试3');
CREATE TABLE student
(
id
INT(11),
name
VARCHAR(20)
) ;
INSERT INTO student
VALUES ('1', 'jiate');
INSERT INTO student
VALUES ('2', 'haoshen');
INSERT INTO student
VALUES ('3', 'leishen');
INSERT INTO student
VALUES ('4', 'tetui');
现在我们执行如下查询操作:
SELECT * FROM student WHERE id IN (SELECT id FROM course)
这个结果很明显,多了一条。而在昨天,我们开发写的是个update 操作,也是就:update student set name='特腿' where id in (select id from course) 本来更新1000条的数据,开发直接误更新了40万条。
仔细排查后,发现刚才两条sql发现 course 中根本就不存在id列,那么刚才那两条sql为啥没有抛出id报错,而是直接全表匹配了?
我们 desc extended 看看优化器究竟干了什么?
DESC EXTENDED SELECT * FROM student WHERE id IN (SELECT id FROM course)
SHOW WARNINGS
第一行1276 大意是说第二个查询中的 id 列在第一个表中被解析到
第二行就是解析器解析后的sql,我们贴出来看看
SELECT
yhtest
.student
.id
AS id
,
yhtest
.student
.name
AS name
FROM
yhtest
.student
semi
JOIN (yhtest
.course
)
WHERE (
yhtest
.student
.id
= yhtest
.student
.id
)
也就是说我们这个子查询中的id 列被解析为了主表的id 列,这样,where 条件是一个恒成立的条件,所以导致我们的查询过结果是a表的全表结果,而这也是这样的查询不抛错的原因。 子查询中的字段,首先会在子查询中查找,1)如果子查询中没该字段,则会去外层主表查找,如果能找到,也不会抛错! 2)如果子查询和主表中都没有该字段,则会抛错。 这其实是一个比较不理想的结果,如果我们无意中错误是update操作,那么这个结果太糟糕。 这个我们提了两个醒: 1)做数据库操作之前,最好都备份 2)平时写sql的时候,子查询尽量都写成表关联的形式,表关联形式,如果出现这种错误,会直接抛出错误。子查询形式,虽然好懂,一目了然,但是性能一般不大好,再一个,如果掉进了以上这种误更新坑中...... 3)在不同版本的数据库对于子查询的优化操作也是不尽相同,性能差异在不同版本之间还是比较明显的。