MySQL SQL语句EXISTS

原创 CuiHulong 2021-11-29

2448

MySQL中EXITS语句用于查明表中是否存在特定的行。普遍情况下EXITS与子查询一起使用，并返回与子查询返回的结果相等或匹配的行。如果行在表中存在，则返回true，否则返回false。在MySQL中使用EXISTS是低效的，因为EXISTS对查理表中的每一行都要重新运行。

1. 常用方式

通过例子，了解EXISTS返回的两种情况：TRUE(1), FALSE(0)

mysql> CREATE TABLE students(
  id int PRIMARY KEY,
  firstName varchar(255) DEFAULT NULL,
  lastName varchar(255) DEFAULT NULL
);
INSERT INTO students(id,firstName,lastName) 
 VALUES(1,"Preet","Sanghavi"), (2,"Rich","John"), (3,"Veron","Brow"), (4,"Geo","Jos"), (5,"Hash","Shah"), (6,"Sachin","Parker"), (7,"David","Miller");

mysql> SELECT *FROM STUDENTS;
+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
|  1 | Preet     | Sanghavi |
|  2 | Rich      | John     |
|  3 | Veron     | Brow     |
|  4 | Geo       | Jos      |
|  5 | Hash      | Shah     |
|  6 | Sachin    | Parker   |
|  7 | David     | Miller   |
+----+-----------+----------+
7 rows in set (0.00 sec)

#查询id
mysql> SELECT EXISTS(SELECT * from students WHERE id=4) as RESULT;
+--------+
| RESULT |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

#查询不存在数据
mysql> SELECT EXISTS(SELECT * from students WHERE id=11) as RESULT;
+--------+
| RESULT |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

#执行计划
mysql> EXPLAIN SELECT EXISTS(SELECT * from students WHERE id=4) as RESULT;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+----------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra          |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+----------------+
|  1 | PRIMARY     | NULL     | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | No tables used |
|  2 | SUBQUERY    | students | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index    |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+----------------+

2.往往容易误写用法

没有条件限制，扫描所有行，最终结果只返回一条TRUE(1),不管是不是NULL

mysql > SELECT EXISTS(SELECT * from students ) as RESULT;
+--------+
| RESULT |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

CREATE TABLE `course` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `stu_id` int NOT NULL,
  `course_name` varchar(20)  NOT NULL,
  `st_couse` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu` (`stu_id`)
);
INSERT INTO  course(stu_id,course_name,st_couse) VALUES(1,'语文',98),(2,'数学',89),(3,'英语',92);

mysql> SELECT st.* FROM students st
       WHERE EXISTS (SELECT cs.stu_id FROM course cs  );
+----+-----------+----------+------+
| id | firstName | lastName | age  |
+----+-----------+----------+------+
|  1 | Preet     | Sanghavi | NULL |
|  2 | Rich      | John     | NULL |
|  3 | Veron     | Brow     | NULL |
|  4 | Geo       | Jos      | NULL |
|  5 | Hash      | Shah     | NULL |
|  6 | Sachin    | Parker   | NULL |
|  7 | David     | Miller   | NULL |
+----+-----------+----------+------+
7 rows in set (0.00 sec)

mysql> SELECT st.* FROM students st
      WHERE EXISTS (SELECT cs.stu_id FROM course cs WHERE cs.stu_id = st.id  );
+----+-----------+----------+------+
| id | firstName | lastName | age  |
+----+-----------+----------+------+
|  1 | Preet     | Sanghavi | NULL |
|  2 | Rich      | John     | NULL |
|  3 | Veron     | Brow     | NULL |
+----+-----------+----------+------+
3 rows in set (0.00 sec)

注意：两次查询结果不一样，仔细对比上面两条 sql 语句，where 语句后面直接跟了exists ，并没有指定关联条件。这里EXISTS 只在乎WHERE里的数据能不能查找出来，是否存在这样的记录。
其运行方式是先运行主查询一次，再去子查询里查询与其对应的结果如果存在返回ture则输出,反之返回false则不输出,再根据主查询中的每一行去子查询里去查询。适合外查询表小，子查询表大的情况，毕竟要一行抽取进行匹配。

3.优化SQL语句

EXISTS是否可以改写进行优化，下面是改写成内连接方式：

mysql> SELECT st.* FROM students st  INNER JOIN course cs  ON   cs.stu_id = st.id ;

执行计划对比：

Semi Join-LooseScan: 把数据基于索引进行分组，取每组数据进行匹配

EXPLAIN ANAYLZE实际执行：

从上诉对比中可以看出，在少量的数据中也存在明显的差异。

4. DDL中EXISTS

在DDL语句创建或删除中添加EXISTS，还是有很多好处的。
如果没有IF EXISTS，语句将失败，并出现一个错误，表明无法删除哪些不存在的表，并且不会进行任何更改。

使用IF EXISTS时，对于不存在的表不会发生错误。该语句删除所有确实存在的已命名表，并为每个不存在的表生成一个NOTE诊断。这些注释可以通过SHOW WARNINGS显示

如果在数据字典中有一个条目，但存储引擎没有管理表的特殊情况下，IF EXISTS也可以用于删除表。(例如，在从存储引擎中删除表之后，在删除数据字典条目之前，服务器异常退出。有一定的效果，但有时未必可行)

2021-11-24T11:33:54.885641Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './db9/t1.ibd' OS error: 71
2021-11-24T11:33:54.885645Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-11-24T11:33:54.885648Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-11-24T11:33:54.885651Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-11-24T11:33:54.885656Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `db9/t1`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

EXISTS删除操作：

mysql> DROP DATABASE db9;
ERROR 3679 (HY000): Schema directory './db9/' does not exist
mysql> DROP DATABASE IF EXISTS db9;
Query OK, 0 rows affected, 1 warning (0.03 sec)