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

MySQL · 特性分析 · common table expression

2017-04-21 2417
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
db匠
目录
相关文章
蒋星熠Jaxonic
|
2月前
|
存储 消息中间件 监控
MySQL 到 ClickHouse 明细分析链路改造：数据校验、补偿与延迟治理
蒋星熠Jaxonic，数据领域技术深耕者。擅长MySQL到ClickHouse链路改造，精通实时同步、数据校验与延迟治理，致力于构建高性能、高一致性的数据架构体系。
蒋星熠Jaxonic
159 2
MySQL 到 ClickHouse 明细分析链路改造：数据校验、补偿与延迟治理
顾翔
|
3月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能（下）
使用MYSQL Report分析数据库性能
顾翔
162 3
艾伦~耶格尔
|
3月前
|
SQL 监控 关系型数据库
MySQL事务处理：ACID特性与实战应用
本文深入解析了MySQL事务处理机制及ACID特性，通过银行转账、批量操作等实际案例展示了事务的应用技巧，并提供了性能优化方案。内容涵盖事务操作、一致性保障、并发控制、持久性机制、分布式事务及最佳实践，助力开发者构建高可靠数据库系统。
艾伦~耶格尔
167 0
凉凉心.
|
2月前
|
NoSQL 算法 Redis
【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
Union文件系统（UnionFS）是一种**分层、轻量级并且高性能的文件系统**，它支持对文件系统的修改作为一次提交来一层层的叠加，同时可以将不同目录挂载到同一个虚拟文件系统下(unite several directories into a single virtual filesystem) Union 文件系统是 Docker 镜像的基础。 镜像可以通过分层来进行继承，基于基础镜像（没有父镜像），可以制作各种具体的应用镜像。
凉凉心.
495 5
蓝易云
|
3月前
|
存储 关系型数据库 MySQL
介绍MySQL的InnoDB引擎特性
总结而言 ， Inno DB 引搞 是 MySQL 中 高 性 能 ， 高 可靠 的 存 储选项 ， 宽泛 应用于要求强 复杂交易处理场景 。
蓝易云
153 15
顾翔
|
3月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（上）
最终建议：当前系统是完美的读密集型负载模型，优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存，预期可降低30%的CPU负载，同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
顾翔
244 6
顾翔
|
3月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（中）
使用MYSQL Report分析数据库性能
顾翔
169 1
蓝易云
|
4月前
|
存储 关系型数据库 MySQL
深入理解MySQL索引类型及其应用场景分析。
通过以上介绍可以看出各类MySQL指标各自拥有明显利弊与最佳实践情墁，在实际业务处理过程中选择正确型号极其重要以确保系统运作流畅而稳健。
蓝易云
195 12
热夏resummer
|
3月前
|
关系型数据库 MySQL 数据库
MySql事务以及事务的四大特性
事务是数据库操作的基本单元，具有ACID四大特性：原子性、一致性、隔离性、持久性。它确保数据的正确性与完整性。并发事务可能引发脏读、不可重复读、幻读等问题，数据库通过不同隔离级别（如读未提交、读已提交、可重复读、串行化）加以解决。MySQL默认使用可重复读级别。高隔离级别虽能更好处理并发问题，但会降低性能。
热夏resummer
175 0
蓝易云
|
5月前
|
存储 SQL 关系型数据库
MySQL的Redo Log与Binlog机制对照分析
通过合理的配置和细致的管理，这两种日志机制相互配合，能够有效地提升MySQL数据库的可靠性和稳定性。
蓝易云
196 10
数据库

关系型数据库

热门文章

最新文章

  • 1
    Linux 性能诊断 perf使用指南
  • 2
    PostgreSQL 9种索引的原理和应用场景
  • 3
    时序数据库分析 - TimescaleDB时序数据库介绍
  • 4
    MongoDB 存储引擎 WiredTiger 原理解析
  • 5
    PostgreSQL 与基友们的故事之 - Redis (无限缓存,实时标签...)
  • 6
    page fault带来的性能问题
  • 7
    PostgreSQL OLTP高并发请求性能优化
  • 8
    PostgreSQL 分库分表 插件之一 pg_shard
  • 9
    Greenplum行存与列存的选择以及转换方法
  • 10
    数据库案例集锦 - 开发者的《如来神掌》
  • 1
    CAXA 电子图板 2025 下载安装指南：小白也能轻松搞定
    38
  • 2
    【赵渝强老师】国产金仓数据库的体系架构
    124
  • 3
    阿里巴巴双肩包带回家！参与「RDS AI助手」体验反馈赢好礼
    107
  • 4
    RDS AI助手深度测评-场景4：CPU压测指引
    86
  • 5
    【Java架构师体系课 | MySQL篇】⑦ 深入理解MySQL事务隔离级别与锁机制
    142
  • 6
    TRUNCATE、DELETE、DROP 的区别？
    278
  • 7
    为什么MySQL会选错索引，如何解决？
    133
  • 8
    【Java架构师体系课 | MySQL篇】⑤ 索引优化实战一
    108
  • 9
    什么是脏读、幻读、不可重复读？Mysql的隔离级别是什么？
    328
  • 10
    【Java架构师体系课 | MySQL篇】② 深入理解MySQL索引底层数据结构与算法
    139

    • 相关产品

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

    • 相关课程

    更多
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • MySQL 技术大全：开发、优化与运维实战
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    一文了解：阿里云对象存储OSS是什么？