AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

MySQL · 特性分析 · common table expression

2017-04-21 2278
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介： common table expressionCommon table expression简称CTE，由SQL:1999标准引入，目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), Mar

common table expression

Common table expression简称CTE，由SQL:1999标准引入，
目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL and H2 (experimental), MySQL8.0.

CTE的语法如下：

WITH [RECURSIVE] with_query [, ...]
SELECT...

with_query:
query_name [ (column_name [,...]) ] AS (SELECT ...)

以下图示来自MariaDB

Non-recursive CTEs
screenshot.png

Recursive CTEs
screenshot.png

CTE的使用

  • CTE使语句更加简洁

例如以下两个语句表达的是同一语义，使用CTE比未使用CTE的嵌套查询更简洁明了。

1) 使用嵌套子查询

SELECT MAX(txt), MIN(txt)
FROM
(
  SELECT concat(cte2.txt, cte3.txt) as txt
  FROM
  (
    SELECT CONCAT(cte1.txt,'is a ') as txt
    FROM
    (
      SELECT 'This ' as txt
    ) as cte1
  ) as cte2,
  (
    SELECT 'nice query' as txt
    UNION
    SELECT 'query that rocks'
    UNION
    SELECT 'query'
  ) as cte3
) as cte4;

2) 使用CTE

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
  • CTE 可以进行树形查询
    树
    初始化这颗树
create table t1(id int, value char(10), parent_id int);
insert into t1 values(1, 'A', NULL);
insert into t1 values(2, 'B', 1);
insert into t1 values(3, 'C', 1);
insert into t1 values(4, 'D', 1);
insert into t1 values(5, 'E', 2);
insert into t1 values(6, 'F', 2);
insert into t1 values(7, 'G', 4);
insert into t1 values(8, 'H', 6);

1) 层序遍历

with recursive cte as (
  select id, value, 0 as level from t1 where parent_id is null
  union all
  select t1.id, t1.value, cte.level+1 from cte join t1 on t1.parent_id=cte.id)
select * from cte;
+------+-------+-------+
| id   | value | level |
+------+-------+-------+
|    1 | A     |     0 |
|    2 | B     |     1 |
|    3 | C     |     1 |
|    4 | D     |     1 |
|    5 | E     |     2 |
|    6 | F     |     2 |
|    7 | G     |     2 |
|    8 | H     |     3 |
+------+-------+-------+

2) 深度优先遍历

with recursive cte as (
  select id, value, 0 as level, CAST(id AS CHAR(200)) AS path  from t1 where parent_id is null
  union all
  select t1.id, t1.value, cte.level+1, CONCAT(cte.path, ",", t1.id)  from cte join t1 on t1.parent_id=cte.id)
select * from cte order by path;
+------+-------+-------+---------+
| id   | value | level | path    |
+------+-------+-------+---------+
|    1 | A     |     0 | 1       |
|    2 | B     |     1 | 1,2     |
|    5 | E     |     2 | 1,2,5   |
|    6 | F     |     2 | 1,2,6   |
|    8 | H     |     3 | 1,2,6,8 |
|    3 | C     |     1 | 1,3     |
|    4 | D     |     1 | 1,4     |
|    7 | G     |     2 | 1,4,7   |
+------+-------+-------+---------+

Oracle

Oracle从9.2才开始支持CTE, 但只支持non-recursive with, 直到Oracle 11.2才完全支持CTE。但oracle 之前就支持connect by 的树形查询，recursive with 语句可以与connect by语句相互转化。 一些相互转化案例可以参考这里.

Oracle recursive with 语句不需要指定recursive关键字，可以自动识别是否recursive.

Oracle 还支持CTE相关的hint,

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

“MATERIALIZE”告诉优化器产生一个全局的临时表保存结果，多次引用CTE时直接访问临时表即可。而”INLINE”则表示每次需要解析查询CTE。

PostgreSQL

PostgreSQL从8.4开始支持CTE，PostgreSQL还扩展了CTE的功能， CTE的query中支持DML语句，例如

create table t1 (c1 int, c2 char(10));
 insert into t1 values(1,'a'),(2,'b');
 select * from t1;
 c1 | c2
----+----
  1 | a
  2 | b


 WITH cte AS (
     UPDATE t1 SET c1= c1 * 2 where c1=1
     RETURNING *
 )
 SELECT * FROM cte; //返回更新的值
 c1 |     c2
----+------------
  2 | a

 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     UPDATE t1 SET c1= c1 * 2 where c1=1
     RETURNING *
 )
 SELECT * FROM t1;//返回原值
 c1 |     c2
----+------------
  1 | a
  2 | b


 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     DELETE FROM t1
     WHERE c1=1
     RETURNING *
 )
 SELECT * FROM cte;//返回删除的行
 c1 |     c2
----+------------
  1 | a


 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     DELETE FROM t1
     WHERE c1=1
     RETURNING *
 )
 SELECT * FROM t1;//返回原值
 c1 |     c2
----+------------
  1 | a
  2 | b
(2 rows)

MariaDB

MariaDB从10.2开始支持CTE。10.2.1 支持non-recursive CTE, 10.2.2开始支持recursive CTE。 目前的GA的版本是10.1.

MySQL

MySQL从8.0开始支持完整的CTE。MySQL8.0还在development
阶段，RC都没有，GA还需时日。

AliSQL

AliSQL基于mariadb10.2， port了no-recursive CTE的实现，此功能近期会上线。

以下从源码主要相关函数简要介绍其实现，

//解析识别with table引用
find_table_def_in_with_clauses

//检查依赖关系，比如不能重复定义with table名字
With_clause::check_dependencies

// 为每个引用clone一份定义
With_element::clone_parsed_spec

//替换with table指定的列名
With_element::rename_columns_of_derived_unit

此实现对于多次引用CTE，CTE会解析多次，因此此版本CTE有简化SQL的作用，但效率上没有效提高。

select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.25 sec)

//从执行时间来看是进行了3次全表扫描
 with t as (select count(*) from t1 where c2 !='z')
     select * from t union select * from t union select * from t;
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.59 sec)

 select count(*) from t1 where c2 !='z'
     union
     select count(*) from t1 where c2 !='z'
     union
    select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.57 sec)

 explain with t as (select count(*) from t1 where c2 !='z')
    -> select * from t union select * from t union select * from t;
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type     | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
|    1 | PRIMARY         | <derived2>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    2 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    3 | RECURSIVE UNION | <derived5>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    5 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    4 | RECURSIVE UNION | <derived6>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    6 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
| NULL | UNION RESULT    | <union1,3,4> | ALL  | NULL          | NULL | NULL    | NULL |  NULL |             |
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
7 rows in set (0.00 sec)

 explain  select count(*) from t1 where c2 !='z'
    union
    select count(*) from t1 where c2 !='z'
    union
    select count(*) from t1 where c2 !='z';
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
|    1 | PRIMARY      | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    2 | UNION        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    3 | UNION        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
| NULL | UNION RESULT | <union1,2,3> | ALL  | NULL          | NULL | NULL    | NULL |  NULL |             |
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
4 rows in set (0.00 sec)

以下是MySQL8.0 只扫描一次的执行计划

mysql> explain select count(*) from t1 where c2 !='z' union select count(*) from t1 where c2 !='z' union select count(*) from t1 where c2 !='z';
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | PRIMARY      | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
|  2 | UNION        | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
|  3 | UNION        | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
| NULL | UNION RESULT | <union1,2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

以下是PostgreSQL9.4 只扫描一次的执行计划

postgres=# explain with t as (select count(*) from t1 where c2 !='z')
postgres-# select * from t union select * from t union select * from t;
 HashAggregate  (cost=391366.28..391366.31 rows=3 width=8)
   Group Key: t.count
   CTE t
     ->  Aggregate  (cost=391366.17..391366.18 rows=1 width=0)
           ->  Seq Scan on t1  (cost=0.00..384392.81 rows=2789345 width=0)
                 Filter: ((c2)::text <> 'z'::text)
   ->  Append  (cost=0.00..0.09 rows=3 width=8)
         ->  CTE Scan on t  (cost=0.00..0.02 rows=1 width=8)
         ->  CTE Scan on t t_1  (cost=0.00..0.02 rows=1 width=8)
         ->  CTE Scan on t t_2  (cost=0.00..0.02 rows=1 width=8)

AliSQL还有待改进。

文章标签：
云数据库 RDS MySQL 版
云原生数据库 PolarDB
关系型数据库
Oracle
C语言
SQL
PostgreSQL
关键词：
云数据库 RDS MySQL 版分析
云数据库 RDS MySQL 版特性
云数据库 RDS MySQL 版table
云数据库 RDS MySQL 版expression
云数据库 RDS MySQL 版分析table
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
db匠
目录
相关文章
绿水长流*z
|
10天前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证，原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误，需要把前面已经执行过的SQL撤销以达到原子性的目的，这个过程也叫做"回滚"，所以Undo Log也叫回滚日志。
绿水长流*z
23 7
MySQL事务日志-Undo Log工作原理分析
蓝染-惣右介
|
20天前
|
SQL 安全 关系型数据库
【MySQL基础篇】事务（事务操作、事务四大特性、并发事务问题、事务隔离级别）
事务是MySQL中一组不可分割的操作集合，确保所有操作要么全部成功，要么全部失败。本文利用SQL演示并总结了事务操作、事务四大特性、并发事务问题、事务隔离级别。
蓝染-惣右介
256 2
【MySQL基础篇】事务（事务操作、事务四大特性、并发事务问题、事务隔离级别）
大数据文摘
|
27天前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解：分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持，提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析，常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`，常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
大数据文摘
68 11
爱吃香蕉的猫
|
2月前
|
关系型数据库 MySQL
mysql事务特性
原子性：一个事务内的操作统一成功或失败 一致性：事务前后的数据总量不变 隔离性：事务与事务之间相互不影响 持久性：事务一旦提交发生的改变不可逆
爱吃香蕉的猫
60 4
绿水长流*z
|
3月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1767 14
MySQL事务日志-Redo Log工作原理分析
小王老师呀
|
2月前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前，自增变量（`AUTO_INCREMENT`）的行为在服务器重启后可能会发生变化，导致意外结果。MySQL 8.0 引入了自增变量的持久化特性，将其信息存储在数据字典中，确保重启后的一致性。这提高了开发和管理的稳定性，减少了主键冲突和数据不一致的风险。默认情况下，MySQL 8.0 启用了这一特性，但在升级时需注意行为变化。
小王老师呀
69 1
小王老师呀
|
3月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
68 4
sunrr
|
3月前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性？
【10月更文挑战第3天】MySQL8.2有哪些新特性？
sunrr
74 2
糜终
|
3月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
糜终
226 4
baker_zhuang
|
3月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库，实现自然语言查询功能，包括环境搭建和配置流程。
baker_zhuang
369 0
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL 如何实现批量更新、删除、插入
  • 4
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 5
    Linux 性能诊断 perf使用指南
  • 6
    PostgreSQL 9种索引的原理和应用场景
  • 7
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 8
    Jedis常见异常汇总
  • 9
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 10
    时序数据库分析 - TimescaleDB时序数据库介绍
  • 1
    误删除了表？PolarDB MySQL帮你恢复！完成任务可领取2025蛇年春联
    11
  • 2
    RDS PostgreSQL版发布 rds_duckdb 插件！
    31
  • 3
    编程技术全景洞察：多领域知识纵横剖析
    81
  • 4
    MySQL进阶突击系列(05)突击MVCC核心原理 | 左右护法ReadView视图和undoLog版本链强强联合
    50
  • 5
    体验领礼啦！体验自建数据库迁移到阿里云数据库RDS，领取桌面置物架！
    56
  • 6
    MySQL秘籍之索引与查询优化实战指南
    66
  • 7
    【AI应用启航workshop】构建高可用数据库、拥抱AI智能问数
    82
  • 8
    【赵渝强老师】PostgreSQL的参数文件
    93
  • 9
    MySQL进阶突击系列(04)事务隔离级别、AICD、CAP、BASE原则一直搞不懂？ | 看这篇就够了
    71
  • 10
    MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
    85

    • 相关产品

  • 云数据库 RDS MySQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • SQL进阶之约束、索引

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅