作者：杨涛涛
资深数据库专家，专研 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 updatable4. 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 的编写。