MySql 8 新特性 - CTE 通用表表达式(先睹为快)

纯洁的微笑 2018-05-11 11:23:19 ⋅ 107 阅读


前言

Mysql 8 正式发布了,新增了很多优秀特性,之后我会挑些重点来分享。

下面和大家一起熟悉下 CTE(Common Table Expressions)通用表表达式。

CTE 是什么

派生表大家都比较熟悉了,CTE 就是针对派生表来的,可以说是增强的派生表,或者说时派生表的替换。

派生表是 FROM 中的子查询,例如:

SELECT ... FROM (subquery) AS derived, t1 ...

CTE 就像派生表,但它的声明是在查询块儿之前,而不是在 FROM 中,例如:

WITH derived AS (subquery) 
SELECT ... FROM derived, t1 ...

CTE 的语法

看几个简单的示例:

WITH qn AS (SELECT a FROM t1)
SELECT * from qn;

WITH qn AS (SELECT a+2 AS a, b FROM t1)
UPDATE t1, qn SET t1.a=qn.a + 10 WHERE t1.a - qn.a = 0; 

WITH qn(a, b) AS (SELECT a+2, b FROM t2)
DELETE t1 FROM t1, qn WHERE t1.a - qn.a = 0;

INSERT INTO t2
WITH qn AS (SELECT 10*a AS a FROM t1) 
SELECT * from qn;

SELECT * FROM t1 WHERE t1.a IN
(WITH cte as (SELECT * FROM t1 AS t2 LIMIT 1)
SELECT a + 0 FROM cte);

CTE 的好处

CTE 相较于派生表有4个明显的优势:

(1)更好的可读性

派生表的形式:

SELECT ...
FROM t1 LEFT JOIN ((SELECT ... FROM ...) AS dt JOIN t2 ON ...) ON ...

CTE的形式:

WITH dt AS (SELECT ... FROM ...)
SELECT ...
FROM t1 LEFT JOIN (dt JOIN t2 ON ...) ON ...

(2)可以被多次引用

派生表不能被引用两次,例如:

SELECT ...
FROM (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d1
JOIN (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d2 ON d1.b = d2.a;

而 CTE 可以,例如:

WITH d AS (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) 
SELECT ... FROM d AS d1 JOIN d AS d2 ON d1.b = d2.a;

(3)可以引用其他的 CTE

派生表不能引用其他派生表,例如:

SELECT ...
FROM (SELECT ... FROM ...) AS d1, (SELECT ... FROM d1 ...) AS d2 ...

ERROR: 1146 (42S02): Table ‘db.d1’ doesn’t exist

CTE 可以引用其他的 CTE,例如:

WITH d1 AS (SELECT ... FROM ...), 
    d2 AS (SELECT ... FROM d1 ...)

SELECT
FROM d1, d2 ...

(4)性能的提升

派生表是具体化的,每个派生表都是一个具体化的存在,就会产生性能问题,例如更多的空间、耗费更多的时间……

CTE 只会被创建一次,不管被引用了多少次

示例

(1)生成 1-10 的数字

先从一个简单的例子开始,生成 1-10 的数字

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
 
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+

简单分析下这个例子:

  • 这个CTE名字是my_cte,需要注意的是名字前面多了一个关键字 RECURSIVE,说明这个CTE是递归形式的

  • 括号中间是CTE的定义

  • SELECT那句是对my_cte的使用

  • SELECT 1 AS n 是初始设置,这一行是用来定义 my_cte 的列,只有一列,类型为 INT,名字为 n

  • SELECT 1+n FROM my_cte WHERE n<10这句的意思是:从 my_cte 中拿 <10 的行,然后产生一行新记录,对 n进行增加

所以 mysql 会做以下步骤:

  • 迭代0:创建初始行,S0:S0={1}

  • 迭代1:基于 S0 进行处理,产生新数据 S1={1+1}={2}

  • 迭代2:基于 S1,产生 S2={1+2}={3}

  • ...

  • 迭代9:基于 S8,产生 S9={1+9}={10}

  • 迭代10:基于 S9,发现没有匹配n<10的,所以没有产出,并使循环终止

  • my_cte 的最终结果就是对 S0,S1,...,S9 进行 union

(2)使用 CTE 创建一个表

USE test;
CREATE TABLE numbers
  WITH RECURSIVE my_cte(n) AS
  (
    SELECT 1
    UNION ALL
    SELECT 1+n FROM my_cte WHERE n<6
  )
  SELECT * FROM my_cte;
Query OK, 6 rows affected (0,40 sec)
 
SELECT * FROM numbers;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

(3)用于 INSERT

INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS
(
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,12 sec)
 
SELECT * FROM numbers;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

(4)在 UPDATE 中使用:

WITH RECURSIVE my_cte(n) AS
(
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
SET numbers.n=0
WHERE numbers.n=my_cte.n*my_cte.n;
Query OK, 4 rows affected (0,01 sec)

SELECT * FROM numbers;
+------+
| n    |
+------+
|    0 |
|    2 |
|    3 |
|    0 |
|    5 |
|    6 |
|    0 |
|    2 |
|    3 |
|    0 |
|    5 |
|    6 |
+------+

(5)在 DELETE 中使用:

DELETE FROM numbers 
WHERE numbers.n >
  (
    WITH RECURSIVE my_cte(n) AS
    (
      SELECT 1
      UNION ALL
      SELECT 1+n FROM my_cte WHERE n<6
    )
    # Half the average is 3.5/2=1.75
    SELECT AVG(n)/2 FROM my_cte
  );
Query OK, 4 rows affected (0,07 sec)
 
SELECT * FROM numbers;
+------+
| n    |
+------+
|    0 |
|    0 |
|    0 |
|    0 |
+------+

Mysql 8 实践环境搭建

我是使用 docker 安装的 Mysql 8.0.11,我感觉这是最简单的方式。

启动正常,但连接mysql时报错:

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

意思是caching_sha2_password这个认证插件不能被加载。

网上查了一下,原因是mysql8改变了认证模式,解决方式是在启动容器时指定参数:

docker run -p 3306:3306 --name mysql8 -e MYSQL_ROOT_PASSWORD=111111 -d mysql:8.0.11 --default-authentication-plugin=mysql_native_password

重点是添加了:

--default-authentication-plugin=mysql_native_password

之后就可以正常登陆了,使用docker mysql作为客户端登录的命令:

docker run -it --link mysql8:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

这是我遇到的一个问题,如果你也是使用docker来实践mysql8,这个经验会帮您节省一些时间。

关注我们

如果需要源码可以关注“IT实战联盟”公众号并留言也可以加入交流群和作者互撩哦~~~


全部评论: 0

    我有话说:

    「开源资讯」MySQL 8.0.22 GA发布,有变化

      MySQL 最新版本 8.0.22 于2020年10月19日正式发布。 主要变化 改进审计日志:对于JSON格式的日志文件,MySQL企业审计支持使用audit_log_read

    关于MySQL 通用查询日志和慢查询日志分析

    MySQL中的日志包括:错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能:通用查询日志和慢查询日志。

    为什么说作为程序员分库分的必要性一定要掌握?

      互联网大厂程序员必须掌握海量数据和高并发问题处理技能,期望进入大厂的程序员一定要仔细看这篇! MySQL 分库分是做什么的? 相信很多程序员对 MySQL 都比较熟悉了,目前国内

    「性能与架构」MySQL 8 查询优化工具 Explain Analyze

    Explain 是我们常用的查询分析工具,可以对查询语句的执行方式进行评估,给出很多有用的线索。

    MySql实战篇:建立高性能的Mysql技巧

    体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库......

    Mysql优化---订单查询优化(1):视图优化+索引创建

    本文针对电商的订单业务进行的Mysql优化

    什么情况下才需要分库分

    我请教一下,我模拟测试,搞了几张大都是3千万左右的数据,带主键查询秒级响应,jmeter插入能够达到每秒6、700条左右,我业务量每秒就是300条左右插入,这个场景下是否不需要考虑分库分的问题?

    MySQL数据库开发需要注意的小细节整理

    尽量不在数据库做运算控制单数据量 纯INT不超过10M条,含Char不超过5M条保持身段苗条平衡范式和冗

    MySQL 插入 100万 条数据整理笔记

    多线程插入(单) 问:为何对同一个的插入多线程会比单线程快?同一时间对一个的写操作不应该是独占的吗? 答:在数据里做插入操作的时候,整体时间的分配是这样的: 1、多链接耗时 (30

    MySql实战篇:正确理解并使用MySql索引

    索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统......

    MySQL可视化管理后台,phpMyAdmin 4.9.3 和 5.0.0 发布,

    phpMyAdmin是一个非常受欢迎的基于web的MySQL数据库管理工具。

    MySQL 8.0.23 GA

    MySQL 最新版本 8.0.23 现已正式发布。此版本除了像以往一样解决了一部分 BUG,同样也增添了一些的功能。具体更新内容如下: 用户管理:授予RELOAD权限,使用户可以执行各种

    MySql实战篇:写一个简单的存储过程,完成订单定时任务

    前言之前我们分享了MySql的性能优化、索引详解等内容,本篇文章主要是针对想要入门MySql存储过程的读者,主要实现的业务是订单库里面的超过30分钟没有支付的订单全部置为失效订单......

    Java 实战篇-JDK9特性体验

    JDK9 已经出来好几个月了,我们一起来了解一下JDK9的一些特性

    MySQL 8.0.24 GA

    MySQL 8.0.24 现已发布。这是一个维护版本,修复了 219 个 Bug。其中有两位中国人的贡献(Yuxiang Jiang 和 Zhai Weixiang),他们发现 Bug 并

    「转载」47 张图带你 MySQL 进阶!!!

    我们在  138 张图带你 MySQL 入门 中主要介绍了基本的 SQL 命令、数据类型和函数,在具备以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员

    JDK 16 即将发布,特性速览!

    8 版本之际,这边下一版本 Java 16 有了...

    Java 14 有了这几个特性,开发直接飞起来!

    内容介绍: Record -- 简化了简单 Java 类的创建。 Pattern Matching -- 简化了 instanceof 。 Switch 表达式 -- 简化了