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

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


前言

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实战联盟”公众号并留言也可以加入交流群和作者互撩哦~~~


注意：本文归作者所有，未经作者允许，不得转载

#MySQL
阅读全部

全部评论: 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 并

    「轻阅读」一份MySQL索引数据结构及优化清单

    Mysql索引数据结构及优化建议

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

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

    JDK 16 即将发布，新特性速览！

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

    Java 14 有了这几个新特性，开发直接飞起来！

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