新特性解读 | MySQL 8.0 通用表达式（WITH）深入用法
新特性解读 | MySQL 8.0 通用表达式（WITH）深入用法
爱可生开源社区
发布于 2021-06-16 19:11:25
发布于 2021-06-16 19:11:25
作者：杨涛涛
资深数据库专家，专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
MySQL 8.0 发布已经好几年了，之前介绍过 WITH 语句（通用表达式）的简单用途以及使用场景，类似如下的语句：
with tmp(a) as (select 1 union all select 2) select * from tmp;
正巧之前客户就咨询我，WITH 有没有可能和 UPDATE、DELETE 等语句一起来用？或者说有没有可以简化日常 SQL 的其他用法，有点迷惑，能否写几个例子简单说明下？
其实 WITH 表达式除了和 SELECT 一起用， 还可以有下面的组合：
insert with 、with update、with delete、with with、with recursive(可以模拟数字、日期等序列)、WITH 可以定义多张表
我们来一个一个看看：
1. 用 WITH 表达式来造数据
用 WITH 表达式来造数据，非常简单，比如下面例子：给表 y1 添加100条记录，日期字段要随机。
localhost:ytt>create table y1 (id serial primary key, r1 int,log_date date);
Query OK, 0 rows affected (0.09 sec)
localhost:ytt>INSERT y1 (r1,log_date)
-> WITH recursive tmp (a, b) AS
-> (SELECT
-> 1,
-> '2021-04-20'
-> UNION
-> ALL
-> SELECT
-> ROUND(RAND() * 10),
-> b - INTERVAL ROUND(RAND() * 1000) DAY
-> FROM
-> tmp
-> LIMIT 100) TABLE tmp;
Query OK, 100 rows affected (0.03 sec)
Records: 100 Duplicates: 0 Warnings: 0
localhost:ytt>table y1 limit 10;
+----+------+------------+
| id | r1 | log_date |
+----+------+------------+
| 1 | 1 | 2021-04-20 |
| 2 | 8 | 2020-04-02 |
| 3 | 5 | 2019-05-26 |
| 4 | 1 | 2018-01-21 |
| 5 | 2 | 2016-09-08 |
| 6 | 9 | 2016-06-14 |
| 7 | 7 | 2016-02-06 |
| 8 | 6 | 2014-03-18 |
| 9 | 6 | 2011-08-25 |
| 10 | 9 | 2010-02-02 |
+----+------+------------+
10 rows in set (0.00 sec)
2. 用 WITH 表达式来更新表数据
WITH 表达式可以与 UPDATE 语句一起，来执行要更新的表记录：
localhost:ytt>WITH recursive tmp (a, b, c) AS
-> (SELECT
-> 1,
-> 1,
-> '2021-04-20'
-> UNION ALL
-> SELECT
-> a + 2,
-> 100,
-> DATE_SUB(
-> CURRENT_DATE(),
-> INTERVAL ROUND(RAND() * 1000, 0) DAY
-> )
-> FROM
-> tmp
-> WHERE a < 100)
-> UPDATE
-> tmp AS a,
-> y1 AS b
-> SET
-> b.r1 = a.b
-> WHERE a.a = b.id;
Query OK, 49 rows affected (0.02 sec)
Rows matched: 50 Changed: 49 Warnings: 0
localhost:ytt>table y1 limit 10;
+----+------+------------+
| id | r1 | log_date |
+----+------+------------+
| 1 | 1 | 2021-04-20 |
| 2 | 8 | 2019-12-26 |
| 3 | 100 | 2018-06-12 |
| 4 | 8 | 2017-07-11 |
| 5 | 100 | 2016-08-10 |
| 6 | 9 | 2015-09-14 |
| 7 | 100 | 2014-12-19 |
| 8 | 2 | 2014-08-13 |
| 9 | 100 | 2014-08-05 |
| 10 | 8 | 2011-11-12 |
+----+------+------------+
10 rows in set (0.00 sec)
3. 用 WITH 表达式来删除表数据
比如删除 ID 为奇数的行，可以用 WITH DELETE 形式的删除语句：
localhost:ytt>WITH recursive tmp (a) AS
-> (SELECT
-> 1
-> UNION
-> ALL
-> SELECT
-> a + 2
-> FROM
-> tmp
-> WHERE a < 100)
-> DELETE FROM y1 WHERE id IN (TABLE tmp);
Query OK, 50 rows affected (0.02 sec)
localhost:ytt>table y1 limit 10;
+----+------+------------+
| id | r1 | log_date |
+----+------+------------+
| 2 | 6 | 2019-05-16 |
| 4 | 8 | 2015-12-07 |
| 6 | 2 | 2014-05-14 |
| 8 | 7 | 2010-05-07 |
| 10 | 3 | 2007-03-27 |
| 12 | 6 | 2006-12-14 |
| 14 | 3 | 2004-04-22 |
| 16 | 7 | 2001-09-16 |
| 18 | 7 | 2001-01-04 |
| 20 | 7 | 2000-02-12 |
+----+------+------------+
10 rows in set (0.00 sec)
与 DELETE 一起使用，要注意一点：WITH 表达式本身数据为只读，所以多表 DELETE 中不能包含 WITH 表达式。比如把上面的语句改成多表删除形式会直接报 WITH 表达式不可更新的错误。
localhost:ytt>WITH recursive tmp (a) AS
-> (SELECT
-> 1
-> UNION
-> ALL
-> SELECT
-> a + 2
-> FROM
-> tmp
-> WHERE a < 100)
-> delete a,b from y1 a join tmp b where a.id = b.a;
ERROR 1288 (HY000): The target table b of the DELETE is not updatable
4. WITH 和 WITH 一起用
前提条件：WITH 表达式不能在同一个层级，一个层级只允许一个 WITH 表达式
localhost:ytt>SELECT * FROM
-> (
-> WITH tmp1 (a, b, c) AS
-> (
-> VALUES
-> ROW (1, 2, 3),
-> ROW (3, 4, 5),
-> ROW (6, 7, 8)
-> ) SELECT * FROM
-> (
-> WITH tmp2 (d, e, f) AS (
-> VALUES
-> ROW (100, 200, 300),
-> ROW (400, 500, 600)
-> ) TABLE tmp2
-> ) X
-> JOIN tmp1 Y
-> ) Z ORDER BY a;
+-----+-----+-----+---+---+---+
| d | e | f | a | b | c |
+-----+-----+-----+---+---+---+
| 400 | 500 | 600 | 1 | 2 | 3 |
| 100 | 200 | 300 | 1 | 2 | 3 |
| 400 | 500 | 600 | 3 | 4 | 5 |
| 100 | 200 | 300 | 3 | 4 | 5 |
| 400 | 500 | 600 | 6 | 7 | 8 |
| 100 | 200 | 300 | 6 | 7 | 8 |
+-----+-----+-----+---+---+---+
6 rows in set (0.01 sec)
5. WITH 多个表达式来 JOIN
用上面的例子，改写多个 WITH 为一个 WITH：
localhost:ytt>WITH
-> tmp1 (a, b, c) AS
-> (
-> VALUES
-> ROW (1, 2, 3),
-> ROW (3, 4, 5),
-> ROW (6, 7, 8)
-> ),
-> tmp2 (d, e, f) AS (
-> VALUES
-> ROW (100, 200, 300),
-> ROW (400, 500, 600)
-> )
-> SELECT * FROM tmp2,tmp1 ORDER BY a;
+-----+-----+-----+---+---+---+
| d | e | f | a | b | c |
+-----+-----+-----+---+---+---+
| 400 | 500 | 600 | 1 | 2 | 3 |
| 100 | 200 | 300 | 1 | 2 | 3 |
| 400 | 500 | 600 | 3 | 4 | 5 |
| 100 | 200 | 300 | 3 | 4 | 5 |
| 400 | 500 | 600 | 6 | 7 | 8 |
| 100 | 200 | 300 | 6 | 7 | 8 |
+-----+-----+-----+---+---+---+
6 rows in set (0.00 sec)
6. with 生成日期序列
用 WITH 表达式生成日期序列，类似于 POSTGRESQL 的 generate_series 表函数，比如，从 ‘2020-01-01’ 开始，生成一个月的日期序列：
localhost:ytt>WITH recursive seq_date (log_date) AS
-> (SELECT
-> '2020-01-01'
-> UNION
-> ALL
-> SELECT
-> log_date + INTERVAL 1 DAY
-> FROM
-> seq_date
-> WHERE log_date + INTERVAL 1 DAY < '2020-02-01')
-> SELECT
-> log_date
-> FROM
-> seq_date;
+------------+
| log_date |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
| 2020-01-04 |
| 2020-01-05 |
| 2020-01-06 |
| 2020-01-07 |
| 2020-01-08 |
| 2020-01-09 |
| 2020-01-10 |
| 2020-01-11 |
| 2020-01-12 |
| 2020-01-13 |
| 2020-01-14 |
| 2020-01-15 |
| 2020-01-16 |
| 2020-01-17 |
| 2020-01-18 |
| 2020-01-19 |
| 2020-01-20 |
| 2020-01-21 |
| 2020-01-22 |
| 2020-01-23 |
| 2020-01-24 |
| 2020-01-25 |
| 2020-01-26 |
| 2020-01-27 |
| 2020-01-28 |
| 2020-01-29 |
| 2020-01-30 |
| 2020-01-31 |
+------------+
31 rows in set (0.00 sec)
7. with 表达式做派生表
使用刚才那个日期列表，
localhost:ytt>SELECT
-> *
-> FROM
-> (
-> WITH recursive seq_date (log_date) AS
-> (SELECT
-> '2020-01-01'
-> UNION
-> ALL
-> SELECT
-> log_date + INTERVAL 1 DAY
-> FROM
-> seq_date
-> WHERE log_date+ interval 1 day < '2020-02-01')
-> select *
-> FROM
-> seq_date
-> ) X
-> LIMIT 10;
+------------+
| log_date |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
| 2020-01-04 |
| 2020-01-05 |
| 2020-01-06 |
| 2020-01-07 |
| 2020-01-08 |
| 2020-01-09 |
| 2020-01-10 |
+------------+
10 rows in set (0.00 sec)
WITH 表达式使用非常灵活，不同的场景可以有不同的写法，的确可以简化日常 SQL 的编写。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2021-05-19，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。