作者:杨涛涛
资深数据库专家,专研 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: 0localhost: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: 0localhost: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 的编写。