MySQL8.0新特性CTE(Common Table Expression)百度首页登录MySQL8.0新特性CTE(Common Table Expression)偏执的IT工匠2020-02-28 12:41关注CTE(Common Table Expression)可以认为是派生表(derived table)的替代，在一定程度上，CTE简化了复杂的join查询和子查询，提高了SQL的可读性和执行性能。CTE是ANSI SQL 99标准的一部分，在MySQL 8.0.1版本被引入。原文地址：https://mytecdb.com/blogDetail.php?id=751. CTE优势查询语句的可读性更好在一个查询中，可以被引用多次能够连接多个CTE能够创建递归查询能够提高SQL执行性能能够有效地替代视图2. 如何创建和使用CTECTE类似于使用子查询时的派生表，但是CTE的定义不在SQL主体中，而是提到SQL最前端，声明CTE的需要使用语法WITH。2.1 CTE的使用先看一个派生表实现的例子：使用CTE改写后，SQL变成这样：CTE的语法也比较简单，在SQL主体查询之前，使用WITH语法，定义一个或者多个CTE，然后就可以在查询SQL的主体中引用一次或多次CTE，可以把CTE看成是一类提前物化的临时表，以便于查询主体引用。2.2 为CTE指定具体的字段名称使用圆括号为CTE指定字段名称，如下eur_name和eur_population为CTE的字段：2.3 CTE也可以被用作数据源来更新其他表CTE可以作为数据源，来更新或者删除其他表，如下：2.4 CTE也可以用于insert ... select 语句2.5 CTE作为提前物化的临时表定义多个CTE，作为提前物化的临时表，在主查询里面可以多次引用这些临时表。如下：2.6 CTE的可见性下面两个例子，第一个例子中cte对于顶层SELECT可见，第二个例子中，cte对顶层SELECT不可见。为了避免这种不可见的问题，通常将CTE定义在最前面，以便能够在查询主体的任何地方都能引用到CTE。2.7 CTE引用链如果在一个查询中创建多个CTE，可能会出现一个CTE引用前一个CTE，导致CTE引用链的产生。下面这个例子展示了CTE引用链：上述SQL如果使用派生表的方式改写，也将是非常庞大和复杂的。2.8 使用CTE代替视图如果你的用户没有权限创建视图，而同时又有需要使用视图，不妨试试CTE来代替视图。视图改写为CTE，如下：使用CTE代替视图能够有效提高执行效率，在本案例中，视图的执行时间大概是0.0097秒，而CTE大概是0.0054秒，CTE更快，因为只需要一次物化临时表，可以被多次引用。3. 总结在MySQL 8.0 中引入CTE新特性，在大多数场景下，能够简化SQL，提高可读性，同时也能使用CTE代替视图，提高整体性能。另外CTE也能实现递归查询，下一篇文章将详细介绍。本文译自：https://www.percona.com/blog/2020/02/10/introduction-to-mysql-8-0-common-table-expressions-part-1/举报/反馈设为首页 Baidu 使用百度前必读 意见反馈 京ICP证030173号 京公网安备11000002000001号