乐趣区

MySQL的NOT-EXISTS遭遇战

jiezi

浅尝一下NOT EXISTS

最近老婆在看视频学习 MySQL，然后碰到了这样一道习题：有三个表，分别记录学生、课程，以及学生选修了什么课程的信息，问如何用 NOT EXISTS 找出选修了所有课程的学生。

为了避免想破脑袋编造一些尴尬的学生姓名和课程名，我简化了一下习题中的表的结构，只留下它们的 ID 列。建表语句如下

-- 学生表
CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 课程表
CREATE TABLE `course` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 选修关系
CREATE TABLE `elective` (
  `student_id` INT NOT NULL,
  `course_id` INT NOT NULL,
  FOREIGN KEY (`student_id`) REFERENCES `student`(`id`),
  FOREIGN KEY (`course_id`) REFERENCES `course`(`id`)
);

还需要给它们塞入一些示例数据

INSERT INTO `student` (`id`) VALUES (1), (2), (3), (4), (5);
INSERT INTO `course` (`id`) VALUES (1), (2);
INSERT INTO `elective` (`course_id`, `student_id`) VALUES (1, 1), (2, 1), (1, 2), (2, 3), (2, 5), (1, 5);

显然，只有 id 列的值为 1 和 5 的学生是选修了全部课程的。用 NOT EXISTS 写出来的 SQL 语句如下

SELECT * 
FROM   `student` 
WHERE  NOT EXISTS (SELECT * 
                   FROM   `course` 
                   WHERE  NOT EXISTS (SELECT * 
                                      FROM   `elective` 
                                      WHERE  `student`.`id` = 
                                             `elective`.`student_id` 
                                             AND `course`.`id` = 
                                     `elective`.`course_id`));

在 DBEaver 中运行后的结果为

正确地找出了两个选修了所有课程的学生的id。

如何理解双重NOT EXISTS

当第一次被请教这道习题的时候，我其实并不能理解 NOT EXISTS 的含义。直到后来去看 EXISTS 的文档，才顿悟了上面的 SQL。

我的理解方法是将双重 NOT EXISTS 转换为三层循环。以上面的 SQL 为例，转述为人话就是：找出 student 表中所有的、没有 任何一门 course 表中的课程是 没有 选修的、的学生——双重的 没有。

转换为三层循环大概长这样

for (const student of students) {
  // 是否存在学生未选修的课程
  let existSuchCourse = false;
  for (const course of courses) {
    let existSuchElective = false;
    for (const elective of electives) {if (elective.student_id === student.id && elective.course_id === course.id) {
        existSuchElective = true;
        break;
      }
    }
    // 如果遍历完 elective 表的记录后，existSuchElective 仍然为 false，说明的确有一门课程是没有选修记录的
    // 那么便意味着“存在至少一门课程，使得当前被遍历的学生与该课程没有选修关系”。if (!existSuchElective) {
      existSuchCourse = true;
      break;
    }
  }
  // 如果遍历完一圈后确实没有找到“未选修”的课程，说明这名学生全都选修了
  if (!existSuchCourse) {console.log(student);
  }
}

NOT EXISTS的本质

即使不强行理解，也可以让 MySQL 明确告知双重 NOT EXISTS 是怎么运作的。用 EXPLAIN 解释上面的 SQL 的结果如下图所示

MySQL 的 EXPLAIN 命令的文档中说明了如何解读执行计划

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. This means that MySQL reads a row from the first table, then finds a matching row in the second table, and then in the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

以上面的 EXPLAIN 为例，MySQL 从 student 表中读出一行，再从 course 表中读取一行，最后从 elective 表中读取一行，然后看看 WHERE 子句是否能够被满足。如果可以，就输出从 student 表中读出来的这行数据。上图第 2 和第 3 行的 select_type 都是 DEPENDENT SUBQUERY，表示它们依赖于“外层”的查询上下文——elective 的WHERE子句依赖于 student 和course中读出来的行。

似乎和方才的三重循环有异曲同工之妙呢。

后记

像 NOT EXISTS 这么“高阶”的功能我从未在业务代码中读过和使用过——别说 NOT EXISTS，就算是EXISTS 也是从未有之，甚至连子查询也极少。毕竟“正经的互联网公司”只是把 MySQL当妹妹 当一个具备复杂查询查询功能的 key-value 数据库来使用（笑

比起双重NOT EXISTS，我更可能凭直觉写出基于子查询的解决方法

SELECT * 
FROM   `student` 
WHERE  `id` IN (SELECT `student_id` 
                FROM   `elective` 
                GROUP  BY `student_id` 
                HAVING(Count(0) ) = 2);

我甚至觉得会有人把数据库里的行读进内存然后用应用层代码来找出选修了全部课程的学生！

全文完。

阅读原文

退出移动版