概览
最近的 MySQL 版本( 8.0.31 ) 中,新增了对 SQL 标准 INTERSECT
和 EXCEPT
表操作符(集合运算)的支持。
准备工作
以下表为例,建表语句如下:
mysql> CREATE TABLE `t_stu_cou_rel`(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
stu_id INT UNSIGNED NOT NULL,
cou_id INT UNSIGNED NOT NULL,
notes VARCHAR(100),
start_date DATE,
end_date DATE) ENGINE=INNODB;
插入一些样例数据:
mysql> INSERT INTO t_stu_cou_rel VALUES(1,90001,3001,NULL,'20220905','20230117'),
(2,90002,3001,NULL,'20220905','20230117'),
(3,90003,3001,NULL,'20220905','20230117'),
(4,90001,2015,NULL,'20220901','20230110'),
(NULL,90003,3003,'Public Elective Course.','20221013',NULL);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t_stu_cou_rel;
+----+--------+--------+-------------------------+------------+------------+
| id | stu_id | cou_id | notes | start_date | end_date |
+----+--------+--------+-------------------------+------------+------------+
| 1 | 90001 | 3001 | NULL | 2022-09-05 | 2023-01-17 |
| 2 | 90002 | 3001 | NULL | 2022-09-05 | 2023-01-17 |
| 3 | 90003 | 3001 | NULL | 2022-09-05 | 2023-01-17 |
| 4 | 90001 | 2015 | NULL | 2022-09-01 | 2023-01-10 |
| 5 | 90003 | 3003 | Public Elective Course. | 2022-10-13 | NULL |
+----+--------+--------+-------------------------+------------+------------+
5 rows in set (0.00 sec)
交集( INTERSECT )
**Query 1:**查询选修了课程 3001 的学生的学号:
mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '3001';
+--------+
| stu_id |
+--------+
| 90001 |
| 90002 |
| 90003 |
+--------+
3 rows in set (0.00 sec)
**Query 2:**查询选修了课程 2015 的学生的学号:
mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '2015';
+--------+
| stu_id |
+--------+
| 90001 |
+--------+
1 row in set (0.00 sec)
执行如下语句,查询选修了课程 3001 和 2015 的学生的学号 stu_id ,即 Query 1 INTERSECT
Query 2:
mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '3001' INTERSECT SELECT stu_id FROM t_stu_cou_rel where cou_id = '2015';
+--------+
| stu_id |
+--------+
| 90001 |
+--------+
1 row in set (0.00 sec)
差集( EXCEPT)
同样,以上面两个查询 Query 1 和 Query 2 为前提,这次我们查询选修了 3001 课程而没选修 2015 课程的学生的学号 stu_id ,即 Query 1 EXCEPT
Query 2 :
mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '3001' EXCEPT SELECT stu_id FROM t_stu_cou_rel where cou_id = '2015';
+--------+
| stu_id |
+--------+
| 90002 |
| 90003 |
+--------+
2 rows in set (0.00 sec)
EXCEPT
相当于 Oracle 的MINUS
集合运算符。
旧版本中交集、差集操作是如何实现的
在之前版本,实现交集、差集操作的最简单的办法是使用外连接+WHERE子句,如下例所示:
- 交集
mysql> SELECT a.stu_id FROM t_stu_cou_rel a LEFT JOIN t_stu_cou_rel b ON a.stu_id = b.stu_id AND a.cou_id = '3001' AND b.cou_id = '2015' WHERE b.stu_id IS NOT NULL;
+--------+
| stu_id |
+--------+
| 90001 |
+--------+
1 row in set (0.01 sec)
--或
mysql> SELECT a.stu_id FROM (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '3001') a LEFT JOIN (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '2015') b ON a.stu_id = b.stu_id WHERE b.stu_id IS NOT NULL;
+--------+
| stu_id |
+--------+
| 90001 |
+--------+
1 row in set (0.00 sec)
- 差集
mysql> SELECT a.stu_id FROM (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '3001') a LEFT JOIN (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '2015') b ON a.stu_id = b.stu_id WHERE b.stu_id IS NULL;
+--------+
| stu_id |
+--------+
| 90002 |
| 90003 |
+--------+
2 rows in set (0.01 sec)
注意
一定要充分理解后再使用外连接。
使用外连接时一定要核对结果集是否如你预期,如否,则查询书写有误,重新检查SQL语句,不建议通过添加额外的WHERE条件来删除多余的行,因为一定程度上讲,这是为了结果正确而强行做的操作,不符合实际情况。
正确的做法是将查询条件写入派生表表中,利用谓词前推提前过滤数据集,再进行表外连接。如上所示。
下面这种做法因外连接使用不当会得到与你预期不符的结果,而这个结果对你所写的SQL语句来讲是“正确”的:mysql> SELECT a.stu_id FROM t_stu_cou_rel a LEFT JOIN t_stu_cou_rel b ON a.stu_id = b.stu_id AND a.cou_id = '3001' AND b.cou_id = '2015' WHERE b.stu_id IS NULL; +--------+ | stu_id | +--------+ | 90002 | | 90003 | | 90001 | | 90003 | +--------+ 4 rows in set (0.00 sec)
总之,SQL 优化的一个万古不变的宗旨是能提前过滤结果集(也可理解为使结果集缩小)的工作尽量提前做。
总结
现在可以更方便地对结果集做交集、差集运算了,增强了对更新版本的SQL标准的兼容性。
由此可见,MySQL 正逐渐贴近 Oracle , Oracle 公司正在将 Oracle 数据库的优点和特色功能在 MySQL 的 InnoDB 引擎上实现,同时增强了对更新版本的SQL标准的兼容性。
本次 8.0.31 版本更新还包含了对标准SQL中FULL
关键字的预告支持,先将其作为关键字保留。如果非要使用 full 作为列、表、视图、存储过程或存储函数的名称,则建议使用**反引号(`)**包围,直接使用会产生警告。