作者:杨涛涛

资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。

本文来源:原创投稿

*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


MySQL 8.0 最新小版本(8.0.31)支持标准SQL 的intersect(交集)和except(差集)操作。

交集: 也就是返回两个结果集的相交部分,也即左侧和右侧同时存在的记录。

差集:也就是返回两个结果集中左侧存在同时右侧不存在的记录。

之前在做其他数据库往MySQL迁移的时候,经常遇到这样的操作。由于MySQL 一直以来不支持这两类操作符,一般得想办法避开或者是通过其他方法来实现。

比如在MySQL 5.7.x 中,想要实现如下两个需求:

第一、求表t1和表t2的交集,并且结果要去重;

第二、求表t1和表t2的差集,并且结果也要去重。

简单创建表t1、表t2,并且插入几条样例数据:

<mysql:5.7.34:(ytt)> create table t1(c1 int);
Query OK, 0 rows affected (0.02 sec)

<mysql:5.7.34:(ytt)> create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)

<mysql:5.7.34:(ytt)> insert t1 values (10),(20),(20),(30),(40),(40),(50);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

<mysql:5.7.34:(ytt)> insert t2 values (10),(30),(30),(50),(50),(70),(90);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0 
<mysql:5.7.34:(ytt)> select * from t1;
+------+
| c1   |
+------+
|   10 |
|   20 |
|   20 |
|   30 |
|   40 |
|   40 |
|   50 |
+------+
7 rows in set (0.00 sec)

<mysql:5.7.34:(ytt)> select * from t2;
+------+
| c1   |
+------+
|   10 |
|   30 |
|   30 |
|   50 |
|   50 |
|   70 |
|   90 |
+------+
7 rows in set (0.00 sec)

我们来实现这两个需求:

  1. 求去重后的交集: 两表内联、去重!
<mysql:5.7.34:(ytt)> select distinct t1.c1 from t1 join t2 using(c1);
+------+
| c1   |
+------+
|   10 |
|   30 |
|   50 |
+------+
3 rows in set (0.00 sec)
  1. 求去重后的差集:两表左外联,去重,并且保留右表关联键为NULL的记录。
<mysql:5.7.34:(ytt)> select distinct t1.c1 from t1 left join t2 using(c1) where t2.c1 is null;
+------+
| c1   |
+------+
|   20 |
|   40 |
+------+
2 rows in set (0.00 sec)

在最新版本MySQL 8.0.31中,直接用intersect 和except两个新操作符即可,写起来非常简单。

创建好同样的表结构和数据,用intersect来求交集:

<mysql:8.0.31:(ytt)>table t1 intersect table t2;
+------+
| c1   |
+------+
|   10 |
|   30 |
|   50 |
+------+
3 rows in set (0.00 sec)

用except来求差集:

<mysql:8.0.31:(ytt)>table t1 except table t2;
+------+
| c1   |
+------+
|   20 |
|   40 |
+------+
2 rows in set (0.00 sec)

intersect 和except操作符默认去重。比如需要保留原始结果,则可以带上all 关键词: 如下求两表差集的结果会保留所有符合条件的记录。

<mysql:8.0.31:(ytt)>table t1 except all table t2;
+------+
| c1   |
+------+
|   20 |
|   20 |
|   40 |
|   40 |
+------+
4 rows in set (0.00 sec)

avatar
100
  Subscribe  
提醒