代码编织梦想

对于聚合的功能MySQL是都是默默的发展。在最新的8.0.31版本中提供对集合操作INTERSECT和EXCEPT。这样一来,集合操作功能基本圆满了。MySQL5.7.40版本是不支持这个集合的。

In this release MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators.
query_a INTERSECT query_b includes only rows appearing in both result sets.
query_a EXCEPT query_b returns any rows from the result set of query_a which are not in the result of query_b.
INTERSECT and EXCEPT both support DISTINCT and ALL, with DISTINCT the default in both cases. (This is the same as for UNION).
INTERSECT groups before EXCEPT or UNION, so TABLE r EXCEPT TABLE s INTERSECT TABLE t is evaluated as TABLE r EXCEPT (TABLE s INTERSECT TABLE t).
See INTERSECT Clause, and EXCEPT Clause, for additional information and examples. (Bug #1309, Bug #31336, Bug #11747209, Bug #11744757)

目前MySQL里提供的集合操作包含:【UNION ,UNION ALL,INTERSECT,EXCEPT】

集合类型数据结果
UNION ALLA={1,2,3},B={3,4,5,6}ALL={1,2,3,3,4,5,6}
UNIONA={1,2,3},B={3,4,5,6}AUB={1,2,3,4,5,6}
INTERSECTA={1,2,3},B={3,4,5,6}A∩B={3}
EXCEPTA={1,2,3},B={3,4,5,6}A-B={1,2}

集合

测试集合

CREATE TABLE `DataSetA` (
  `id` bigint NOT NULL ,
  `name` varchar(10) DEFAULT NULL
);
INSERT INTO DataSetA(id,name) VALUES(1,'A'),(2,'A'),(3,'C');

CREATE TABLE `DataSetB` (
  `id` bigint NOT NULL ,
  `name` varchar(10) DEFAULT NULL
);
INSERT INTO DataSetB(id,name) VALUES(3,'C'),(4,'D'),(5,'E'),(6,'F');

CREATE TABLE `DataSetC` (
  `id` bigint NOT NULL, 
  `name` varchar(10) DEFAULT NULL
);
INSERT INTO DataSetC(id,name) VALUES(6,'F'),(7,'G'),(7,'G'),(8,'H');

不同的结果操作集:

执行计划:

除了UNION ALL,都需要通过Using temporary 进行运算,所以性能还是存在一定影响。毕竟涉及临时表。

集合中的DISTINCT和ALL

INTERSECT和EXCEPT都支持DISTINCT和ALL与UNION相同),在这两种情况下默认为DISTINCT。
字面意义相同是否去重或全部显示。

uery_block INTERSECT [ALL | DISTINCT] query_block
mysql> table  DataSetC INTERSECT DISTINCT table DataSetC;
+----+------+
| id | name |
+----+------+
|  6 | F    |
|  7 | G    |
|  8 | H    |
+----+------+
3 rows in set (0.00 sec)

mysql> table  DataSetC INTERSECT ALL table DataSetC;
+----+------+
| id | name |
+----+------+
|  6 | F    |
|  7 | G    |
|  7 | G    |
|  8 | H    |
+----+------+
4 rows in set (0.00 sec)
复制
备注:对于INTERSECT ALL,左手表中任何唯一行的最大支持的重复数是4294967295。

注意事项

从底层实现和测试现象来看,集合对比方式是用object方式进行对比。只要列数量对就可以。类型无关紧要。但实际业务中肯定是满足字段,类型一致。

1.集合对比必须具有相同的列数,列数不同出现问题:

mysql> SELECT ID FROM DataSetA 
       INTERSECT 
     SELECT ID ,NAME FROM DataSetB;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

2. 不同类型对比,可以进行对比。但对于最终结果来说就没意义。

mysql> SELECT ID FROM DataSetA  
       UNION  
       SELECT NAME FROM DataSetA;
+------+
| ID   |
+------+
| 1    |
| 2    |
| 3    |
| A    |
| C    |
+------+
5 rows in set (0.00 sec)

3.版本对比写法不同
MySQL 5.7相比,在MySQL 8.0中,对SELECT和集合的解析器规则进行了重构,使其更加一致并减少了重复.

  • LIMIT 写法
#mysql5.7.40不支持
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right 
syntax to use near 'UNION SELECT 2) LIMIT 1' at line 1

#mysql8.0.31支持
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
  • FOR UPDATE 写法
#mysql5.7.40
mysql> SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> (SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

#mysql8.0.31 更严谨
mysql> SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right 
syntax to use near 'UNION SELECT 1 FOR UPDATE' at line 1
mysql> (SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

总结

集合对比操作也算聚合的一种,在MySQL里性能影响还是比较可观的。基本要通过临时表进行处理。所以环境中需要合理使用。

参考:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/dreamyuzhou/article/details/128711928

mysql8数据库教程_MySQL8.0数据库基础教程(二)-理解-爱代码爱编程

1 SQL 的哲学 形如 Linux 哲学一切都是文件,在 SQL 领域也有这样一条至理名言 一切都是关系 2 关系数据库 所谓关系数据库(Relational database)是创建在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。 现实世界中的各种实体以及实体之间的各种联系均用关系模型表示。现如今虽然对此模型

mysql except运算_SQL集合运算-爱代码爱编程

本专栏与大家分享小白学SQL的全过程,记录学习中遇到的难题与相应解决方法,希望能和大家共勉成长。才疏学浅文章多有错漏,还望大家多多指导,谢谢! 资料来源:DataWhale组队学习 结合运算并,交和差-UNION,INTERSECT和EXCEPT 表的加法—UNION **练习题:**假设连锁店想要增加毛利率超过 50%或者售价低于 800 的

mysql8.0数据库教程_MySQL8.0数据库基础教程(二)-理解"关系"-爱代码爱编程

1 SQL 的哲学 形如 Linux 哲学一切都是文件,在 SQL 领域也有这样一条至理名言 一切都是关系 2 关系数据库 所谓关系数据库(Relational database)是创建在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。 现实世界中的各种实体以及实体之间的各种联系均用关系模型表示。现如今虽然对此模型

mysql where 集合_MySQL|集合运算(上)-爱代码爱编程

4.1 表的加减法 4.1.1 什么是集合运算 文氏图 4.1.2 表的加法–UNION 4.1.2.1 UNION-并集运算 既可以对两张表进行并集运算,也可以对同一张表进行计算 mysql> select product_id,product_name -> from product -> union -&g

MySQL错误日志Got an error reading communication packet-爱代码爱编程

问题 经常收到客户关于通信故障错误的问题—客户面临间歇性的”Got an error reading communication packet”错误 这里分析这个错误出现的原因,以及如何解决这个问题。 官方解释 首先,当通信故障错误出现时,MySQL的状态变量Aborted_clients和Aborted_connects的计数会

MySQL 分析HeatWave引擎-爱代码爱编程

在关系型轻量级关系型数据库领域中MySQL一直占据着主导之位,但随着数据量增加,业务多态化下, OLAP结合的场景越来越多,MySQL的短板问题越来越凸出。为了解决类似问题,通常会采用MySQL的binlog同步机制 或 ETL方式抽取到对应的分析平台,再使用Spark,Impala等计算引擎做计算,提供AP的业务支持。MySQL为了在大数据时代继续延伸,

mysql- 集合运算_如我1220的博客-爱代码爱编程

1.表的加减法 1)集合运算 UNION, intersect, except 集合运算符 交集,差集,并集,子集 2)表的加法 - UNION UNION 将两个表合并, 并去除重复记录 UNION 与 OR 在同一个表中,UNION 语句 也可以用 OR 实现 不同表中,只能使用 UNION SELE

mysql-集合运算_꧁ᝰ苏苏ᝰ꧂的博客-爱代码爱编程

第四章:集合运算 4.1 表的加减法 4.1.1 什么是集合运算 集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。 在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和

新特性解读 | mysql 8.0 的交集和差集介绍_actiontech的博客-爱代码爱编程

作者:杨涛涛 资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及

mysql 8.0.31 新增支持sql标准中的交集(intersect)和差集(except)表操作符_影三人的博客-爱代码爱编程

文章目录 概览准备工作交集( INTERSECT )差集( EXCEPT)旧版本中交集、差集操作是如何实现的总结 概览 最近的 MySQL 版本( 8.0.31 ) 中,新增了对 SQL 标准 INTERSE